Subtotal 100 tabs of workbook

[cmware12]

I am back to my workbook with 100+ tabs. I need help with taking all but two tabs, performing a copy/paste special values only, then subtotalling based on cell A10 on each sheet.

Thanks

Cathy

 

[Weaver]

Are you saying you need to copy/paste values all but 2 sheets?

What's the names of the 2?

 

[Weaver]

Try this, but use it on a copy of your data first!

Sub summarise()
    Dim summarySht As Worksheet
    Dim specialShts As Variant
    Dim thisSht As String
    Dim thisRow As Long
    'put the names of sheets you don't want to include in the following array
    specialShts = Array("sheet2", "sheet3")
    thisRow = 2
    Set summarySht = Sheets.Add(before:=Sheets(1))
    summarySht.Name = "SuperSummary"
    summarySht.Range("A1:B1") = Array("SHEET", "RESULT")
    For x = 1 To ActiveWorkbook.Sheets.Count
        ss = False
        For y = 0 To UBound(specialShts) - 1
            thisSht = Sheets(x).Name
            If specialShts(y) = thisSht Or thisSht = summarySht.Name Then
                ss = True
                Exit For
            End If
        Next y
        If Not ss Then
            'remove or comment out next 3 lines if you don't want to copy/paste values
            With Sheets(thisSht).UsedRange
                .Value = .Value
            End With
            With summarySht
                .Cells(thisRow, 1) = thisSht
                .Cells(thisRow, 2).Formula = "='" & thisSht & "'!A10"
            End With
            thisRow = thisRow + 1
        End If
    Next x
    With summarySht
        .Cells(thisRow, 1) = "Total"
        .Cells(thisRow, 2).Formula = "=SUM(B2:B" & thisRow - 1 & ")"
    End With
End Sub

 

[cmware12]

I need to copy and paste special, but more importantly, I need to subtotal each sheet by one column, showing three sums. If you think this is something that can be written....I can figure out how to post one of the sheets tomorrow for some additional assistance. With Grouping of the sheets I can easily do the copy paste special values, but it's the subtotalling that is causing a problem because I cannot do this with a "grouping".

thanks

 

[Weaver]

I need to copy and paste special, but more importantly, I need to subtotal each sheet by one column, showing three sums. If you think this is something that can be written....I can figure out how to post one of the sheets tomorrow for some additional assistance. With Grouping of the sheets I can easily do the copy paste special values, but it's the subtotalling that is causing a problem because I cannot do this with a "grouping".

thanks

Yes, that would be useful. There is a 'subtotal' function we might be able to use for this.