Part of the code not executed by the macro

Question

Thanks a lot for your help! Unfortunately, I tested you code and I got the following error message: Run time error 9 Subscript out of range

It seems in fact that this line causes a problem: Set wbThis = ThisWorkbook Due to this problem, it seems that "Sheet1" is not recognized in my current workbook (I checked it via a debug print in my immediate window), I consulted this topic: Subscript out of range when referencing a worksheet in another workbook from a variable. That is why I modified "Set wbThis = ThisWorkbook" by "Set wbThis = ActiveWorkbook" After doing this modification and executing my macro (this time I do not get any error message), the excel file "Parc Vehicule Template.xls" is open but the instruction rng.Copy wsThat.Range("A1") is not executed, It means that my datas are not copied yet from my initial workbook open to my other workbook "Parc Vehicule Template.xls"

Thank you so much In advance for your help. Xavi

I don't understand why you have the Activate, Select, Paste lines in both the copysheet1... and OpenFile subs. Have you tried stepping through your code? — Rey Juna

Siddharth Rout Siddharth Rout · Accepted Answer · 2018-08-07T17:20:51

Set your objects and then work with them. Your life will become very easy. If I was to do the same thing, I would do it this way...

Is this what you are trying? (UNTESTED)

Sub copysheet1tofileParcVehiculeTemplatefortherest()
    Dim wbThis As Workbook, wbThat As Workbook
    Dim wsThis As Worksheet, wsThat As Worksheet
    Dim rng As Range
    Dim fName As String

    Set wbThis = ThisWorkbook
    Set wsThis = wbThis.Sheets("Sheet1")
    Set rng = wsThis.Range("A1:AZ10000")

    fName = "\\ingfs05\data1\GBS Center \52 Migration\ Files\Parc auto Template.xls"

    If Not IsWorkBookOpen(fName) Then
        Set wbThat = Workbooks.Open(fName)
        Set wsThat = wbThat.Sheets("PV template for the rest")
        rng.Copy wsThat.Range("A1")
    End If
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

Also I see that Fname and Workbooks("Parc Vehicule Template.xls") are different. If that is intentional then I guess you are trying this?

Sub copysheet1tofileParcVehiculeTemplatefortherest()
    Dim wbThis As Workbook, wbThat As Workbook, wbTmplt As Workbook
    Dim wsThis As Worksheet, wsThat As Worksheet
    Dim rng As Range
    Dim fName As String

    Set wbThis = ThisWorkbook
    Set wsThis = wbThis.Sheets("Sheet1")
    Set rng = wsThis.Range("A1:AZ10000")

    fName = "\\ingfs05\data1\GBS Center \52 Migration\ Files\Parc auto Template.xls"

    If Not IsWorkBookOpen(fName) Then
        Set wbTmplt = Workbooks.Open(fName)
        Set wbThat = Workbooks("Parc Vehicule Template.xls")
        Set wsThat = wbThat.Sheets("PV template for the rest")
        rng.Copy wsThat.Range("A1")
    End If
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

Part of the code not executed by the macro

1 Answers