CODEWITHSUNDEEP
excelvba
Hibari
Hibari

Reputation: 131

Creating a summary sheet from multiple sheets

PROBLEM

I want to iterate through all sheets and create a summary sheet. I have a dynamic column, whereas, some header is on COLUMN X while some on COLUMN AG.

So instead of selecting the specific column, I decided to get the specific header name in order to address that issue.

However, I can't seem to progress on my code since I don't know how to loop all throughout the sheets, get the column then add it to the summary.

CODE

Public Sub forSummary()
Dim ws As Worksheet
Dim lRow As Long
Dim aCell As Range, rng1 As Range

'~~> Set this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("JAN 6")

With ws
    '~~> Find the cell which has the name
    Set aCell = .Range("A1:BA1").Find("TOTAL OUT")

    '~~> If the cell is found
    If Not aCell Is Nothing Then
        '~~> Get the last row in that column and check if the last row is > 1
        lRow = .Range(Split(.Cells(, aCell.Column).Address, "$")(1) & .Rows.Count).End(xlUp).Row

        If lRow > 1 Then
            '~~> Set your Range
            Set rng1 = .Range(aCell.Offset(1), .Cells(lRow, aCell.Column))
            '~~> This will give you the address
            rng1.Select
        End If
    End If
End With
End Sub

Any thoughts?

Upvotes: 1

Views: 302

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

The issue in your code is that you are setting your ws to a specific sheet. Instead, loop through the worksheet collection and do your work that way.

Dim ws As Worksheet, tempRng As Range

For Each ws In ThisWorkbook.Worksheets

    Set tempRng = ws.Range()    'Set your range you need for your summary page

    ' Code to perform actions with your range

    Set tempRng = Nothing

Next ws

Upvotes: 1

Related Questions