Excel VBA

[Amsterdam_Neil_D]

Hi All,

 

I have a little VBA macro that combines the contents of the active sheets into 1 master table.

So I have Sheet Main (Where i run the macro from) and sheet 1,2,3.

This works fine but I added a couple of sheets for some calculations and other stuff.  So now I have sheet 1,2,3,4,5 but I only want to Macro to do it's thing on Sheet 1,2,3.  Currently it's grabbing all the tables form all the active sheets apart from the Main and this is sending it wrong as I pivot from the resulting master table.

The bit in bold is where I need to identify the sheets to include but I cannot remember the syntax. (Something like if sheet = 1,2,3 then do it)

Thanks

NJD

 

Sub NJD()
For Each sh In Worksheets
 If sh.Name <> ActiveSheet.Connect Then
  With sh
   .Range("A2:F" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
    End With
 End If
Next sh
End Sub

[BOF]

As a cludge could you set a counter to 1 and increment it within the loop, then break out of the loop once the counter goes above 3 ?

[Amsterdam_Neil_D]

I did an even worse fix but works great,  I just inserted empty columns up to F on each supporting page that was not in the 3 I needed.

So removed the the data that it could pick up.

Your idea was sound also though.

 

[BOF]

2 hours ago, Amsterdam_Neil_D said:

I did an even worse fix but works great,  I just inserted empty columns up to F on each supporting page that was not in the 3 I needed.

So removed the the data that it could pick up.

Oh you dirty vagrant

[snowychap]

If sh.Name = "Sheet1" Or sh.Name = "Sheet2" Or sh.Name = "Sheet3" Then