I need your help in "for" function.

[Steven]

I have this macro does find all available worksheets, and copies the
used range, and paste into newly created worksheet.
It works fine, but I need a couple of modifications. I will appreciate
your time to read this.

1. I have multiple worksheets named,
1,2,3,4,5,6,1a,2a,3a,4a,5a,.....
Now the macro reads everything, but I need the macro to read every
numbered worksheets (i.e. 1,2,3,4,5,6), and stop when it hits 1a.
I don't need any data from 1a,2a,3a,.....on new worksheet.


2. When new worksheet is generated, a default name is given to this new
worksheet (i.e. sheetX) other than this, I need to assign a name like
"report"

Thank you for taking your time to read this.

 

[Bob Phillips]

1. I have multiple worksheets named,
1,2,3,4,5,6,1a,2a,3a,4a,5a,.....
Now the macro reads everything, but I need the macro to read every
numbered worksheets (i.e. 1,2,3,4,5,6), and stop when it hits 1a.
I don't need any data from 1a,2a,3a,.....on new worksheet.

When you loop through the worksheets, add an IsNumeric test to the loop

For Each sh In Activeworkbool.Worksheets
If Isnumeric(sh.Name) Then
...

2. When new worksheet is generated, a default name is given to this new
worksheet (i.e. sheetX) other than this, I need to assign a name like
"report"

Worksheets.Add.Name = "report"