| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summary 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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Summary sheet
It works. Thanks a lot for your help!
"Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Summary sheet
You are welcome
-- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... It works. Thanks a lot for your help! "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... 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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Summary sheet
I add a example to my website
http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... It works. Thanks a lot for your help! "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... 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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Summary sheet
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 "Ron de Bruin" wrote: I add a example to my website http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... It works. Thanks a lot for your help! "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... 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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Summary sheet
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "graloe" wrote in message ... 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 "Ron de Bruin" wrote: I add a example to my website http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... It works. Thanks a lot for your help! "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... 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! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Summary sheet
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? "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "graloe" wrote in message ... 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 "Ron de Bruin" wrote: I add a example to my website http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... It works. Thanks a lot for your help! "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... 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! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Summary sheet
I reply to you private with a example
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "graloe" wrote in message ... 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? "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "graloe" wrote in message ... 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 "Ron de Bruin" wrote: I add a example to my website http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... It works. Thanks a lot for your help! "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Vincdc" wrote in message ... 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| a summary sheet | New Users to Excel | |||
| Summary Sheet | Excel Discussion (Misc queries) | |||
| How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
| Summary Sheet | Excel Worksheet Functions | |||
| Relative Sheet Reference (Summary Sheet) | Excel Discussion (Misc queries) | |||
Linear Mode
