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.

 

[Joe4]

An easy way to check that is to put a MsgBox line before the line giving you an error to see what it is really returning, and then check to make it matches your file name exactly, i.e.
MsgBox data_wbk3 & "CARS Detail Transaction Lines.xlsx"

 

[jordanburch]

that worked thanks there needed to be a space added. Now for the rest of it. I recorded it. what do you think about it? It is erroring out on

Sheets("Sheet1").Select

 

[Joe4]

Do you have a sheet visible in that file name "Sheet1"?

 

[jordanburch]

yes its visible its not hidden or anything like that

 

[Joe4]

Are you sure that file is active at that point in time, and the macro is not on another file?
You could see what file and sheet name it is currently on by putting these lines above that one that is erroring out:
MsgBox ActiveWorkbook.Name
MsgBox ActiveSheet.Name

 

[jordanburch]

the macro is in another file. I thought it opened the file and made it the active file when
Workbooks(data_wbk3 & "CARS Detail Transaction Lines.xlsx").Activate

that happens. it is open for sure.

I am not sure why it isnt activating it, but it shows the file that the macro is in as being the active file which is not the file I want to copy the sheet from.

 

[Joe4]

When you open a file, that should become the active file. But if you open other files after that, the last opened file would be the active file, unless you activate a different one.
So it all depends on where in your code you put these commands.

 

[jordanburch]

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

seems like it activates it correctly, but when I run the code it gives me a 400 error message.

Here is the code I run after that line and that is all the code that is in the script


Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Workbooks( _
"IDARRS - Suspense_&data_wbk6&_NIPR Version.xlsx").Sheets(1)

i cant figure it out

 

[Joe4]

I don't think you can copy across Workbooks without activate the other one first.

I would recommend trying this way:
1. Create the new sheet in the "IDARRS -..." Workbook first
2. Go back to the Workbook you are copying from
3. Activate Sheet1
4. Add your Copy command
5. Activate your "IDARRS -..." Workbook
6. Go to the new sheet you just created
7. Go to cell A1
8. Paste