VBA to Copy Range from Muliple Worksheets in one master

Elle Mc

New Member
Joined
Jan 29, 2019
Messages
4


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




 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,225
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top