0

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
TylerH
  • 20,799
  • 66
  • 75
  • 101
petros
  • 1
  • 2
  • 1
    To display code, select it and then hit the `{}` button in the menu. – MBB70 Jan 25 '19 at 16:57
  • 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 Jan 25 '19 at 16:59
  • Are you sure that's where the error is occurring? You are missing a quote in the line above (around `Sheet0`). – MBB70 Jan 25 '19 at 17:03
  • 1
    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 Jan 25 '19 at 17:05
  • 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 Jan 25 '19 at 17:43
  • donPablo I tried it, but it says that I have open only one workbook, the "This Workbook" and not the second one. Probably because the second one is in a different window. Do you know how to select workbooks that are in different windows? – petros Jan 28 '19 at 13:42

2 Answers2

0

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?

Community
  • 1
  • 1
Asger
  • 3,822
  • 3
  • 12
  • 37
  • same issue, it works perfectly when I have opened wb2 from the wb1(destination/ThisWorkbook) and do File-Open-petros20190118.xlsm(wb2). But when I open wb2 as double click from the folder, it opens in a new excel window (application) and the macro gives error 9. – petros Jan 28 '19 at 14:20
  • yeah the macro is in wb1 and the error is shown in the row that I set wb2 = .... I run the macro by opening the module in wb1 and run the module that the code is in. I have tried running it by assignining the code to a box and click on the box, but the issue is the same. – petros Jan 28 '19 at 14:35
  • I have already changed it to wb1 = ThisWorkbook. Same issue. I receive the error and highlighted yellow row at set wb2 = ... even before the call code. The wb1 has the Instructions sheet always.Probably, because it is in a different excel window it can't see it as open (maybe issue with excel 2016?). – petros Jan 28 '19 at 15:13
  • hm, same issue, the row after the "If wb2 is Nothing then" is highlighted with yellow, error 9. I tested the name by changing the code to "Set wb2 = Workbooks.Open(path-petros" & filedate & ".xlsm) and it opens the spreadsheet without any issues. But I want it to work even if it is already open. Probably when I open it from the folder, it is opened in a view other than the normal or protected one. Could we see somehow in what view the wb2 is opened? – petros Jan 28 '19 at 16:21
  • I think it's 2 excel instances, for example I can drag wb2 to my second monitor without taking the wb1 workbook together. I opened it just with double click from the folder, but it seems it opens it to a new excel instance. – petros Jan 28 '19 at 16:45
  • I also run the debug code, I can see in the normal workbooks only WB1 and not wb2 – petros Jan 28 '19 at 16:47
  • Then please check this https://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel and come up with a comment here later. I'll adapt my answer accordingly and delete some of my comments as they are misleading or unnecessary – Asger Jan 28 '19 at 16:51
  • ok i finally managed to make it work. I searched online and I changed the code to "Set wb2 = GetObject("Path\petros" & filedate & ".xlsm")" and it worked fine! – petros Jan 28 '19 at 17:35
  • Thanks for all your help Asger! – petros Jan 28 '19 at 17:35
0

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

petros
  • 1
  • 2