Combine Sheets but leave certain sheets out

  • 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?

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

    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
  • 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

  • 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.

    Thanks, Christine

  • See if this gets you anywhere.

    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
  • 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:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!