CODEWITHSUNDEEP
excelvba
Roca
Roca

Reputation: 69

Select worksheet with non specific name

I am trying to execute a macro on a workbook that is downloaded from the web daily.

It has only one worksheet that changes names accordingly with the date downloaded (say: "vendas 201709294524455").

I need to set this worksheet as a variable. How can I do that with this dynamic name change?

I tried:

Dim Sh1           As Worksheet
Dim Sh2           As Worksheet
Dim FindCell      As Range

Set Sh2 = ThisWorkbook.Worksheets("book1")
Set Sh1 = Workbooks("vendas.xlsm").Worksheets("*vendas*")

LastRow = Sh2.Range("d65536").End(xlUp).Row

'...rest of the code

Upvotes: 0

Views: 584

Answers (2)

CallumDA
CallumDA

Reputation: 12113

Scott Craner's solution is perfect for you. But just for anyone in future -- this is how you would do it if you want to avoid using the worksheet index (which changes if you change the order of the worksheets). Only worth using this method if you have more than one worksheet.

Sub SetWorksheet()
    Dim ws As Worksheet, targetWorksheet As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name Like "*test*" Then
            Set targetWorksheet = ws
            Exit For
        End If
    Next ws

    'use targetWorksheet
    MsgBox targetWorksheet.Name
End Sub

If there are multiple sheets like "test" (in this example), you will get the first one.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

As it only has one worksheet just use:

Set Sh1 = Workbooks("vendas.xlsm").Worksheets(1)

The Worksheets() allows for the index or a string name. With only one sheet the index is always 1 of that one sheet. See: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-object-excel

This will set Sh1 to the first worksheet in the workbook regardless of the name.

Upvotes: 5

Related Questions