Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a summary sheet
Hello,
I am using the below formula to try to create a summary sheet within one workbook from multiple sheets. How would I change the below to look only at column a-f and all rows in the worksheet that have actual information in the row? Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a summary sheet
If you want that use a Copy macro
See http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hello, I am using the below formula to try to create a summary sheet within one workbook from multiple sheets. How would I change the below to look only at column a-f and all rows in the worksheet that have actual information in the row? Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a summary sheet
Hey Ron,
I tried using the "Copy a range/column after the last column with data" and get a compile error when I run the macro. It says sub or function not defined. It is highlighting the below code: Last = LastCol(DestSh) "Ron de Bruin" wrote: If you want that use a Copy macro See http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hello, I am using the below formula to try to create a summary sheet within one workbook from multiple sheets. How would I change the below to look only at column a-f and all rows in the worksheet that have actual information in the row? Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a summary sheet
Read this above the macro
Note: This example use the function LastCol Copy the function also in you module From the webpage Where do I copy the macros and functions from this page? 1. Alt-F11 2. InsertModule from the Menu bar 3. Paste the Code there 4. Alt-Q to go back to Excel 5. Alt-F8 to run the subs Common Functions required for all routines: Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hey Ron, I tried using the "Copy a range/column after the last column with data" and get a compile error when I run the macro. It says sub or function not defined. It is highlighting the below code: Last = LastCol(DestSh) "Ron de Bruin" wrote: If you want that use a Copy macro See http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hello, I am using the below formula to try to create a summary sheet within one workbook from multiple sheets. How would I change the below to look only at column a-f and all rows in the worksheet that have actual information in the row? Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a summary sheet
Thanks Ron, that worked.
The "merge" sheet is taking the data from sheets 1,2 &3 and placing it horizontally across the sheet. Is there a way to place the merged data vertically, one sheet's data on top of the other ending at the last row of data? For example, my data is in columns A:F in three separate sheets. There may be many rows completed in each sheet however. I am trying to create a summary sheet that has A:F of all three sheets in one nice. Does this make sense and is it possible? "Ron de Bruin" wrote: Read this above the macro Note: This example use the function LastCol Copy the function also in you module From the webpage Where do I copy the macros and functions from this page? 1. Alt-F11 2. InsertModule from the Menu bar 3. Paste the Code there 4. Alt-Q to go back to Excel 5. Alt-F8 to run the subs Common Functions required for all routines: Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hey Ron, I tried using the "Copy a range/column after the last column with data" and get a compile error when I run the macro. It says sub or function not defined. It is highlighting the below code: Last = LastCol(DestSh) "Ron de Bruin" wrote: If you want that use a Copy macro See http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hello, I am using the below formula to try to create a summary sheet within one workbook from multiple sheets. How would I change the below to look only at column a-f and all rows in the worksheet that have actual information in the row? Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a summary sheet
Use this example on the page
Copy from row 2 till the last row with data -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Thanks Ron, that worked. The "merge" sheet is taking the data from sheets 1,2 &3 and placing it horizontally across the sheet. Is there a way to place the merged data vertically, one sheet's data on top of the other ending at the last row of data? For example, my data is in columns A:F in three separate sheets. There may be many rows completed in each sheet however. I am trying to create a summary sheet that has A:F of all three sheets in one nice. Does this make sense and is it possible? "Ron de Bruin" wrote: Read this above the macro Note: This example use the function LastCol Copy the function also in you module From the webpage Where do I copy the macros and functions from this page? 1. Alt-F11 2. InsertModule from the Menu bar 3. Paste the Code there 4. Alt-Q to go back to Excel 5. Alt-F8 to run the subs Common Functions required for all routines: Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hey Ron, I tried using the "Copy a range/column after the last column with data" and get a compile error when I run the macro. It says sub or function not defined. It is highlighting the below code: Last = LastCol(DestSh) "Ron de Bruin" wrote: If you want that use a Copy macro See http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hello, I am using the below formula to try to create a summary sheet within one workbook from multiple sheets. How would I change the below to look only at column a-f and all rows in the worksheet that have actual information in the row? Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a summary sheet
It's still doing the same thing, how do I get the merge sheet to show data
from all other sheets stacked on top of each other as opposed to next to each other? "Ron de Bruin" wrote: Use this example on the page Copy from row 2 till the last row with data -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Thanks Ron, that worked. The "merge" sheet is taking the data from sheets 1,2 &3 and placing it horizontally across the sheet. Is there a way to place the merged data vertically, one sheet's data on top of the other ending at the last row of data? For example, my data is in columns A:F in three separate sheets. There may be many rows completed in each sheet however. I am trying to create a summary sheet that has A:F of all three sheets in one nice. Does this make sense and is it possible? "Ron de Bruin" wrote: Read this above the macro Note: This example use the function LastCol Copy the function also in you module From the webpage Where do I copy the macros and functions from this page? 1. Alt-F11 2. InsertModule from the Menu bar 3. Paste the Code there 4. Alt-Q to go back to Excel 5. Alt-F8 to run the subs Common Functions required for all routines: Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hey Ron, I tried using the "Copy a range/column after the last column with data" and get a compile error when I run the macro. It says sub or function not defined. It is highlighting the below code: Last = LastCol(DestSh) "Ron de Bruin" wrote: If you want that use a Copy macro See http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hello, I am using the below formula to try to create a summary sheet within one workbook from multiple sheets. How would I change the below to look only at column a-f and all rows in the worksheet that have actual information in the row? Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a summary sheet
If you use this example the data will be below each other and not next to each other
Try the example macro named "Test2" together with the LastRow function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... It's still doing the same thing, how do I get the merge sheet to show data from all other sheets stacked on top of each other as opposed to next to each other? "Ron de Bruin" wrote: Use this example on the page Copy from row 2 till the last row with data -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Thanks Ron, that worked. The "merge" sheet is taking the data from sheets 1,2 &3 and placing it horizontally across the sheet. Is there a way to place the merged data vertically, one sheet's data on top of the other ending at the last row of data? For example, my data is in columns A:F in three separate sheets. There may be many rows completed in each sheet however. I am trying to create a summary sheet that has A:F of all three sheets in one nice. Does this make sense and is it possible? "Ron de Bruin" wrote: Read this above the macro Note: This example use the function LastCol Copy the function also in you module From the webpage Where do I copy the macros and functions from this page? 1. Alt-F11 2. InsertModule from the Menu bar 3. Paste the Code there 4. Alt-Q to go back to Excel 5. Alt-F8 to run the subs Common Functions required for all routines: Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hey Ron, I tried using the "Copy a range/column after the last column with data" and get a compile error when I run the macro. It says sub or function not defined. It is highlighting the below code: Last = LastCol(DestSh) "Ron de Bruin" wrote: If you want that use a Copy macro See http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hello, I am using the below formula to try to create a summary sheet within one workbook from multiple sheets. How would I change the below to look only at column a-f and all rows in the worksheet that have actual information in the row? Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a summary sheet
Thank you so much Ron for sticking with me to figure this out, it worked!
Thank you "Ron de Bruin" wrote: If you use this example the data will be below each other and not next to each other Try the example macro named "Test2" together with the LastRow function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... It's still doing the same thing, how do I get the merge sheet to show data from all other sheets stacked on top of each other as opposed to next to each other? "Ron de Bruin" wrote: Use this example on the page Copy from row 2 till the last row with data -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Thanks Ron, that worked. The "merge" sheet is taking the data from sheets 1,2 &3 and placing it horizontally across the sheet. Is there a way to place the merged data vertically, one sheet's data on top of the other ending at the last row of data? For example, my data is in columns A:F in three separate sheets. There may be many rows completed in each sheet however. I am trying to create a summary sheet that has A:F of all three sheets in one nice. Does this make sense and is it possible? "Ron de Bruin" wrote: Read this above the macro Note: This example use the function LastCol Copy the function also in you module From the webpage Where do I copy the macros and functions from this page? 1. Alt-F11 2. InsertModule from the Menu bar 3. Paste the Code there 4. Alt-Q to go back to Excel 5. Alt-F8 to run the subs Common Functions required for all routines: Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hey Ron, I tried using the "Copy a range/column after the last column with data" and get a compile error when I run the macro. It says sub or function not defined. It is highlighting the below code: Last = LastCol(DestSh) "Ron de Bruin" wrote: If you want that use a Copy macro See http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hello, I am using the below formula to try to create a summary sheet within one workbook from multiple sheets. How would I change the below to look only at column a-f and all rows in the worksheet that have actual information in the row? Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a summary sheet
You are welcome
Thanks for the feedback -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Thank you so much Ron for sticking with me to figure this out, it worked! Thank you "Ron de Bruin" wrote: If you use this example the data will be below each other and not next to each other Try the example macro named "Test2" together with the LastRow function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... It's still doing the same thing, how do I get the merge sheet to show data from all other sheets stacked on top of each other as opposed to next to each other? "Ron de Bruin" wrote: Use this example on the page Copy from row 2 till the last row with data -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Thanks Ron, that worked. The "merge" sheet is taking the data from sheets 1,2 &3 and placing it horizontally across the sheet. Is there a way to place the merged data vertically, one sheet's data on top of the other ending at the last row of data? For example, my data is in columns A:F in three separate sheets. There may be many rows completed in each sheet however. I am trying to create a summary sheet that has A:F of all three sheets in one nice. Does this make sense and is it possible? "Ron de Bruin" wrote: Read this above the macro Note: This example use the function LastCol Copy the function also in you module From the webpage Where do I copy the macros and functions from this page? 1. Alt-F11 2. InsertModule from the Menu bar 3. Paste the Code there 4. Alt-Q to go back to Excel 5. Alt-F8 to run the subs Common Functions required for all routines: Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hey Ron, I tried using the "Copy a range/column after the last column with data" and get a compile error when I run the macro. It says sub or function not defined. It is highlighting the below code: Last = LastCol(DestSh) "Ron de Bruin" wrote: If you want that use a Copy macro See http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nabanco" wrote in message ... Hello, I am using the below formula to try to create a summary sheet within one workbook from multiple sheets. How would I change the below to look only at column a-f and all rows in the worksheet that have actual information in the row? Sub Summary_All_Worksheets_With_Formulas() Dim Sh As Worksheet Dim Newsh As Worksheet Dim myCell As Range Dim ColNum As Integer Dim RwNum As Long Dim Basebook As Workbook With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Summary-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Summary-Sheet" 'The links to the first sheet will start in row 2 RwNum = 1 For Each Sh In Basebook.Worksheets If Sh.Name < Newsh.Name And Sh.Visible Then ColNum = 1 RwNum = RwNum + 1 'Copy the sheet name in the A column Newsh.Cells(RwNum, 1).Value = Sh.Name For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range ColNum = ColNum + 1 Newsh.Cells(RwNum, ColNum).Formula = _ "='" & Sh.Name & "'!" & myCell.Address(False, False) Next myCell End If Next Sh Newsh.UsedRange.Columns.AutoFit With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Summary Sheet | Excel Discussion (Misc queries) | |||
Creating summary sheet | Excel Programming | |||
creating a summary sheet | Excel Discussion (Misc queries) | |||
Creating a summary sheet from data across multiple worksheets | Excel Discussion (Misc queries) | |||
Creating A Summary Sheet With Information From Several Worksheets | Excel Discussion (Misc queries) |