Freddie has attended:
Excel VBA Introduction course
IF function - with partial worksheet name
Hi Guys
I'm trying to use an IF function to find a certain worksheet in a workbook with the below, however the macro keeps falling over at the "If ws.Name Like " part.
Are you able to let me know why it is falling over?
Workbooks.Open (FilePath)
Dim ws As Worksheet
If ws.Name Like "Firm L with Amendments*" Then
ws.Select
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Else: ws.Select "FIRM L (LDN)*"
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End If
End Sub
Thanks!
Freddie
RE: IF function - with partial worksheet name
Hi Freddie,
Thank you for the forum question.
When you work with Object Variables the object variables need to be Set but in your example I will suggest that you put a loop around the code. Try:
Workbooks.Open (FilePath)
Dim ws As Worksheet
For Each ws in ThisWorkBook.Worksheets
If ws.Name Like "Firm L with Amendments*" Then
ws.Select
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ElseIf ws.Name Like "FIRM L (LDN)*" Then
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End If
Next ws
End Sub
I hope this can do what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
