• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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.
0
Canders_12
Asked:
Canders_12
  • 5
  • 4
1 Solution
 
theras2000Commented:
Attach the workbook, or a demo if it's private.
0
 
dlmilleCommented:
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
0
 
Canders_12Author Commented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

Dave
0
 
Canders_12Author Commented:
That is correct, apologies for my ambiguity. If it helps "AUT" will ALWAYS be the fourth worksheet.
0
 
Canders_12Author Commented:
That is correct, apologies for my ambiguity. If it helps "AUT" will ALWAYS be the fourth worksheet.
0
 
dlmilleCommented:
Ok - this copies the same range, but for only those sheets that come AFTER the AUT sheet

see attached demo that its working.

Methodology - figure out the # worksheets, then loop through till "AUT" is found, from that point, copy the data.

Here's the code:

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

    rSize = Range("A26:R43").Rows.Count
    cSize = Range("A26:R43").Columns.Count
    countSheets = ActiveWorkbook.Sheets.Count
    foundAUT = False
    
    Sheets("AUT").Select
    Cells.Clear

    
    For i = 1 To countSheets
        If foundAUT Then
            myOutputCursor.Resize(rSize, cSize).Value = ActiveWorkbook.Sheets(i).Range("A26:R43").Value
            Set myOutputCursor = myOutputCursor.Offset(rSize, 0)
        Else
            If ActiveWorkbook.Sheets(i).Name = "AUT" Then
                foundAUT = True
                Set myaut = ActiveWorkbook.Sheets(i)
                myaut.Activate
                Set myOutputCursor = myaut.Range("A1")
            End If
        End If
    Next i
    
End Sub

Open in new window

Enjoy!

Dave
GatherDataAUT-r2.xlsm
0
 
Canders_12Author Commented:
Dave,

Excellent solution. Many many thanks for your swift replies.
0
 
dlmilleCommented:
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
0
 
Canders_12Author Commented:
Above and beyond the call of duty.... Thank you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now