Excel Consolidation Tab [Solved/Closed]

Report
Posts
7
Registration date
Thursday June 4, 2015
Status
Member
Last seen
August 21, 2015
-
Posts
7
Registration date
Thursday June 4, 2015
Status
Member
Last seen
August 21, 2015
-
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

1 reply

Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
455
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.
Posts
7
Registration date
Thursday June 4, 2015
Status
Member
Last seen
August 21, 2015

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
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
455
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
Posts
7
Registration date
Thursday June 4, 2015
Status
Member
Last seen
August 21, 2015

Hi Trowa. Thank you so much. It worked perfectly

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!