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 |
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) |