How to Add New Worksheet named "Report" and run loop through all the Sheets Except "Report

Hello,

I am keenly looking to learn few things and tweak the below macro.

1. How to add new worksheet "Report"? If it already exist then do nothing

2. How to Loop through all the Sheets Except the Sheet "Report"

3. Suggest any other tweaks If I missed to learn

"For i = 1 To Sheets.Count - 1" This line seems to be incorrect. If I Move the "Report" Sheet from Last to First then The macro becomes messy.

Private Sub Workbook_Open()
'Declare nextblank row
Dim Nextblankrow As Long, i As Long

'CleareContents of Report Sheet
Sheets("Report").Cells.ClearContents

'Loop Through Sheets & Copy Data to reports Sheet
For i = 1 To Sheets.Count - 1
'Identify Lastrow
Nextblankrow = Sheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

Sheets(i).Range("A2:A11").Copy
Sheets("Report").Cells(Nextblankrow, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Next i

End Sub

Thank you.

Private Sub Workbook_Open()
'Declare nextblank row
Dim i As Long

If Not Evaluate("isref(Report!A1)") Then Sheets.Add.Name = "Report"

'CleareContents of Report Sheet
Sheets("Report").Cells.ClearContents

'Loop Through Sheets & Copy Data to reports Sheet
For i = 1 To Sheets.Count
    If Not Sheets(i).Name = "Report" Then
        Sheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(10).Value = Sheets(i).Range("A2:A11").Value
    End If
Next i

End Sub

Thank you very much

You're welcome and thanks for the rep.

Hello,

How to modify the code If I don't know the Exact ranges to be copied in Each Sheet? or If the Data in Each Sheet is Dynamic? Can I Use the Property CurrentRegion? Or UsedRange?

Thank you

Please post example file of how a sheet looks like (desensitize data if needed)
It's always data in column A that needs to copied no matter the length of data ?

No.It's not always Data in Column A that needs to be copied. But, the Starting cell is always "A2"

What I mean to say is for Example

Sheet1 - Data is in Range ("A2:C5") dynamic range which may change next day to ("A2:G25")
Sheet2 - Data is in Range ("A2:F16") dynamic range too
Sheet3 - Data is in Range ("A2:L25") same

and So on

So, If the data in Each sheet is Dynamic then how do I copy the Entire Data from all the Sheets(1,2,3,4,5, etc.,) to the Sheet named "Report". I want to Identify the Dynamic Range in Each Sheet and copy the same to "Report" Sheet.

The Below line is copying only Ten Cells In Column"A"

Sheets(i).Range("A2:A11").Value

Thank you

Try this. I assume you have headers in Row 1 ?

Private Sub Workbook_Open()
'Declare nextblank row
Dim i As Long, nRows As Long, nColumns As Long

If Not Evaluate("isref(Report!A1)") Then Sheets.Add.Name = "Report"

'CleareContents of Report Sheet
Sheets("Report").Cells.ClearContents

'Loop Through Sheets & Copy Data to reports Sheet
For i = 1 To Sheets.Count
    If Not Sheets(i).Name = "Report" Then
        With Sheets(i)
            nRows = .Cells(1).CurrentRegion.Rows.Count
            nColumns = .Cells(1).CurrentRegion.Columns.Count
            Sheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(nRows - 1, _
                    nColumns).Value = .Range("A2").Resize(nRows - 1, nColumns).Value
        End With
    End If
Next i

End Sub

Thank you Sir.

No probs.