Hi everybody,
I have an Excel workbook with several sheets.
I want to copy a range of data to a certain range in another workbook.
Both workbooks are in the same directory.
Example : If a certain cell in the first workbook has value "1", then copy from sheet LayoutF56 the range from W16 to W69, open the other workbook (F57.xlsx), paste the copied data to G2.
Then save and close F57.xlsx
After this, : return to starting sheet.
The problem is that I always run into "Error 9 - Subscript out of range" at line "Windows("F57.xlsx").Select"
I spend hours trying to fix it....... no luck.
I make the macro and write the code and test it in Office 2019 pro. It has to work in Office 2013 too.
I am no expert like the people over here
Sub CopyF57()
'
' CopyF57 Macro
' Copies data naar F57 workbook
'
'
If Sheets("HomeF56").Range("J8").Value = 1 Then
Application.ScreenUpdating = False
Sheets("LayoutF56").Select
Range("W16:W69").Select
Selection.Copy
Windows("F57.xlsx").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H2").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("HomeF57").Select
End If
End Sub
I have an Excel workbook with several sheets.
I want to copy a range of data to a certain range in another workbook.
Both workbooks are in the same directory.
Example : If a certain cell in the first workbook has value "1", then copy from sheet LayoutF56 the range from W16 to W69, open the other workbook (F57.xlsx), paste the copied data to G2.
Then save and close F57.xlsx
After this, : return to starting sheet.
The problem is that I always run into "Error 9 - Subscript out of range" at line "Windows("F57.xlsx").Select"
I spend hours trying to fix it....... no luck.
I make the macro and write the code and test it in Office 2019 pro. It has to work in Office 2013 too.
I am no expert like the people over here
Sub CopyF57()
'
' CopyF57 Macro
' Copies data naar F57 workbook
'
'
If Sheets("HomeF56").Range("J8").Value = 1 Then
Application.ScreenUpdating = False
Sheets("LayoutF56").Select
Range("W16:W69").Select
Selection.Copy
Windows("F57.xlsx").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H2").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("HomeF57").Select
End If
End Sub