 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2007Summary sheet
Hello: I have some worksheets (questionnaire format) for each of the client. The client name, phone number and other information are at the same locations in each worksheet. My question is how I can create a summary sheet, so that each row in the summary sheet contains one client information. I wrote the following code, but I have no idea on how each of the client information can be inserted into a new row, e.g. A3, B3, C3/A4, B4, C4. Sub Macro4() Range("A2").Select ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!RC[1])" Range("B2").Select ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[1]C[1])" Range("C2").Select ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[3]C[1])" End Sub Thanks in advance! Hi Try this small example Add a worksheet with the name "Master" to your testworkbook first and run this macro If you only want to have the values see http://www.rondebruin.nl/copy2.htm Sub test() Dim sh As Worksheet Dim cell As Range Dim a As Long Dim rw As Long rw = 0 For Each sh In ThisWorkbook.Worksheets If sh.Name <> "Master" Then rw = rw + 1 a = 0 For Each cell In Range("A3,B3,C3") a = a + 1 Sheets("Master").Cells(rw, a).Formula = "=" & sh.Name & "!" & cell.Address Next cell End If Next sh End Sub  SignatureRegards Ron de Bruin http://www.rondebruin.nl > Hello: > I have some worksheets (questionnaire format) for each of the client. The [quoted text clipped - 12 lines] > End Sub > Thanks in advance! It works. Thanks a lot for your help! > Hi > [quoted text clipped - 38 lines] > > End Sub > > Thanks in advance! You are welcome SignatureRegards Ron de Bruin http://www.rondebruin.nl > It works. Thanks a lot for your help! > [quoted text clipped - 40 lines] >> > End Sub >> > Thanks in advance! I add a example to my website http://www.rondebruin.nl/summary.htm SignatureRegards Ron de Bruin http://www.rondebruin.nl > It works. Thanks a lot for your help! > [quoted text clipped - 40 lines] >> > End Sub >> > Thanks in advance! This post was brilliant and exactly what I needed for my situation... However, I need the mergesheet to automatically format the columns so that the data fits into the cells.. right now it's all scrunched up - so, everytime I run the macro to update it - I would have to re-format/add column headings to the mergesheet when I would rather it do it as part of the macro run... I also wonder if it's possible for it to automatically add calculation/formula based on the data on the "new" mergesheet? Again, this forum has been so immensely helpful and all of the people who share their knowledge and time are to be highly commended!! Thank you in advance > I add a example to my website > http://www.rondebruin.nl/summary.htm [quoted text clipped - 43 lines] > >> > End Sub > >> > Thanks in advance! Do you use this example now ? http://www.rondebruin.nl/summary.htm Add one line above the start row line 'Add headers Newsh.Range("A1:D1").Value = Array("header1", "header2", "header3", "header4") 'The links to the first sheet will start in row 2 RwNum = 1 It autofit the columns with this line Newsh.UsedRange.Columns.AutoFit SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > This post was brilliant and exactly what I needed for my situation... > However, I need the mergesheet to automatically format the columns so that [quoted text clipped - 58 lines] >> >> > End Sub >> >> > Thanks in advance! Just in case I continue to have email problems! I am using your example http://www.rondebruin.nl/copy2.htm (modified to copy just values/formats).... Will the below code still work in that example? If yes, could you let me know where I should put it? > Do you use this example now ? > http://www.rondebruin.nl/summary.htm [quoted text clipped - 72 lines] > >> >> > End Sub > >> >> > Thanks in advance! I reply to you private with a example SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > Just in case I continue to have email problems! > [quoted text clipped - 80 lines] >> >> >> > End Sub >> >> >> > Thanks in advance! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.