Home/ Questions/Q 3308
Next
In Process
Computing Staff
  • 0
Asked: 2021-11-03T12:19:35+00:00 In:

Replace A Worksheet With One From Another Workbook

  • 0

I have a wb with 11 worksheets and each ws has multiple formulas. The first worksheet is called ‘Raw Data’ which the users put their data in and the other sheets are calculating things like errors, closures, etc.

Because of all the formulas, we want to keep the users out of the workbook and have given them their own with just a ‘Raw Data’ page. There is one formula on this ws which calculates how long it’s taken to work each case. (It’s column Q and the formula is =IF(C3=””,””,IF(D3=””,TODAY()-C3,D3-C3)) )

Everyday for a month, the user Raw Data worksheet needs to override the ws in the main workbook so we can get the numbers off the other ws.

Does anyone have any ideas?

Share

    1 Answer

    1. What you are asking for can be done, with various levels of automation.

      If you place a version of the code shown below in the ThisWorkbook module of the Master workbook, it will perform the following tasks:

      1 – Delete the current Raw Data sheet from the Master workbook
      2 – Open the user Raw Data workbook
      3 – Copy the Raw Data sheet from the user workbook to the Master workbook as the first worksheet
      4 – Close the user workbook

      As written, the code will do all of this automatically every time the Master workbook is opened.

      If you want more control, you can eliminate the 2 DisplayAlerts lines and you will be warned before the Raw Data sheet in the Master workbook is Deleted. You can choose to have it Deleted or you can keep the original and add a copy of the User version.

      If you want even more control, you can run similar code “manually” by not using it as a Workbook_Open event.

      Obviously you’ll need to change the workbook names, sheet names and paths to match your environment. I’ve simply provided a generic shell so that you can see what the code could look like.

      Private Sub Workbook_Open()
'Disable Alerts
   Application.DisplayAlerts = False
'Delete Raw Data sheet In Raw Data Master
   Sheets("Raw Data").Delete
'Open user Raw Data workbook
   Workbooks.Open Filename:= _
        "C:\DerbyDad03\My Documents\Raw Data User.xlsx"
'Copy user Raw Data sheet to Raw Data Master sheet
   Workbooks("Raw Data User.xlsx").Sheets("Raw Data").Copy _
      Before:=Workbooks("Raw Data Master.xlsm").Sheets(1)
'Close user Raw Data
   Workbooks("Raw Data User.xlsx").Close
'Enable Alerts
   Application.DisplayAlerts = True
End Sub

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0