VBA: Copy/Paste without opening destination File (Need Speed Boost)

[ragnar12]

I've written the below code which accomplishes the task....but given that I will need to loop it through 300+ lines (creating that many different files) I want to find a way to speed it up. Before publishing I'll add the LastRow bit, but for testing purposes I've kept it to the 3 rows. Any suggestions would be appreciated.

I thought about creating the files first, then opening each file to insert the information. While it created the files quickly it still ultimately took about the same amount of time due to having to loop back through opening the files to pass the information along (but the code was longer, so I switched back to the below). Is there a way to push information into a closed file? I can't seem to find the method if so.

Sub Create_Workbook()

Dim i As Integer

ScreenUpdating = False

For i = 3 To 5
    
    'Open template
    Workbooks.Open "U:\1 - Projects\2023 AOP Budget\Executive Summary\2021 Executive Summary - Property ID.xlsx"

        'fill in Property Overview
        Workbooks("Executive Summary - File Builder").Worksheets("List of Files").Cells(i, 2).Copy _
        Workbooks("2021 Executive Summary - Property ID").Worksheets("Property Plan-Hospital Update ").Cells(3, 5)
        
        Workbooks("Executive Summary - File Builder").Worksheets("List of Files").Cells(i, 3).Copy _
        Workbooks("2021 Executive Summary - Property ID").Worksheets("Property Plan-Hospital Update ").Cells(3, 2)
        
        Workbooks("Executive Summary - File Builder").Worksheets("List of Files").Cells(i, 4).Copy _
        Workbooks("2021 Executive Summary - Property ID").Worksheets("Property Plan-Hospital Update ").Cells(4, 2)
        
        Workbooks("Executive Summary - File Builder").Worksheets("List of Files").Cells(i, 6).Copy _
        Workbooks("2021 Executive Summary - Property ID").Worksheets("Property Plan-Hospital Update ").Cells(3, 8)
        
        Workbooks("Executive Summary - File Builder").Worksheets("List of Files").Cells(i, 7).Copy _
        Workbooks("2021 Executive Summary - Property ID").Worksheets("Property Plan-Hospital Update ").Cells(4, 8)
        
        'On/Off Campus
        If Workbooks("Executive Summary - File Builder").Worksheets("List of Files").Cells(i, 5).Value = "On Campus" Then
            Workbooks("2021 Executive Summary - Property ID").Worksheets("Property Plan-Hospital Update ").Cells(4, 5) = "On"
        End If
        If Workbooks("Executive Summary - File Builder").Worksheets("List of Files").Cells(i, 5).Value <> "On Campus" Then
            Workbooks("2021 Executive Summary - Property ID").Worksheets("Property Plan-Hospital Update ").Cells(4, 5) = "Off"
        End If
    
    'Save and rename file
    ActiveWorkbook.SaveAs Filename:="U:\1 - Projects\2023 AOP Budget\Executive Summary\" & "2022 Executive Summary - " & Workbooks("2021 Executive Summary - Property ID").Worksheets("Property Plan-Hospital Update ").Cells(3, 2).Value & ".xlsx"
    
    'Close file
    ActiveWorkbook.Close

Next


ScreenUpdating = True
End Sub

 

[Joe4]

The only way I have seen to update a closed Excel workbook with hard-coded data without opening it up if the data is arranged in a database-like structure..
Then you could use ADO in VBA. See here: Use Excel VBA to change the value of a cell in a closed workbook?
Everything else I have seen involves VBA opening up the workbook, updating it, and closing it.

 

[ragnar12]

Thanks Joe, I'll play around with that. The destination worksheet isn't laid out like a DB, but maybe I can create a hidden worksheet to pass the information to and then pull from.