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]

You're very welcome.

 

[pikeman56]

Ok. Just ran into an issue. I ran this on some test data and it worked fine but when I applied it to my actual data it didn't work correctly because the data in my sheets are formulas. How can I change the copy paste code to paste values?

 

[Candyman8019]

try something like this...

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).PasteSpecial Paste:=xlPasteValues
   End If
Next WS
End Sub

 

[pikeman56]

Hmm....I got a compile error.

 

[Candyman8019]

sheets("GL Detail"....should be on the next line

 

[pikeman56]

Like this? I've got something wonky in my syntax.

WS.Range("CA5:CO589").Copy sheets("GL Detail").Range("A" & lrow + 1).PasteSpecial Paste:=xlPasteValues
Sheets ("GL Detail")

 

[Candyman8019]

No...exactly how I put it in post #13

Separate the copy and paste portions to different lines

 

[pikeman56]

Oh man....I guess it would help if I read the previous code correctly. LOL. I got it now. Works like a charm. Thanks again.