Writing to a sheet in a closed external File Possible?

[chazrab]

I'm updating Sheet2 of open Workbook 2025.xlsm through its userform. I want to be able to update a sheet named NEW with the exact same
structure in an external File(Book1.xlsm) so that both sheets reflect exactly the same data at all times.

This has been difficult for me because the code does not always work.
Does the external file need to be opened to be able to write to Sheet NEW in order to update it ?
BTW: linking won't work, because this effort needs to involve 2 way syncing - meaning I should be able to update 2025.xlsm from Book1 when I'm working in
that workbook that won't work since Sheet NEW contains linked formulas from 2025.xlsm.

Possible to do if one Workbook is closed ?

Thx for anyone's help.
cr

 

[jkpieterse]

Which begs the question: Why edit data in two places? I would suggest to only edit in one file, have the other file pull in the data. Saves so much head-ache.

 

[chazrab]

Which begs the question: Why edit data in two places? I would suggest to only edit in one file, have the other file pull in the data. Saves so much head-ache.

Hi Jan - yes sir, that does make good sense nd that's the first thing I thought of - here's the thing - I'm constantly working in two files
back and forth all day long. Sometimes I need to update this file asap so I won't forget important info.

Now, what I have to do is update the open file I'm working in, save it, open the other file with the same sheet and structure
and update that sheet so both sheets will be identical at all times.

The quick and dirty way I've been doing this is just copy the newly updated sheet to the other book, which must be open - with code or manually,
delete the sheet in the other book and replace it with the newly copied sheet. and vice versa for 2 way syncing.

To me, this is an archaic and clumsy way of achieving having two sheets of two different workbooks always be the same.
When you said "pull data" I'm not 100% sure what you mean. As MVPs, You and RoryA are way more experienced than I in finding efficient solutions to pushing Excel to do challenge solutions. So I'll follow any suggestions you have.

cr
Kingwood, Tx

 

[chazrab]

From cr: this looks unbelievably simple - if it works all the time. From CoPilot:

Sub UpdateSheet()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet

    Set wb1 = Workbooks("2025.xlsm")
    Set wb2 = Workbooks("Book1.xlsm")
    Set wsSource = wb1.Sheets("Sheet2")
    Set wsDest = wb2.Sheets("NEW")

    ' Clear existing data in the destination sheet
    wsDest.Cells.Clear

    ' Copy data from source sheet to destination sheet
    wsSource.UsedRange.Copy wsDest.Range("A1")

    ' Save both workbooks
    wb1.Save
    wb2.Save

    ' Notify user
    MsgBox "Data has been synchronized."
End Sub

 

[jkpieterse]

1. Choose which file will be your input
2. Open the other file
3. Insert a worksheet
4. Data, Get Data, From File, From Workbook
5. Select the table or sheet to fetch the info from
6. Follow steps.

Once the table is in the sheet, set it to update when the workbook is opened

 

[chazrab]

1. Choose which file will be your input
2. Open the other file
3. Insert a worksheet
4. Data, Get Data, From File, From Workbook
5. Select the table or sheet to fetch the info from
6. Follow steps.

Once the table is in the sheet, set it to update when the workbook is opened

1. Choose which file will be your input
2. Open the other file
3. Insert a worksheet
4. Data, Get Data, From File, From Workbook
5. Select the table or sheet to fetch the info from
6. Follow steps.

Once the table is in the sheet, set it to update when the workbook is opened

Hi Jan - following your idea, I came up with this solution which seems to work well with minimal code:

Sub COPYADDBKSHEET()
    Application.DisplayAlerts = False ' Disable alerts
    ' Copy Sheet2 to BIBLEAPPBEST.xlsm before Sheet 8
    Sheets("Sheet2").Select
    Sheets("Sheet2").copy Before:=Workbooks("BIBLEAPPBEST.xlsm").Sheets(8)
        ' Select and delete the existing NEW sheet
    Sheets("NEW").Select
    ActiveWindow.SelectedSheets.Delete
    ' Rename the copied sheet to NEW
    Sheets("Sheet2 (2)").Select
    Sheets("Sheet2 (2)").Name = "NEW"
    ' Save the active workbook
    ActiveWorkbook.Save
    ' Reactivate the original workbook
    Windows("2025.xlsm").Activate
    Application.DisplayAlerts = True ' Enable alerts
End Sub

I just recorded a macro and disabled alerts. CoPilot helped, but does not always get it right.
Thanks for all your help.

cr

 

[jkpieterse]

Well, as long as you're happy with the result I'm OK with it. But have you even tried my suggestion? I encourage you to have a look.