copy from an open workbook to ThisWorkbook without reopening the second workbook - VBA error 9

0
votes

I am having some difficulties with a piece of code where I try to copy some data from an open workbook with name ("petros20190118.xlsm") to the workbook where I run the macro (Thisworkbook).

The cell C4 contains the string "20190118".

The error I am getting is "Run-time error '9': Subscript out of range" at row "Set x = Workbooks("petros" & filedate & ".xlsm")"

Sub Copy_Paste()

    Dim x As Workbook
    Dim filedate As String

    filedate = ThisWorkbook.Sheets("Instructions").Range("C4")
    ThisWorkbook.Sheets("Sheet0").Range("A2:V1000").ClearContents
    Set x = Workbooks("petros" & filedate & ".xlsm")
    x.Sheets("Sheet5").Range("A2:V1000").Copy
    ThisWorkbook.Sheets("Sheet1").Range("A2").PasteSpecial xlPasteValues
    x.Sheets("Sheet2").Range("A:S").Copy
    ThisWorkbook.Sheets("Sheet3").Range("A:S").PasteSpecial xlPasteValues

End Sub
2
excelvba
To display code, select it and then hit the {} button in the menu. – MBB70
To display commands and other code-related stuff within your sentences, flank the string you want to stand out with the back-tick character (i.e. Shift ~) – MBB70
Are you sure that's where the error is occurring? You are missing a quote in the line above (around Sheet0). – MBB70
Also, do some looking around here on SO for methods avoiding the use of Copy/Paste to move range data around. Setting ranges equal to each other is the more efficient approach from a memory perspective. – MBB70
Try adding this just before where you get the error. It will tell you whether that book is open or not. -- -- Dim n As Long For n = 1 To Workbooks.Count MsgBox ("n=" & n & " bk=" & Workbooks(n).Name) Next n – donPablo

2 Answers

0
votes

The filedate is correct.

I found the error, it gives the 9 error because the second workbook is open in a new excel window and it is not in the same as the first one. When I am at "ThisWorkbook" and click on open file, the VBA code works without any issues. I am using Excel 2016.

So, now I have to figure out how to put a statement that searches for open workbooks regardless if it is in the same window or not. If someone knows, feel free to help me. Thanks – Petros

0
votes

ThisWorkbook, ActiveWorkbook and almost all others

  • ThisWorkbook is always the workbook where the active VBA code resides.
  • ActiveWorkbook changes if you select or open another workbook.
  • You can address a workbook also by its filename

By this you may find out all opened workbook's names of your Excel instance:

Private Sub DebugAllOpenedWorkbookNames()
    Dim wb As Workbook
    Dim i As Integer
    
    For Each wb In Application.Workbooks
        Debug.Print "Normal Workbook: " & wb.Name
    Next wb
    
    For i = 1 To Application.ProtectedViewWindows.Count
        Debug.Print "Protected Workbook: " & _
            Application.ProtectedViewWindows(i).Workbook.Name
    Next i
End Sub

If you have a workbook open which is not shown by that code, it is openend by another instance. Then look here please : Can VBA Reach Across Instances of Excel?