Solved: Excel summary page.

[BOATS]

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.

[acw]

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]

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.

[SamT]

... 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]

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]

Thanks for all the info I'll play around with it to see what works best.

[BOATS]

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]

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]

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]

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]

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.