Excel Consolidation Tab

Solved/Closed
nnavarro Posts 7 Registration date Thursday June 4, 2015 Status Member Last seen August 21, 2015 - Jun 4, 2015 at 01:13 AM
nnavarro Posts 7 Registration date Thursday June 4, 2015 Status Member Last seen August 21, 2015 - Jun 11, 2015 at 08:30 PM
Hi all,

I am wondering if it is possible to exclude one spreadsheet from being pulled into the master tab. I could name it Data. Can you advice? See code below.

Sub Master()
Dim j As Long, k As Long, r As Range
j = Worksheets.Count
With Worksheets("master")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
r.EntireRow.Delete
End With
For k = 1 To j
If Worksheets(k).Name = "master" Then GoTo errorhandler
With Worksheets(k)
If .Range("A4") = "" Then GoTo errorhandler
Set r = Range(.Range("A4"), .Range("A4").End(xlDown))
r.EntireRow.Copy
Worksheets("master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
errorhandler:
Next k
End Sub

Thanks,

Noah
Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jun 4, 2015 at 11:21 AM
Hi Noah,

Well you already have a code line which excludes a sheet from the loop, so all you need is to add to that line:
Change:
If Worksheets(k).Name = "master" Then GoTo errorhandler
Into:
If Worksheets(k).Name = "master" or Worksheets(k).Name = "Data" Then GoTo errorhandler

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
nnavarro Posts 7 Registration date Thursday June 4, 2015 Status Member Last seen August 21, 2015
Jun 4, 2015 at 01:29 PM
Trowa,

Thank you so much for such quick response!! This works like a charm. I just got a question. Once the code is ran. It opens a save table. I close it and works fine. I was just wondering why that would happen.

Once again thank you,

Noah
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jun 9, 2015 at 11:32 AM
Hi Noah,

If by save table, you mean the clipboard (the window that remembers all the stuff you copied), then you could add the following code line between "Next k" and "End Sub":
Application.CutCopyMode = False

OR
You can change the following 2 lines into 1 line (without the ".PasteSpecial"):
r.EntireRow.Copy
Worksheets("master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial

Change into:
r.EntireRow.Copy Worksheets("master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Best regards,
Trowa
nnavarro Posts 7 Registration date Thursday June 4, 2015 Status Member Last seen August 21, 2015
Jun 11, 2015 at 08:30 PM
Hi Trowa. Thank you so much. It worked perfectly