Set a variable for workbook name

[Kanger14]

So I need to set a workbook name as a variable as it changes every time an updated version is downloaded.

So far here is the macro I have set. The problem occurs because the workbook name, which today is "MasterMDs-2020-01-27" will change constantly for the current date. Initially thought of just renaming the sheet every time in the code, but then realized a variable could solve the issue. Just uncertain of the syntax as I am not a VBA expert.

   Cells.Select
    Selection.Columns.AutoFit
    Columns("A:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:V").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:AD").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 1
    Columns("A:F").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("MasterMDs-2020-01-27").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("MasterMDS-2020-01-27").AutoFilter.Sort.SortFields. _
        Add2 Key:=Range("A1:A5971"), SortOn:=xlSortOnValues, Order:=xlDescending _
        , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("MasterMDs-2020-01-27").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$1:$F$5971").AutoFilter Field:=2, Criteria1:="TRUE"
    ActiveSheet.Range("$A$1:$F$5971").AutoFilter Field:=3, Criteria1:="TRUE"

Any help is appreciated

 

[Fluff]

Hi & welcome to MrExcel.
Will that sheet always be the active sheet, when you run the code?

 

[Legacy 456129]

Dim wbkName as Workbook

Set wbkName = ThisWorkbook 'or ActiveWorkbook or any other workbook that needs reference'

 

[Kanger14]

Dim wbkName as Workbook

Set wbkName = ThisWorkbook 'or ActiveWorkbook or any other workbook that needs reference'

So would this declaration of the variable occur prior to the column clears? sorry I'm not familiar at all with variables and their syntax with VBA. Thank you!

 

[Kanger14]

Hi & welcome to MrExcel.
Will that sheet always be the active sheet, when you run the code?

Hi thank you! Yes the target sheet will always remain the same. The data it pulls from will have a name variation depending on the date.

For example the shared sheet we use is titled "Renewals" which is what we make public to our employees to work out of. The "MasterMDs-2020-01-27" is raw updated data we pull. We need to bring the updated data from "MasterMDs-2020-01-27" into "Renewals". But the MasterMDs name will vary dependent on the date.

Hope that is enough explanation to go off of!

 

[Fluff]

If the MasterMDs*** sheet will be the activesheet when you run the macro replace

ActiveWorkbook.Worksheets("MasterMDs-2020-01-27")

with

ActiveSheet

 

[Legacy 456129]

*Kanger14

Earliest Dim declaration possible i.e. ASAP when your workbooks get loaded. In this case before the Cells.Select

I generally don't like any VBA reference to a cell or range in any workbook/worksheet without making it blindingly obvious where that cell is pulling from so my book would say

With wbkName.Sheets("xyz")
.Range("abc").Cells.select

The dot before Range signifies it belong to the last 'With' statement in the code so wbkName.Sheets("xyz"). Zero doubt where that is and will run irrespective of where you are in your workbook.

Most of the times a Select is not needed at all... it only slows processing down. Something to read up on v.v. Select and Activate.

 

[Kanger14]

If the MasterMDs*** sheet will be the activesheet when you run the macro replace

ActiveWorkbook.Worksheets("MasterMDs-2020-01-27")

with

ActiveSheet

WOW! Simple ActiveSheet solved the problem! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback