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]

You would be correct with the latter. You can reference a sheet with
Sheets("Sheet1")
or
Sheets(1)

the former is very direct, though if ppl change the name of a tab or remove one, there are errors that occur. Sheet(1) is simply referencing the first sheet in order in the book. Not as risky if it is a template that does not change all that often.

Okay, I'm going to clear the contents of sheet "Stock" for the report
I'm going to loop through all sheets in the workbook
If the sheet I'm looping through has "Quanity" in Cell A7 then import all rows
that meet the criteria the Cell in column A being numeric and not blank
--Loop through all lines and copy to destination
--Loop through all sheets in the workbook for the above

Sub My_Summary()
Sheets("Stock").Cells.Clear 'Prep work area
For a = 1 To Application.Sheets.Count 'Check everything
If sheets(a).Range("A7") = "Quanity" then 'typo!?
    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("Stock").Range("A60000").End(xlUp).Offset(1, 0) 'move the to the report
            End If
        End With
    Next b
End if
Next a
End Sub</pre>

Give the above a try, it works for me in a test workbook with a few dozen sheets.

Let me know if you encounter an error or have any further questions,
jc

 

[wpjensen]

Hi JC,
Sub My_Summary()
Sheets("Stock").Cells.Clear 'Prep work area

For some reason I can not get it to do anything pass the above

I have all my sheets named is that what is stopping it from reporting

I have tried a few different things but no results apart from clearing the sheet "Stock"

Sorry to be a pain

Wayne

 

[wpjensen]

JC I have a question in your macro what does sheets(a) refer to

(Cells(b, 1 ) 1 & 6 stands for I guess is # columns

any chance of looking at your test wookbook ????????

Wayne Like to figure this out for my self if I can

 

[jc.021286]

Sorry, I had pasted an older code, guess I did not hit copy. Actual is at the bottom,
my test file, ignore the 2 private subs, I just used those to generate me some random data across 20+ sheets
http://www.filedropper.com/testfile_1

To answer your question,
I have a loop set up "For a = 2 to 10" for instance, this means that inside this loop, I'm going to set a = 2 go through the entire code, and when I hit the end "Next a" a will got to 3, run through the code, 4,5,6,7,8,9,10, once it hits 11, it sees that I wanted to stop at 10 so it will exit this loop.

Sheets(a) is simply 2nd sheet, 3rd sheet, 4th sheet, 10th sheet, Nth sheet.

Cells(b,1) is "B" and "1" where I'm referencing Row, Column,
Column 1 is "A" and my "b" is a variable as with the loop I explained above that lets me loop through vast numbers of data.

In the test sheet, I used a loop inside a loop to generate 200*8*20 instances of random data... so 32,000 random data points for testing.

Hope this helps,
let me know,
jc

Sub My_Summary()
Application.ScreenUpdating = False
Sheets("Stock").Cells.Clear 'Prep work area
For a = 1 To Application.Sheets.Count 'Check everything
If Sheets(a).Range("A7") = "Quanity" Then 'typo!?
Sheets(a).Select
    For b = 1 To Sheets(a).Range("A60000").End(xlUp).Row 'check every line
        With Sheets(a) 'check each line of...
            If IsNumeric(Sheets(a).Cells(b, 1)) And Sheets(a).Cells(b, 1) <> "" Then '...Criteria
                Sheets(a).Range(Cells(b, 1), Cells(b, 6)).Copy Destination:=Sheets("Stock").Range("A60000").End(xlUp).Offset(1, 0) 'move the to the report
            End If
        End With
    Next b
End If
Next a
Application.ScreenUpdating = True
End Sub

 

[wpjensen]

Hi JC,

Thanks very much I owe you one now

Just a little tweaking here and there but working real good
and I learn a lot from you and looking for info on macros

Thanks again

Wayne

Where is the best place to learn or find out macro code, format , names,
and layout ???????

 

[jc.021286]

Best place is inside excel itself,

Simply go to record macro, do what you want, bold, moving, copying, pasting, etc.

After you record the macro, Excel will automatically write the macro code. At this point, if you know how to do loops through for and while statements you can take that up to the next level.

After that, simply doing quick searches through google for a particular problem will be able to help you will all other details.

jc