98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » IF function - with partial worksheet name | Excel forum
IF function - with partial worksheet name | Excel forum
Resolved · High Priority · Version 2010
Fri 27 Nov 2015, 16:53Delegate Freddie said...
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
Mon 30 Nov 2015, 10:23Trainer Jens said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Missing Field handleIf your field handle goes missing all you need to do is go to tools > options > edit tab and then make sure that the check boxes for paste and insert buttons are checked. |
