Help with Macro, copy Multiple Worksheets into an Overall Worksheet

[suttonutd]

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?:

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

 

[steve the fish]

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".

 

[suttonutd]

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