Use Current Workbook

[mlindquist]

What is the correct way to write the VBA to have myWorkbook be equal to the current active workbook instead of a user entering a name. The user will be required to be on the workbook to do all the steps that are coming up. I thought of instead of having them entering the workbook name we could have it use the active workbook. Everything I tried isn't working.

Dim myWorkbook as Workbook: set myWorkbook = ThisWorkbook

ALSO TRIED

Dim myWorkbook as Workbook: set myWorkbook = ActiveWorkbook

Here is my original code

Sub CopySheets()

myWorkbook = InputBox("Enter Workbook Name to Copy Worksheet onto - ex. 06-2022HireRightInvoice - do not include .xlsx")

ActiveWorkbook.CheckCompatibility = False
 
Workbooks("HirerightInvoice_SplitMacro").Sheets("Confirmation Form").Copy _
    After:=Workbooks(myWorkbook & ".xlsx").Sheets(Workbooks(myWorkbook & ".xlsx").Sheets.Count)
        
Workbooks("HirerightInvoice_SplitMacro").Sheets("Dispute Reasons").Copy _
    After:=Workbooks(myWorkbook & ".xlsx").Sheets(Workbooks(myWorkbook & ".xlsx").Sheets.Count)

Sheets("Confirmation Form").Select

Range("C18:C29").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=DisputeReasons"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
End Sub

 

[Fluff]

This should work

Dim myWorkbook As Workbook: Set myWorkbook = ActiveWorkbook

In what way isn't it working for you?

 

[Joe4]

Both those lines should work.
What makes you think that they don't work?

Note that once you do that, it is a Workbook variable, and not a string anymore, so you wouldn't reference it like a string, i.e.
this whole reference:

Workbooks(myWorkbook & ".xlsx")

would be replaced by this:

myWorkbook