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

[zaska]

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.

Please Login or Register  to view this content.

Thank you.

[bakerman2]

Please Login or Register  to view this content.

[zaska]

Thank you very much

[bakerman2]

You're welcome and thanks for the rep.

[zaska]

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

[bakerman2]

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 ?

[zaska]

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"

Please Login or Register  to view this content.

Thank you

[bakerman2]

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

Please Login or Register  to view this content.

[zaska]

Thank you Sir.

[bakerman2]

No probs.