Previous Topic Next Topic

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

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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.
0
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0
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
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses