Run macro over multiple worksheets

[wpjensen]

I am having trouble getting this macro to run over multiple worksheets
I have tried lost of ideas but can't get it right.
Just learning to write macros but this has me beat

Would also like to know how to restrict just to 3 or 4 columns in the Report(Stock)

This works perfectly for the sheet listed (Cap 2)

I think the lines in blue need to be changed for multiple sheets

Wayne

Sub CopyRowsWithNumbers()
' report Macro
' Macro recorded 2/23/2011
'
Dim X As Long
Dim LastRow As Long
Dim Source As Worksheet
Dim Destination As Worksheet
Dim RowsWithNumbers As Range
Set Source = Worksheets("Cap 2")
Set Destination = Worksheets("Stock")
With Source
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 8 To 155
If IsNumeric(.Cells(X, "A").Value) And .Cells(X, "A").Value <> "" Then
If RowsWithNumbers Is Nothing Then
Set RowsWithNumbers = .Cells(X, "A")
Else
Set RowsWithNumbers = Union(RowsWithNumbers, .Cells(X, "A"))
End If
End If
Next
If Not RowsWithNumbers Is Nothing Then
RowsWithNumbers.EntireRow.Copy Destination.Range("A2")
End If
End With
MsgBox "Data has been updated !!", vbInformation, "Transfer Done"

End Sub

 

[jc.021286]

Could you explain what you are doing? How many sheets you are working with? ect.

For a = 2 to Application.Sheets.Count
    Set Destination = sheets(a)
    '.... your code here
Next a

 

[wpjensen]

There is around 12 sheets

Trying to list all the components that I have, from all the data sheets
into a Report sheet

Wayne

 

[jc.021286]

so Simply,
Give a report of all items in 12 sheets in a report sheet?

Report = Destination
Summary sheets are 12 of them?

for each sheet you need to go through all items in column A for columns
x = 8 to 155 or are you only checking A8:A155?

If the row item in column A is Numeric and not blank then copy to your report page?

Is that the gist? if so, I can draft you up a quick macro to do just that.

jc

 

[wpjensen]

JC Report the rows that have numbers in column A only (Approx 6 per worksheet), in current workbook, to a new worksheet (Report)

Some rows are 30 columns wide want to limit to 6 columns wide

Update there are 30 worksheets and around 200 rows

Hope this is a bit clearer now

Wayne

My macro does this for one sheet now and reports the whole row

 

[jc.021286]

I'll make the following assumptions, please advise if different
1st sheet is your summary report
next 30 sheets are your data to be summarized into the report
you only wish for items in column A that are numbers to be summarized
you do not want to import the entire line, the first 6 cells in that row would suffice

Please save before using this!

Sub My_Summary()
Sheets(1).Cells.Clear 'Prep work area
For a = 2 To Application.Sheets.Count 'start at 2 as we'll skip 1 which is the report itself
    For b = 1 To Sheets(a).Range("A60000").End(xlUp).Row 'check every line
        With Sheets(a) 'check each line of...
            If IsNumeric(Cells(b, 1)) And Cells(b, 1) <> "" Then '...Criteria
                Range(Cells(b, 1), Cells(b, 6)).Copy Destination:=Sheets(1).Range("A60000").End(xlUp).Offset(1, 0) 'move the to the report
            End If
        End With
    Next b
Next a
End Sub

 

[wpjensen]

JC I'll make the following assumptions, please advise if different
1st sheet is your summary report No it is not but if it makes it easier I could try and to rearange
next 30 sheets are your data to be summarized into the report Yes
you only wish for items in column A that are numbers to be summarized Yes
you do not want to import the entire line,No the first 6 cells in that row would suffice Yes
Just a Question can you pick indivualy cells like 2,3,5,7 & 9 ?

Wayne

 

[jc.021286]

Yes, that is possible,
As you can see, I have 2 main structures

for 2 to total # of sheets
and then I'll use "a" as my marker.
If let us say you have 200 sheets
okay, as long as it is the last 30, then set it to for a = 170 to 200
the destination I have set as Sheets(1)
You can change this to the exact name Sheets("My Report") inside the double quotations

If your 30 sheets have some specific Identifier such as in cell B2 of each sheet something such as "My Summary" is written there, then we can go pick those up.
Otherwise, you'd have to individually list out the sheets in some manner... (not ideal with large data sets)

for looking at something logical like every other line, we can just add inside
b = b + 1 inside the loop,
Thus, if we ran through it from the start where b = 1...
... it runs through the code and right before the Next b we have
b = b+ 1
Next b
We have b = b + 1 which is b is now equal to 1 +1 = 2 and we hit the next line which is Next b where we'll add another 1
So we effectively go 1,3,5,7,9

Though There would be a need for some logic on what lines ought to be looked at.

jc

 

[wpjensen]

Code:


Sub My_Summary()Sheets(1).Cells.Clear 'Prep work areaFor a = 2 To Application.Sheets.Count 'start at 2 as we'll skip 1 which is the report itself For b = 1 To Sheets(a).Range("A60000").End(xlUp).Row 'check every line With Sheets(a) 'check each line of... If IsNumeric(Cells(b, 1)) And Cells(b, 1) <> "" Then '...Criteria Range(Cells(b, 1), Cells(b, 6)).Copy Destination:=Sheets(1).Range("A60000").End(xlUp).Offset(1, 0) 'move the to the report End If End With Next bNext aEnd Sub
</PRE>
The above macro does nothing pass! Sheets(1).Cells.Clear 'Prep work area

Something else i notice that when you Rename tabs and move them the ORG sheet # remains the same
When referring to sheets in a macro is it the ORG sheet # or the new Tab Name ????

 

[wpjensen]

The word Quanity in cell A7, is common in all sheets to be Reported

Wayne