VBA to Run Macro on Every Sheet in Workbook

[ktsmith21]

I'm trying to develop the code to run a macro on every sheet of the workbook, except for a few named sheets. The macro is simply copying the information from each worksheet into a list on the "Summary" sheet. I am very new to VBA (as you can see from my macro below). Here is what I have so far that is not working:

Sub SUMMARY_COPY_ALL_SHEETS()
'
' SUMMARY_COPY_ALL_SHEETS Macro
'
'
Dim ws As Worksheet
For Each ws In Worksheets
' Select case is case sensitive
Select Case UCase(ws.CodeName)
Case "Project Limits"
Case "HCSS Import"
Case "SUM_STAT"
Case "SUMMARY"
'The sheets above are the sheets that I do not want to include'
Case Else
With ws
Range("B1", ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("SUMMARY").Select
Range("G4").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End With
End Select
Next ws

End Sub


I would also love to know if there is a better way of positioning the copied data onto the destination sheet. Currently, I am using the CTRL+SHIFT+DOWN method, then CTRL+SHIFT+UP, then offset one cell down to get to the right place to paste the data from the next sheet.

Thanks for any help you can give me,

Kevin

 

[NewYears1978]

What is the data on each sheet? is it just one column of information?

 

[NewYears1978]

Not knowing exactly what you are doing, I assumed copy G4 down and paste on Summary column one. Might have to tweak a little (like the range for the column which you mentioned in your OP)

Sub SummaryOfSheets()
    Dim ws As Worksheet
     
    Application.ScreenUpdating = False
    Sheets("Summary").Activate
     
    For Each ws In Worksheets
        If ws.Name <> "Summary" And ws.Name <> "Project Limits" And ws.Name <> "HCSS Import" And ws.Name <> "SUM_STAT""SUMMARY" Then
             ws.Range("G6:G500000").Copy
            Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
        End If
    Next ws
End Sub

Also you

 and

when you post codes and put the code between the tags (on the forums I mean)

 

[ktsmith21]

Thank you for your help with this. For the worksheets included in this, I'm trying to do all worksheets, except those that are named below: Summary, Project Limits, HCSS Import, SUM_STAT

I need it to run the code for all of the other worksheets. The name of the sheets will change regularly in the future.

Essentially, I want to copy the data in columns B through I, from all sheets except those named, into the Summary Sheet, beginning in cell G5. Once I paste the information from the first sheet, I need the information from the next sheet to paste, starting in the same column, underneath the last set of info. Basically building one big list of all info from the other sheets.

Not knowing exactly what you are doing, I assumed copy G4 down and paste on Summary column one. Might have to tweak a little (like the range for the column which you mentioned in your OP)

Sub SummaryOfSheets()
    Dim ws As Worksheet
     
    Application.ScreenUpdating = False
    Sheets("Summary").Activate
     
    For Each ws In Worksheets
        If ws.Name <> "Summary" And ws.Name <> "Project Limits" And ws.Name <> "HCSS Import" And ws.Name <> "SUM_STAT""SUMMARY" Then
             ws.Range("G6:G500000").Copy
            Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
        End If
    Next ws
End Sub

Also you

 and

when you post codes and put the code between the tags (on the forums I mean)

 

[NewYears1978]

The code I posted does what you said, well except that it pastes in A1 but you can change that.

 

[ktsmith21]

Ok I got it working right - I did not realize the sheet names were case sensitive. I adjusted those and moved the location on Summary sheet to paste to. Works perfectly. Thank you very much for your help with this!

 

[NewYears1978]

Ok I got it working right - I did not realize the sheet names were case sensitive. I adjusted those and moved the location on Summary sheet to paste to. Works perfectly. Thank you very much for your help with this!

Great You're welcome.