asked on

Create Summary Page Shenanigans

Hello all,

I have a worksheet called "AUT". It is the fourth worksheet in the workbook. I would like to copy the range (A26:R43) for all worksheets post the fourth worksheet, underneath each other, into the worksheet titled "AUT". This will create a summary page that has all the ranges (A26 :R43) from all the other worksheets.

Any help would be greatly appreciated.

Many thanks, Alan.

theras2000

Attach the workbook, or a demo if it's private.

dlmille

Try this: and see attached spreadsheet demo

The app GatherData goes through every worksheet, copying the range specified. note, it clears the AUT tab of all data before starting.

Sub GatherData()
Dim mySheet As Worksheet
Dim myOutputCursor As Range
Dim rSize As Integer, cSize As Integer

    rSize = Range("A26:R43").Rows.Count
    cSize = Range("A26:R43").Columns.Count
    
    Sheets("AUT").Select
    Cells.Clear
    
    Set myOutputCursor = ActiveSheet.Range("A1")
    
    For Each mySheet In Application.Worksheets
        If mySheet.Name <> "AUT" Then
            
            myOutputCursor.Resize(rSize, cSize).Value = mySheet.Range("A26:R43").Value
            Set myOutputCursor = myOutputCursor.Offset(rSize, 0)
            
        End If
    Next mySheet

Open in new window

Cheers,

Dave
GatherDataAUT-r1.xlsm

Canders_12

ASKER

Theras2000;

It is sensitive :/ will try to Mock up a quick demo.

Dave;

Almost ideal, is there an easy way to tweak code so it all gets the information for all worksheets that are after the AUT worksheet?

Many thanks.

dlmille

Ok - please be clear. You want to gather information for worksheets AFTER AUT - but that same range, right?

Dave

Canders_12

ASKER

That is correct, apologies for my ambiguity. If it helps "AUT" will ALWAYS be the fourth worksheet.

Canders_12

ASKER

That is correct, apologies for my ambiguity. If it helps "AUT" will ALWAYS be the fourth worksheet.

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Canders_12

ASKER

Dave,

Excellent solution. Many many thanks for your swift replies.

dlmille

This one's a bit more efficient - starting with the AUT sheet (versus finding it), then going to the sheets.count end...

code (and see attached file):

Sub GatherData()
Dim myOutputCursor As Range
Dim rSize As Integer, cSize As Integer
Dim countSheets As Integer, i As Integer

    rSize = Range("A26:R43").Rows.Count
    cSize = Range("A26:R43").Columns.Count
    countSheets = ActiveWorkbook.Sheets.Count
    
    Sheets("AUT").Cells.Clear
    
    Set myOutputCursor = Sheets("AUT").Range("A1")
    
    For i = Sheets("AUT").Index + 1 To countSheets
        myOutputCursor.Resize(rSize, cSize).Value = ActiveWorkbook.Sheets(i).Range("A26:R43").Value
        Set myOutputCursor = myOutputCursor.Offset(rSize, 0)
    Next i
    
End Sub

Open in new window

Dave
GatherDataAUT-r3.xlsm

Canders_12

ASKER

Above and beyond the call of duty.... Thank you :)