Loop through Worksheet with certain names

[pikeman56]

Hello all - I need help with a macro. I have a workbook with multiple worksheets. I need to create a macro to copy data from certain worksheets Ex: Range(A4:F20) only from worksheets with a name that starts with the number 5. Example the names can be 5432, 5542, 5998 etc. I'm having trouble getting my loop to work.

Thanks.

 

[Candyman8019]

Can you share what you have so far?

 

[pikeman56]

Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets
If WS.Name Like "5" & "*" Then
Range("CA5:CO589").Select

End If
Next WS

 

[Candyman8019]

I think you’re close. Try something like this:

Private sub copydata()
Dim WS As Worksheet

For Each WS In Worksheets
If WS.Name = "5*” Then
WS.Range("CA5:CO589").copy destination:=sheet12.range(“A1”)

End If
Next WS
End sub

I did this on my phone so I haven’t tested it out…but should be close. Also…I have no idea where you want to paste to so you’ll need to update your destination.

 

[pikeman56]

The destination Sheet is named GL Detail.

 

[pikeman56]

I tested the above loop just to see if I can select the correct ranges and get the following error.

 

[Candyman8019]

The problem there is there is a weird " after the 5*. Replace the quotes and it should run fine.

 

[Candyman8019]

Here's what I ended up with that worked:

Private Sub copydata()
Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets
   If WS.Name Like "5*" Then
        WS.Range("A5:Z20").Copy Sheets("GL Detail").Range("A1")
   End If
Next WS
End Sub

 

[Candyman8019]

Taking that one step further...I assume you'll want to paste the range to the next available row on GL Detail. Give something like this a try.

Private Sub copydata()
Dim WS As Worksheet
Dim lrow As Long

For Each WS In ActiveWorkbook.Worksheets
   If WS.Name Like "5*" Then
   lrow = Sheets("GL Detail").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        WS.Range("A5:Z20").Copy Sheets("GL Detail").Range("A" & lrow + 1)
   End If
Next WS
End Sub

 

[pikeman56]

Awesome. This works perfectly. I had to change a few of the ranges etc but it's good to go. Thanks a ton!!!