Help with this code please!

[jordanburch]

Public Sub openfiles()
Dim data_wbk1 As String
Dim data_wbk2 As String
Dim data_wbk3 As String
Dim data_wbk4 As String
Dim data_wbk5 As String
Dim data_wbk6 As String
data_wbk1 = InputBox("Enter FY IE FY20", Default:="FY20")
data_wbk2 = InputBox("Enter month IE 08-MAY20", Default:="08-MAY20")
data_wbk3 = InputBox("Enter month Name I.E. MAY20:", Default:="MAY20")
'Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Monthly Suspense Recon\" & data_wbk1 & "\" & data_wbk2 & "\" & "CARS" & "\" & data_wbk3 & " " & "CARS Detail Transaction Lines.xls*")
data_wbk4 = InputBox("Enter FY IE FY20", Default:="FY20")
data_wbk5 = InputBox("Enter month IE 08-MAY20", Default:="08 - MAY 2020")
data_wbk6 = InputBox("Enter month Name I.E. YYYYMM:", Default:="202005")
'Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\CARS to HQARS\HQARS Files\" & data_wbk4 & "\" & data_wbk5 & "\" & "IDARRS - Suspense_" & data_wbk6 & "_NIPR Version.xls*")


Workbooks(data_wbk3 & "CARS Detail Transaction Lines.xlsx").Activate

Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Workbooks( _
"IDARRS - Suspense_" & data_wbk6 & "_NIPR Version.xlsx").Sheets(1)
Windows("data_wbk3 CARS Detail Transaction Lines.xlsx").Activate
ActiveWindow.Close
Application.WindowState = xlNormal
Windows("IDARRS - Suspense_" & data_wbk6 & "_NIPR Version.xlsx").Activate
Sheets("Sheet1 (2)").Select
Windows("Suspense automation.xlsm").Activate

End Sub

its erroring out on the workbooks(data_wbk3 & "CARS Detail Transaction Lines.xlsx").Activate
line

I dont think its pulling the variable correctly. It give the subscript is out of range error.

 

[jordanburch]

ok so that worked when I redid it and made sure I clicked it to activate it. Now I am moving on to the next piece. It is saying subscript out of range.

Windows("IDARRS - Suspense_202005_NIPR Version.xlsx").Activate
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "data_wbk7" & " " & "data_wbk4" & " " & "CARS"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "data_wbk7" & " " & "data_wbk4" & " " & "IDARRS"
Range("H394").Select

I am trying to dynamically name the tab based on the variable. It seems like its not working right. Giving me a subscript out of range error.

 

[Joe4]

You put "data_wbk7" and "data_wbk4" inside of double-quotes.
This make them literal text. If you want the variables, you meed to not put double-quotes around them.

Note that if you use the "MsgBox" trick I showed you earlier in this thread, you would have noticed that pretty quickly.
These debugging tricks are very useful, so you want to be sure to incorporate them when you run into these kind of issues.