Solved Replace a worksheet with one from another workbook

December 7, 2012 at 08:20:23
Specs: Windows XP

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?


See More: Replace a worksheet with one from another workbook

Report •


#1
December 7, 2012 at 08:29:44

I don't know what you mean by:

"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."

Everyday for a month? What month? How will this be determined?

...override the ws in the main workbook... What does "override" mean? I've never heard of one worksheet "overriding" another.

We need some more detail before we can offer any suggestions.

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


Report •

#2
December 7, 2012 at 10:05:36

Well, the workbook is for a month, so let's start on December. Every working day of December, I want the user data worksheet to replace the data worksheet in the main workbook. The name of the wb is either "QA Master Dec 2012" or "QA Raw Data Dec 2012." The name of the worksheets I want to use is "Raw Data" in both wbs.

Each day of the month, the users open their (it's a group) worksheet and add a row of data for each case they worked that day. They do the same thing the next day so the ws grows every day. The columns are used to T, so it's a lot of fields.

So 12/1, no data. 12/2, data from the first day; 12/3, data from the first two days; by the 6th, I want to see the data for the first 5 days of the month. On December 7th, I want to see the data for the first 6 days of the month. If one of the users realizes they made a mistake on yesterday's data and corrects it, I don't want to just take what they did the day before, I want the entire worksheet to update in the master. That's why I said I wanted it to override the ws in the master workbook every day. (I'm looking at yesterday's data and fir the first 4 working days of the month, we have 47 rows filled out.)

I didn't want to have to copy/paste the worksheet, I thought there might be a function or script I could use. Does this make more sense, DerbyDad?


Report •

#3
December 7, 2012 at 11:39:17
✔ Best Answer

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.


Report •

Related Solutions

#4
December 7, 2012 at 12:53:30

That looks great, DerbyDad! I'll plug it in and see what happens before marking this solved. I appreciate your guidance!

Report •

#5
December 7, 2012 at 14:38:41

I strongly suggest that you try this in a backup copy of your workbook. Since it is going to delete a sheet upon opening, you don't want that to happen in your only copy in case things go terribly wrong.

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


Report •

#6
December 7, 2012 at 15:33:31

I followed that advice to and I'm glad I did because I'm doing something wrong.

This is my code:
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:= _
"J:\CommercialAccountOpening\Quality Control\Metrics\2012\Master Metrics\QA Raw Data User WB Dec 2012.xlsx"
'Copy user Raw Data sheet to Raw Data Master sheet
Workbooks("QA Raw Data User WB Dec 2012.xlsx").Sheets("Raw Data").Copy _
Before:=Workbooks("QA Master Dec 2012.xlsm").Sheets(1)
'Close user Raw Data
Workbooks("QA Raw Data User WB Dec 2012.xlsx").Close
'Enable Alerts
Application.DisplayAlerts = True
End Sub

And I keep getting errors. It deleted the ws okay but didn't replace it. I get this error: Run-time error '-2147352565 (8002000b)':
The specified dimension is not valid for the current chart type.

Then I click debug and get this line highlighted in yellow:
Workbooks("QA Raw Data User WB Dec 2012.xlsm").Sheets("Raw Data").Copy _
Before:=Workbooks("QA Master Dec 2012.xlsm").Sheets(1)

Do you see what I'm doing wrong?


Report •

#7
December 8, 2012 at 13:14:08

Well, the first thing you did wrong was not follow the request to click the blue line at the bottom of my post before posting VBA code in this forum. Please be sure to do that next time.

As far as the error, I don't know why you are getting a chart related error. Is the user Raw Data sheet a chart sheet or is it just a regular worksheet? Is the Sheet(1) of the Master workbook a chart sheet?

The only other thing that comes to mind is a shared workbook being an issue. I only mentioned that because you said that many users add data to the Raw Data sheet. Is it a shared workbook? I still don't know why you would be getting a chart related error, but I know that there are restrictions when a workbook is shared.

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


Report •

#8
December 10, 2012 at 05:57:38

Sorry about the blue line mistake. I read over it and will follow those directions next time.

And as for the error: yes, both the master and the user are shared docs. The master has 9 ws and also 3 hidden ws and it includes charts but the worksheet itself, Raw Data, doesn't in either workbook. Raw Data does supply the numbers used on the chart ws though. Would that be why I'm getting the error? Any idea how to fix it?

Or, is there a different way you'd do this? Replacing the entire page seemed easiest to me but I could be going about this the wrong way.


Report •

#9
December 10, 2012 at 09:25:29

The first thing I would try is to create an unshared copy of each file and run the macro using those. If it works, share one or the other and try it again.

While I don't know why, or even if, a shared document would produce a chart related error, it's a fairly simple test to eliminate that as the cause.

The fact that you do have charts in both workbooks could indeed be the issue, but it would be next to impossible for me to replicate your environment for testing.

The only other suggestion I have to to hide and protect the other sheets in the Master workbook and only allow users to access the Raw Data sheet for updates.

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


Report •

#10
December 20, 2012 at 12:22:13

Before I could get this problem fixed, management changed the specifications on me and I didn't need to complete the wb. Just wanted to thank you for your time anyway, DerbyDad. Much appreciated!

Report •


Ask Question