Hello Grimm127,
We can use a sheet name range to exclude the sheets that you don't want included in the data transfer. This method would be a tidier way of doing this.
Hence, open a new sheet and name it say Sheet List. In cell A1 of this new sheet, place the heading ShList. Now, from A2 down, place the names of all the sheets that you wish to exclude starting with the name of your main sheet (Master) in A2 and then Sheet List in A3 with the other sheets following on from there.
Next, high-light (select) all the sheet names that you have in the list, including the heading (ShList). Go to the Formulas tab and select Define Name. A little dialogue box will appear. Check that the details are correct and click "OK".
Next, create a table around this list by going to the Insert tab and selecting "Table" in the Table group (make sure that the list is still high-lighted before selecting "Table"). Check that the details are correct and click "OK". You now have a table that will expand and contract as you add or remove sheet names.
Next, place the following amended code in a standard module and assign it to a button:-
Code:
Sub TransferData()
Dim ws As Worksheet, sh As Worksheet, sht As Worksheet
Dim shRng As Range, lr As Long
Set sht = Sheets("Sheet List")
Set sh = Sheets("Master")
Set shtRng = sht.Range("ShList")
Application.ScreenUpdating = False
sh.UsedRange.Offset(1).ClearContents
For Each ws In Worksheets
If shtRng.Find(What:=ws.Name, LookAt:=xlWhole) Is Nothing Then
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
Union(ws.Range("A2:F" & lr), ws.Range("I2:U" & lr), ws.Range("W2:AH" & lr)).Copy
sh.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
End If
Next ws
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Every sheet that you place in the ShList will now be excluded from the data transfer. If you wish that certain sheets be included in future then just remove them from the ShList.
Make sure that the Master and Sheet List names are always in the ShList.
So that everyone is clear on what is happening, following is the link to a sample file that I created for you to play with. Click on the "RUN" button to see it work.You will see that only data from sheet2 and sheet3 will be transferred to the Master sheet. Hence, if you only have the excluded sheets in the Sheet List, the remaining ten of your source sheets will have their data transferred over to the Master sheet.
http://ge.tt/5pMYFlm2
To prevent duplication in the Master sheet, all previous data is cleared prior to a new data transfer being executed.
Test the code in a copy of your work book first.
Cheerio,
vcoolio.
