Help with Macro, copy Multiple Worksheets into an Overall Worksheet

suttonutd

Board Regular
Joined
Oct 5, 2010
Messages
68
Morning all, I'm an absolute newbie with Macros and have also received a lot of help from previous Postings. I just need this bit of code adapted slightly.

So I have multiple worksheets and I need to copy them into one overall sheet. I have captured some script from another thread, see below. This will copy a line IF a text string is present within column A on the various sheets matches the text string within B1 on the Summary Sheet. What I need to do instead is to copy a line if Col B has text on the various sheets AND to start the search from Row 6.

Any ideas?:

Code:
Sub Summary()
Dim WkSht As Worksheet
Dim r As Integer
For Each WkSht In ThisWorkbook.Worksheets
If WkSht.Name <> "SUMMARY" Then
For r = 1 To 1000
'This will check the first 1000 rows of each sheet
If WkSht.Range("A" & r).Value = Sheets("Summary").Range("B1").Value Then
WkSht.Rows(r & ":" & r).Copy
Sheets("Summary").Range("A65536").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll
Sheets("Summary").Range("B" & Sheets("Summary").Range("A65536").End(xlUp).Row).Value = WkSht.Name
Exit For
End If
Next r
End If
Next WkSht
End Sub
 
Last edited by a moderator:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Lets look at the macro. The For loop starts by saying For r = 1 To 1000. So this means on the first loop the variable r is 1 then on the 2nd loop its 2 then 3, 4, etc. until we get to 1000. Now you are using this r variable in WkSht.Range("A" & r).Value so you are saying on the first loop WkSht.Range("A1"). You want to start at 6 so change the For loop to For r = 6 To 1000. Now you want to compare column B so again using WkSht.Range("A" & r).Value you are comparing whatever is in column "A" so change the "A" to "B".
 
Upvote 0
Hi Steve the fish, thanks for your swift response. I have now amended the Row number but when it comes to comparing I would like the macro to copy a row IF the cell within Col B has a value instead of matching it to a criteria. Any suggestions. Thanks, Steve
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top