I have a weekly task where i need to report progress on 17 asset areas each
with 7 of their own workbooks, progress is simply looking at number of null
cells per workbook etc. The problem being that each of the workbooks have
been split up to make the size of the data more manageable. Depending on the
workbook some have been split into 40 tabs!

So rather than running the progress macro on each of the 40 tabs i want to
some way copy each of the tabs and dump this into one tab and then run the
progress macro.

Any help would be great - if this could be completed through some sort of
macro it would be brilliant. Each of the s/s have the same number of columns
etc

Cheers Pete

Hi,

Please put on examples, by that it will easier to understand your query.

"Little pete" wrote:

I have a weekly task where i need to report progress on 17 asset areas each
with 7 of their own workbooks, progress is simply looking at number of null
cells per workbook etc. The problem being that each of the workbooks have
been split up to make the size of the data more manageable. Depending on the
workbook some have been split into 40 tabs!

So rather than running the progress macro on each of the 40 tabs i want to
some way copy each of the tabs and dump this into one tab and then run the
progress macro.

Any help would be great - if this could be completed through some sort of
macro it would be brilliant. Each of the s/s have the same number of columns
etc

Cheers Pete

Hi Pete,

The following code ignores your request to copy them to a new sheet b/c
if you don't actually use the completed sheet for anything I don't
think it is necessary. However, if it is used, create code for copying
the required range to a "Master Sheet" & insert this in the "For Each"
construct (I've provided a possible solution). This solution will pop
up std Excel warnings if there is not enough space to copy data etc.

Sub RunProgressMacroForAllSheets()
Dim sh As Worksheet
Dim LastRow As Long
Dim MasterSheet As Worksheet
Set MasterSheet = ActiveSheet 'change if needed

For Each sh In ActiveWorkbook.Worksheets
LastRow = MasterSheet.Cells(rows.Count, "A").End(xlUp).Row
sh.UsedRange.Copy MasterSheet.Range("A" & LastRow + 1)
Next sh
'enter the code of your "progress macro" here
End Sub

Note, if you normally run the progress macro on the active sheet,
you'll probably need to change all the phrases "activesheet" to
MasterSheet.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=527083