Combine Sheets but leave certain sheets out

Amapola188 · Jan 15th 2018

Good Afternoon
I'm compiling a monthly report, adding data monthly to make it YTD. I have one to two sheets per month with the data which I'm adding every month. I then analyse the data with a Pivot Table.

I have a macro that combines all sheets in a workbook but need to find a way to tell the macro to leave specific sheets out. Else I either need to recreate the Pivot Table each month, or combine the data manually.

I first thought by hiding the sheet the macro would ignore the sheets but that doesn't work. Then I thought that maybe I could tell it to only combine the visible sheets. But my code isn't right. When stepping through my code, it also doesn't realibly hide the sheets and I have no idea why that would be.

Thanks for any ideas. Maybe there is another property that could define which sheets should be combined?

Code

Public Sub CombineSheets()


Dim ws As Worksheet
Dim wsPivot As Worksheet
Dim wsTeams As Worksheet
On Error Resume Next


Set wsPivot = ActiveWorkbook.Sheets("Pivot")
Set wsTeams = ActiveWorkbook.Sheets("Teams")


wsPivot.Visible = xlSheetVeryHidden
wsTeams.Visible = xlSheetVeryHidden


Worksheets(1).Activate


Dim J As Integer
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"


If Sheets(2).Visible = True Then
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
End If


If Sheets(J).Visible = True Then
For J = 2 To Sheets.Count
    Sheets(J).Activate
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
End If




wsPivot.Visible = xlSheetVisible
wsTeams.Visible = xlSheetVisible




End Sub

Display More

jolivanes · Jan 15th 2018

I have not looked into your code but you'll have to incorporate something like this.

Code

Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Leave Me out 1" And ws.Name <> "Leave Me Out 2" And ws.Name <> "Leave Me Out 3" Then

Amapola188 · Jan 23rd 2018

Thanks, jolivanes. I have incorporated that into my code. I don't have any data to try at the moment but will report back when I do my next round. Christine

Amapola188 · Feb 13th 2018

A new month, same problem. I have incorporated the above into my code and played around with it a bit. I don't know whether it works because I get a runtime error 91 on the exact line.
Runtime 91 is if a variable hasn't been set and I can't see the issue. ws has been declared and the .Name bit is part of the object model. I fail to see what it's problem is!

My code below. Does anybody have any idea? I'm starting to think this may just not be possible.

Code

Dim ws As Worksheet


Worksheets(1).Activate
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"


    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")


Dim J As Integer
For J = 2 To Sheets.Count
    If ws.Name <> "Pivot" And ws.Name <> "Teams" Then


    Sheets(J).Activate
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    
End If
        
    Next

Display More

Thanks, Christine

jolivanes · Feb 13th 2018

See if this gets you anywhere.

Code

Sub Maybe()
Dim i As Long
Sheets.Add after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "Combined"
    For i = 1 To ActiveWorkbook.Sheets.Count
        If Sheets(i).Name <> "Pivot" And Sheets(i).Name <> "Teams" Then
            Sheets(i).UsedRange.Copy Sheets("Combined").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        End If
    Next i
End Sub

Amapola188 · Apr 10th 2018

VBA does not like me! The above didn't really get me anywhere so I have now modified my code to only combine sheets from the 3rd sheet onwards. So I have all my admin sheets first and the data sheets 3 to x.

For this report, I have one "admin" sheet (the pivot table). I add the new data (usually a sheet), then delete the "combined" sheet and run the macro. Seems to work so far! - For the other report, I will change the 3 to 4 and see what happens!

This is what my code looks like now:

Code

Public Sub CombineSheets()


Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(3).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 3 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next


End Sub

Display More

Combine Sheets but leave certain sheets out

Participate now!

Share