Subscript Out of Range - Copying from workbook to workbook

[emyyra]

I'm pretty new to VBA, so I'm not too familiar with the errors and exact coding rules. I'm getting a Run-time error '9': Subscript Out of Range when I use this macro:

Sub CopyStuff()


Range("d4:I15").Copy
Workbooks("C:\Users\emyyra\Desktop\Random Thing.xlsm").Worksheets("Myyra").Range("d4:I15").Paste


End Sub


'Any suggestions would be appreciated. Thanks!

 

[Timjan]

Try:

Sub CopyStuff()


Range("d4:I15").Copy
Workbooks("C:\Users\emyyra\Desktop\Random Thing.xlsm").Worksheets("emyyra").Range("d4:I15").Paste
End Sub

 

[emyyra]

Try:

Sub CopyStuff()


Range("d4:I15").Copy
Workbooks("C:\Users\emyyra\Desktop\Random Thing.xlsm").Worksheets("emyyra").Range("d4:I15").Paste
End Sub

The sheet in the "Random Thing" workbook that I'm targeting is called "myyra", so "emyyra" won't work since it isn't a sheet in that workbook. I'm trying to have a range of cells copied into a specific workbook.

 

[Norie]

Is the workbook named 'Random Thing.xlsm' open?

If it is you only need the workbook name to refer to it, you don't need the path.

 

[Krayons]

I'm pretty new to VBA, so I'm not too familiar with the errors and exact coding rules. I'm getting a Run-time error '9': Subscript Out of Range when I use this macro:

Sub CopyStuff()


Range("d4:I15").Copy
Workbooks("C:\Users\emyyra\Desktop\Random Thing.xlsm").Worksheets("Myyra").Range("d4:I15").Paste


End Sub


'Any suggestions would be appreciated. Thanks!

I feel as though that Subscript Out of Range error is occurring because of where and how you are trying to call CopyStuff().

I believe the issue is with the following line of code:

Range("d4:I15").Copy

I'm guessing Excel doesn't know which range you are trying to reference. The next line of code shouldn't be returning any out of range errors because you've defined a hard path to the file, worksheet and range:

Workbooks("C:\Users\emyyra\Desktop\Random Thing.xlsm").Worksheets("Myyra").Range("d4:I15").Paste

Could you please advise which workbook, worksheet, and where within your code you make this call to CopyStuff().

Edit: Try defining the workbook and worksheet in the first line of code.

 

[emyyra]

I feel as though that Subscript Out of Range error is occurring because of where and how you are trying to call CopyStuff().

I believe the issue is with the following line of code:

Range("d4:I15").Copy

I'm guessing Excel doesn't know which range you are trying to reference. The next line of code shouldn't be returning any out of range errors because you've defined a hard path to the file, worksheet and range:

Workbooks("C:\Users\emyyra\Desktop\Random Thing.xlsm").Worksheets("Myyra").Range("d4:I15").Paste

Could you please advise which workbook, worksheet, and where within your code you make this call to CopyStuff().

Edit: Try defining the workbook and worksheet in the first line of code.

I tried it with the actual pathway, but then the error was on the first line of code:

Sub CopyStuff()


Workbooks("C:\Users\emyyra\Desktop\First Thing.xlsm").Sheets("Annual Hours").Range("d4:i15").Copy
Workbooks("C:\Users\emyyra\Desktop\Random Thing.xlsx").Sheets("Myyra").Range("d4:i15").Paste


End Sub

The error occurs during the 2nd line of code in my first statement.

 

[Timjan]

try changing this line,

Workbooks("C:\Users\emyyra\Desktop\Random Thing.xlsx").Sheets("Myyra").Range("d4:i15").Paste

to

Workbooks("C:\Users\emyyra\Desktop\Random Thing.xlsx").Sheets("Myyra").Range("d4:i15").PasteSpecial xlPasteAll

 

[Norie]

You do not use/need the path when referring to an open workbook.

Try this.

Sub CopyStuff()

Workbooks("First Thing.xlsm").Sheets("Annual Hours").Range("d4:i15").Copy

Workbooks("Random Thing.xlsm").Worksheets("Myyra").Range("d4:I15").Paste


End Sub

Note - the workbooks 'First Thing.xlsm' and 'Random Thing.xlsm' have to be open.

 

[dmt32]

Not tested but does this help:

Sub CopyStuff()
    Dim wbDestination As Workbook
    Dim CopyRange As Range
    Dim FolderPath As String, FileName As String
    
     Application.ScreenUpdating = False
     
    FolderPath = "C:\Users\emyyra\Desktop\"
    FileName = "Random Thing.xlsm"
    
    Set CopyRange = ThisWorkbook.Worksheets("Sheet1").Range("d4:I15")
   
    On Error Resume Next
    Set wbDestination = Workbooks(FileName)
    If wbDestination Is Nothing Then Set wbDestination = Workbooks.Open(FolderPath & FileName, ReadOnly:=False): Err.Clear
    
    On Error GoTo myerror
    CopyRange.Copy wbDestination.Worksheets("Myyra").Range("d4")
    
'close & save
    'wbDestination.Close True
      
myerror:
    Application.ScreenUpdating = True
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Change the copy sheet name shown in RED as required.

Dave

 

[emyyra]

Not tested but does this help:

Sub CopyStuff()
    Dim wbDestination As Workbook
    Dim CopyRange As Range
    Dim FolderPath As String, FileName As String
    
     Application.ScreenUpdating = False
     
    FolderPath = "C:\Users\emyyra\Desktop\"
    FileName = "Random Thing.xlsm"
    
    Set CopyRange = ThisWorkbook.Worksheets("Sheet1").Range("d4:I15")
   
    On Error Resume Next
    Set wbDestination = Workbooks(FileName)
    If wbDestination Is Nothing Then Set wbDestination = Workbooks.Open(FolderPath & FileName, ReadOnly:=False): Err.Clear
    
    On Error GoTo myerror
    CopyRange.Copy wbDestination.Worksheets("Myyra").Range("d4")
    
'close & save
    'wbDestination.Close True
      
myerror:
    Application.ScreenUpdating = True
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Change the copy sheet name shown in RED as required.

Dave

Dave,
Yes! It does work! Thank you very much! I appreciate it!