View Full Version : Solved: Excel summary page.
BOATS
10-16-2006, 06:09 PM
Hello all, I am new to the Macro portion of excel and could use a little help. I have searched almost completely through all of the posts looking for what I need but can't seem to get any of the other codes to work right. Attached is a test copy of the work book I am working on. Simply put I need to get the information in columns A thru I (excluding the header portions) from all of the named worksheets onto the summary worksheet. I also need this to auto update as new information is input and auto sort numerically based on the PS # in Column A. There are also other worksheets in the workbook that contain different info not needed on the summary page. Any help would be greatly appreciated.
Boats
The sample book has 6 sheets. I'm assuming that sheet1 is going to be the summary sheet, and the data from the other sheets is going to be put there.
However, if you have "other worksheets in the workbook that contain different info not needed on the summary page" then how are these going to be identified so the data is not copied across.
Tony
Bob Phillips
10-17-2006, 01:49 AM
Add a button from the forms toolbar to the summary sheet, and assign this macro to it
Sub GatherData()
Dim i As Long
Dim iLastRow As Long
Dim iNextRow As Long
Dim sh As Worksheet
iNextRow = 4
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> ActiveSheet.Name Then
iLastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
If iLastRow > 4 Then
sh.Range("A1:I4").Copy Range("A1")
For i = 5 To iLastRow
iNextRow = iNextRow + 1
sh.Rows(i).Copy Cells(iNextRow, "A")
Next i
End If
End If
Next sh
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A4:I" & iLastRow).Sort key1:=Range("A4"), order1:=xlAscending, header:=xlYes
Columns("A:I").AutoFit
End Sub
You will need to handle the other sheets though as acw mentioned.
... will need to handle the other sheets though, as acw mentioned
With only 6 sheets, I would just change the IF statement to
If sh.Name = "Boats" Or "Fac" Or "Etc."
'Code
End If
Or, with Range A1 being empty, one could put a code word there with Font Color = Fill Color, and test sh[A1] for the code.
SamT :dunno
Bob Phillips
10-17-2006, 06:02 AM
With only 6 sheets, I would just change the IF statement to
If sh.Name = "Boats" Or "Fac" Or "Etc."
'Code
End If
Ah, but that assumes always 6 sheets, not a good assumption IMO.
BOATS
10-17-2006, 07:16 AM
Thanks for all the info I'll play around with it to see what works best.
BOATS
10-17-2006, 08:30 AM
OK the original code works great on the test workbook (only has the six sheets) but when I tried it in my other work book changing the if statement to the tab names I get a run time error '13' Type Mismatch. I've played with the tab names and still have had no luck. As this sheet stands the five named tabs that need to be tallied on the summary sheet are "BOATS", "FAC", "ADM", "LE", AND "RESCUE". Anyone have any ideas. Thanks for all the help.
Bob Phillips
10-17-2006, 08:32 AM
OK the original code works great on the test workbook (only has the six sheets) but when I tried it in my other work book changing the if statement to the tab names I get a run time error '13' Type Mismatch. I've played with the tab names and still have had no luck. As this sheet stands the five named tabs that need to be tallied on the summary sheet are "BOATS", "FAC", "ADM", "LE", AND "RESCUE". Anyone have any ideas. Thanks for all the help.
Post the code.
BOATS
10-17-2006, 09:17 AM
Sub GatherData()
Dim i As Long
Dim iLastRow As Long
Dim iNextRow As Long
Dim sh As Worksheet
iNextRow = 4
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> ActiveSheet.Name Then
iLastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
If iLastRow > 4 Then
sh.Range("A1:I4").Copy Range("A1")
For i = 5 To iLastRow
iNextRow = iNextRow + 1
sh.Rows(i).Copy Cells(iNextRow, "A")
Next i
End If
End If
Next sh
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A4:I" & iLastRow).Sort key1:=Range("A4"), order1:=xlAscending, header:=xlYes
Columns("A:I").AutoFit
End Sub
As I said for the Test workbook it brings everything in great, but on the actuall work book I have 11 other worksheets that don't need to be included on the Summary sheet. I tried changing the If statemene to
If sh.name= "Boats" Or "Fac" Or "Etc."
But then I get the Run time error.
thanks for the help.
Bob Phillips
10-17-2006, 09:35 AM
Change
If sh.Name <> ActiveSheet.Name Then
iLastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
If iLastRow > 4 Then
sh.Range("A1:I4").Copy Range("A1")
For i = 5 To iLastRow
iNextRow = iNextRow + 1
sh.Rows(i).Copy Cells(iNextRow, "A")
Next i
End If
End If
with
Select Case sh.Name
Case "Boats", "FAC", "Etc.":
iLastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
If iLastRow > 4 Then
sh.Range("A1:I4").Copy Range("A1")
For i = 5 To iLastRow
iNextRow = iNextRow + 1
sh.Rows(i).Copy Cells(iNextRow, "A")
Next i
End If
End Select
BOATS
10-17-2006, 05:21 PM
Outstanding it works great. This should save me a lot of time copying and pasting into the summary sheet so that one person looking over my shoulder is happy. Thanks again for all the help. With a little tweeking I think it will help me on a couple other projects as well.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.