VBA to Copy Range from Muliple Worksheets in one master

[Elle Mc]

Hi All,

I am hoping someone can help. I have a spread sheet with 30+ tabs and I want to copy certain named tabs for the same range on each selected tab into 1 master sheet. The below works if I have only 1 to exclude but I have multiple to exclude . how do I amend to either include 20+ sheets or exclude 20+sheets?


Sub Master()


Dim Sht As Worksheet


For Each Sht In ActiveWorkbook.Worksheets


If Sht.Name <> "Master" Then


Sht.Select


Range("A4:W89").Copy


Sheets("Master").Select


Range("A65536").End(xlUp).Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues


Else


End If


Next Sht


End Sub

 

[Fluff]

How about

Sub ElleMc()
   Dim Sht As Worksheet
   
   For Each Sht In ActiveWorkbook.Worksheets
      Select Case Sht.Name
         Case [COLOR=#ff0000]"Master", "Sheet1", "Sheet2"[/COLOR]
         Case Else
            Sht.Range("A4:W89").Copy
            Sheets("Master").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
     End Select
   Next Sht
End Sub

Change the part in red to the sheets you don't want to copy

 

[Elle Mc]

OMG, that worked a Treat, Your a legend, Thanks very much!

 

[Fluff]

You're welcome & thanks for the feedback