Copy rows from another file (without opening file) and paste in active file

[mellymelle]

Hello, trying to do the title task (in Phase 1 & 3 of code below), but getting the type mismatch error when running. Please help, thanks in advance!

Sub PhaseOne()


    Application.ScreenUpdating = False
    Call PhaseOneStepOne
    Call PhaseOneStepTwo
    Call PhaseOneStepThree
    Application.ScreenUpdating = True


End Sub




Sub PhaseOneStepOne()


'From the file Performance template (Final).xlsx, Copy rows 36, 37 & 38 and INSERT copied rows into the active client sheet at row 36


    Dim SourceFile As Workbook
    Dim SourceSheet As Worksheet
    Dim TargetFile As Workbook
    Dim TargetSheet As Worksheet


    Set SourceFile = "Performance Template (Final).xlsx"
    Set TargetFile = ThisWorkbook
    Set SourceSheet = SourceFile.Sheets("Template")
    Set TargetSheet = TargetFile.Sheets("Template")
    
    TargetSheet.rows("36:38").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    SourceSheet.rows("36:38").Copy
        TargetSheet.Range("A36").PasteSpecial Paste:=xlPasteFormulas
    
End Sub


Sub PhaseOneStepTwo()


'Value paste rows 39,40 & 41 of the active client sheet


   TargetSheet.rows("39:41").Copy
        TargetSheet.Range("A39").PasteSpecial Paste:=xlPasteValues


End Sub


Sub PhaseOneStepThree()


'From the file Performance template (Final), Copy formulas in cells G7:G15 and PASTE formula into each individual client sheet at G7:G15


    SourceSheet.Range("G7:G15").Copy
        TargetSheet.Range("G7").PasteSpecial Paste:=xlPasteFormulas


End Sub

 

[rlv01]

Sub PhaseOne()
    Dim SourceFile As Workbook
    Dim SourceSheet As Worksheet
    Dim TargetFile As Workbook
    Dim TargetSheet As Worksheet

    'Is the workbook already open?
    For Each SourceFile In Application.Workbooks
        If SourceFile.Name = Workbooks("Performance Template (Final).xlsx") Then
            Exit For
        End If
        Set SourceFile = Nothing
    Next SourceFile

    'If no, then open it
    If SourceFile Is Nothing Then
        Set SourceFile = Workbooks.Open("Performance Template (Final).xlsx")
    End If

    Set TargetFile = ThisWorkbook
    Set SourceSheet = SourceFile.Sheets("Template")
    Set TargetSheet = TargetFile.Sheets("Template")

    Application.ScreenUpdating = False
    PhaseOneStepOne SourceSheet, TargetSheet
    PhaseOneStepTwo TargetSheet
    PhaseOneStepThree SourceSheet, TargetSheet
    Application.ScreenUpdating = True
End Sub

Sub PhaseOneStepOne(ByRef SourceSheet As Worksheet, ByRef TargetSheet As Worksheet)
'From the file Performance template (Final).xlsx, Copy rows 36, 37 & 38 and INSERT copied rows into the
'active client sheet at row 36

    TargetSheet.Rows("36:38").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    SourceSheet.Rows("36:38").Copy
    TargetSheet.Range("A36").PasteSpecial Paste:=xlPasteFormulas
End Sub

Sub PhaseOneStepTwo(ByRef TargetSheet As Worksheet)
'Value paste rows 39,40 & 41 of the active client sheet
    TargetSheet.Rows("39:41").Copy
    TargetSheet.Range("A39").PasteSpecial Paste:=xlPasteValues
End Sub

Sub PhaseOneStepThree(ByRef SourceSheet As Worksheet, ByRef TargetSheet As Worksheet)
'From the file Performance template (Final), Copy formulas in cells G7:G15 and PASTE formula into each individual client sheet at G7:G15
    SourceSheet.Range("G7:G15").Copy
    TargetSheet.Range("G7").PasteSpecial Paste:=xlPasteFormulas
End Sub

 

[mellymelle]

Thanks rlv01, but I'm trying to avoid opening the source file. In any case, tried your code but received a runtime error 9 subscript out of range, on the line:

If SourceFile.Name = Workbooks("MacNicol Performance Template (Final).xlsx") Then

Both the source and target files are saved in the same folder.

 

[mellymelle]

I just modified the code to skip the section of checking if the workbook is open and if not opening it, substituting it with

Set SourceFile = Workbooks("Performance Template (Final).xlsx")

Which works if I have this source workbook open. Still trying to figure out how to do this whole thing without opening the source workbook though, if you (or anyone else) have any ideas. Thanks!

 

[rlv01]

In any case, tried your code but received a runtime error 9 subscript out of range, on the line:

If SourceFile.Name = Workbooks("MacNicol Performance Template (Final).xlsx") Then

Oops. That was my error. I must have been having a brain freeze. To test for an already-open workbook, it should have been

     If SourceFile.Name = "Performance Template (Final).xlsx" Then
            Exit For
        End If

Why don't you want to open the SourceFile workbook? I don't know that you can copy data from sourcefile unless it is open. If you don't want to leave it open, you can close it after you do what you need to do.

 

[mellymelle]

I'm writing the macro for use by someone who is not good with Excel, and the macro would be used to update 200+ files from the Performance Template info so wanted to minimize the number of open workbooks to minimize confusion. I guess I could just add code to close the Performance Template file at the end of the macro, but figured not opening it at all would be more efficient. Have seen threads that talk about this being doable but haven't been able to replicate code that's functional so will likely just stick with your code what with deadlines and all.

Thanks for your help!

 

[Fluff]

It's doable, but never done it myself.
have a look here https://www.rondebruin.nl/win/s3/win024.htm

 

[mellymelle]

Thanks Fluff will check out your link. Cheers!