CODEWITHSUNDEEP
vba
B. Moore
B. Moore

Reputation: 109

Check if worksheet in workbook has specific name, and change it if it does

I am currently writing a macro that is supposed to take the contents of a specific excel workbook and worksheet and copy them into another worksheet in a different workbook.

Not too difficult, but the problem that I am running into is that the worksheets that I am trying to pull info from have one of two names. They are either Sheet1 or T Points to Measure.

The current fix that I attempting to use (may not be the best) is to check if the worksheet has the name T Points to Measure. If it does then I want to change it to Sheet1 so all worksheets should have the same name.

My current code that I am trying to use is as follows:

Let Copyrange = "T - ATKB000" & serialno & " Ply Count Request"
Set wb2 = Workbooks(Copyrange)
wb2.Activate
If wb2.Worksheet.Name = "T Points to Measure" Then
    wb2.Worksheet.Name = "Sheet1"
End If
Set ws2 = wb2.Sheets("Sheet1")

However I am getting the error:

Run-time error '438':
Object doesn't support this property or method

Upvotes: 2

Views: 401

Answers (1)

Stewbob
Stewbob

Reputation: 16899

Your error is happening on this line of code:

If wb2.Worksheet.Name = "T Points to Measure" Then

specifically at wb2.Worksheet. That is not valid vba syntax as there is no Worksheet method or property on the Workbook object.

There is a Worksheets property, which is what you need. In order to access an individual worksheet, you need to use the Worksheets syntax with an index or loop through each worksheet in the workbook and check the names individually. The if statement below will run without error, but it's kind of redundant.

If wb2.Worksheets("T Points to Measure").Name = "T Points to Measure" Then

Upvotes: 3

Related Questions