Auto update saved worksheet from new/current worksheet

[peck55]

I've reposted since no one responded from 2 days or so ago. If this is not possible, perhaps someone can respond with that info? I'm hoping it's truly not too difficult, since it is at the forefront of my new reporting model success.

Here's the problem:
I have a workbook with multiple worksheet tabs. I would like each tab to automatically update from it's master file worksheet located elsewhere on the network drive.

The functionality is so each workbook tab will reflect the 'current' period data/report. The master file worksheet(s) are saved anew each month (reporting period) with the same name and in the same location on the network drive.

I have been able to bring the data over to fill the page using the 'Get external data from other sources', but it doesn't hold the formatting of the original report. The workbook I am re-creating each month will be sent to end-users, so the format needs to be the same as in the master file(s).

Thanks!
Phyllis

[BuckoAk]

Can you upload a sample workbook that contains the desired formatting format.

[peck55]

Thanks for your help! I added a few notes on the worksheet tabs as well. Look forward to your reply` Phyllis

[BuckoAk]

Phyllis,

I'm going to need a little more info. Quote " data connection function is not truly working"

• Is there a macro that updates the Receiving Sheet with the Master Sheet Info? Formulas?
• Are you/someone doing a copy & past?

Quote "it shifts the current worksheet data to the right"

• Is this the Master or Receiving Sheet?
• Is this after Copy/Past, Macro, Formula?

[peck55]

Of course. here are more details.

The worksheet I attached does not have any links or connections, as I hoped to simply share the 2 worksheets that are involved with you.

To explain my remarks and questions, I tried to populate the 'OHSU Operating' worksheet with the new/current month's data from the master file by using the 'Get External Data' function, under the Data tab. When I use the 'from other sources' function, and link the master file location, it will update the OHSU Operating tab with the master file data, but does it by moving the current report data ('May-12' period data) to the right, and adds the master file worksheet in it's place, to include formatting that is not the same or friendly to look at.

Once I created that connection, however, I noticed under the 'connections' properties, there appears to be a way to bring the data over and leave the existing format, but I'm not easily able to reformat the data that came over using, thus allowing the connections properties to be effective for a subsequent refresh. I'm also not sure if each 'refresh' will continue to move what's on the page to the right, which would not be feasible.

As another option, I'm also trying to simply link each cell in the monthly report tab to the master file worksheet (using the =(file/cell reference) and do that formula for cells A1-Z100, for instance. This allows me to maintain the worksheet formatting and just refill the cells based on the master file data. This seems like a decent alternative, but I hoped to more easily use a macro or function that would require less work to setup, if that makes sense. For each of these monthly reports, there could be upwards of 10 or more tabs that will need updating each month to different master files. And we will likely have close to 50+ reports to set-up.

I hope this makes sense.
Thanks,
Phyllis

[BuckoAk]

I had a chance to work with data linking as you described.
When you setup your query, are you linking all of the columns cell for cell.
Thinking on the alternative side of things, it would be easy to setup a macro to import a sheet(s) into your reports workbook. The import would mimic the source in every way, but if your not using all of the data the data linking is your best alternative.

On a side note: when you tell your query where to start, are you telling it A1?

[peck55]

Excellant! Thank you for working on this for me!

My alternative (since I didn't have a macro/function working) was to link cell to cell for all columns. However this results in some cells producing a 0 in the cell if the newest report links to a cell that contains nothing. Given the propensity for possible formatting and other errors such as this, I do feel a macro that would mimic the source in every way is the best solution.

Regarding the 'query' that creates the master report, it actually lands in a central network file location based on our IT dept 'putting' it there each month. The University dbase (Cognos system) generates these monthly reports and the naming convention remains the same each month, basically overwriting the prior month report.

So my monthly report will look to this central file location each month to pick up this particular account from a master Excel report with many tabs, one of which is this 0151-68123 report.

The master report will always be the same with regard to cell A1 and include the 'logo' in the inserted box taking up space from A1-A9.

In creating the macro, I can provide the file path if needed or of course I can subsequently add that to whatever 'template' macro you can send.

Thanks again,
Phyllis

[BuckoAk]

Phyllis,

This is just a dry run to work out any bugs, on the Sheet "ImportData" you will find a command button and a Sheet name to Import.

If Cell B1 & B2 have the correct Sheet names go ahead and press the Import button, navigate to the Master File and select it.
(This file location can be hard coded later, as you said the file name and location stays the same)
This operation will open the file, copy the contents and close without saving.
I am having issues with the image coming in correctly, I will continue to look into this and find a resolution.

Once things are up and running I can setup a dialog with worksheet names for you to choose from to make this operation more user friendly.
Baby steps for now, let me now how it goes.......

[peck55]

Wow, that is just great! Yes it works! Regarding the image, if it's easy to add to the macro to delete the image and shift the 'header cells' B1:L10 to the left, that is completely fine. I realize the source data has many of these cells merged, so that will add a few extra steps as well. Quite honestly, there are merged cells that prove annoying throughout the worksheet, so 'unmerging' within this opening macro would be desirable if possible. When I normally work within these workbooks, I highlight the entire document and unmerge it as a habit anyway.

I am most thankful for your help with this! I'm meeting today to report progress on this project and will be so happy to let them know I am having such great incremental success!
Thanks again,
Phyllis

[BuckoAk]

Phyllis,

Next version with your added comments, give it a run.

On Sheet ImportData you will find (2) columns "A" will contain the Sheet Names Located in the Master(s) Sheet(s)
In column "B" are the New Sheet names, If you add new sheet names just click on the Refresh List Button.
On workbook Open the List will also refresh to make sure things are up to date.
Replace the Sheet names with the correct ones and/or your preferred names and you should be good to go.

[peck55]

Just getting back to this today, so sorry for the delay. I will play with this and let you know how it works and if I have any questions. My first thought is that the 'Master Sheet' will be different for all of the Report worksheet tabs, so am curious if I will need to step into to edit the macro to adjust or if I can play with the Column A names. I'll get back to you with any additional thoughts/concerns. I am so very thankful for your help, Bucko!

[peck55]

The worksheet functions very well, however at present, when I select Import Sheet from Master, it opens the file directory box and I must search and select the 'master file' and then it auto-populates. This results in a minor time savings, since I could not do the macro and merely file open and copy/move from each needed workbook and achieve the same result. It would be great to simply select from the drop down and click on Import Sheet from Master and have it find the file path/location. Is it possible to put the file path to the master file in Column A and have the macro read this to open the file? The absolute ideal solution would be if the file path for all the tabs in the report workbook were identified in column A on the Import Data tab, and I could select Import from Master and have it import/replace all the tabbed workbooks with one click. Is one of these two thoughts possible?

I did find a prior post where someone wrote a macro that automatically replaced files from a master file to a user file when the file date changed. I am happy to provide those macro details, which I saved to a word doc, if that would be helpful in developing this one. I am sadly unable to step into macros and correct when they are this complex without a ton of trial and error, so always happy to let a pro work at it more efficiently!

Thanks again!

[BuckoAk]

Some general quesiton to help me setup the code.

It would be great to simply select from the drop down and click on Import Sheet from Master and have it find the file path/location
Is it possible to put the file path to the master file in Column A and have the macro read this to open the file?

This is possible.
How many Master Files are there?
How many Sheets will be pulled from this Master File(s)?
Will the location of the Master file(s) stay Static?

---

The absolute ideal solution would be if the file path for all the tabs in the report workbook were identified in column A on the Import Data tab, and I could select Import from Master and have it import/replace all the tabbed workbooks with one click

This is possible.
Do the Sheet Names in the Master Files(s) stay Static?
If Yes, I can set it up to import all or some of the Sheets into your current workbook.

Send me the macro or a link to it, I will take a look at it.
Is this how you want your setup?

---

It basically sounds like your making a copy of the Master File(s) and then Re-formatting it to the way you want it to look?

Plan A

• Remove the File Browse Function
• Sheet Names - Column A
• Master File(s) Directory Path - Column B
• Drop Down list & Command Button will import selected Sheet
• Possibly Add additional Command Button to import All Sheets

Plan B

• Use macro to look at date from Master file, update/import All Sheets from master on workbook open action

Both Plans can be hard coded or use the cells to drive the actions, using the cells is more user friendly for non vba users.

[peck55]

Plan A seems the best since each reporting workbook will have static but individual differences as to the source document, especially for the grant reporting, which is continually changing. I've attached a worksheet to clarify the 'source' for each of the tabs on this particular 'test case', however I used column A for the master file(s) path and column B for the current workbook 'tab' name (to clarify from your suggestion above). Please note that I included the particular 'tab' on the master file workbook that this user report needs to include. For instance, on the first file path, the Dept Name.xlsx file has multiple tabs with all the operating accounts and I only need to select the one tab identified as "0151-68123 Detail".

This is the case for all the master files which are automatically sent from our main university dbase to our Dept file location on a monthly 'close schedule' basis.

I do think the 'additional command' to update all sheets with one button push would be the absolutely best solution!

Attached is a current worksheet to help define the paths, though they shouldn't be hard-coded but referred to as discussed.

Thanks again and again and ...

[peck55]

I just realized another piece to this worksheet macro, as I've been spending some time today analyzing your macro and playing with it a bit. The new, monthly report that auto imports will need to replace/overwrite the prior one that is there if it exists This is needed since these individual tab reports are referred to on the first worksheet tab that is a 'combined report', linking to the various individual tabbed reports. Can the macro be written so that it overwrites what is on an existing tab if the tab name be the same? For instance, the tab which is renamed 'Operating Report'. Can the new import overwrite the data on that existing tab?

I noticed this problen since I tried to 're-run' the import function with the the prior imported tab named Operating Report already in the workbook and it does not overwrite the existing tab. It creates a new tab, using the original file name and throws a 400 error.

I also worked with the macro and tried to put the imported tab in specific spots in the workbook, but it seems stuck on putting it in front of Sheet1. No matter if I delete that Sheet1 and use another Sheet as Sheet1, or if I try to update the macro with a new Sheet name in place of the Sheet1 distintion in the macro.

Hopefully you won't tell me to go take a macro class and can help me work through these additional trials and errors` I'm learning a good bit simply by viewing your macro creation! Thanks again, Bucko` Phyllis

[captjwj]

I didn't have (take) the time to analyze the entire thread,.. so sorry if the answer is there already.. But,.. Why can't you just simply directly link the cells to the appropriate cells in the master file? Heck, a simple copy then paste special, links can slave an entire sheet to another sheet. When you open the worksheet, it will ask if you want to refresh, when you do, all the links are updated.

You can "paste special" formats, etc,.. too.

Hope this helps.

Jerry

[peck55]

Hi Jerry... I tried this path originally, however to paste the link doesn't hold the formatting (FY the line formatting on the net margin row) and any blank cell results in a 0 showing. So I used a NA in the link formula to resolve that, but again the underlining of key Revenue/Expense/Net Margin rows does not carry over. And I can't format this into the end-user report as the number of rows will change each month as more lines account for more line item details under these 'key' rows that need to be highlighted for easy reading by the end-user.

I know, this should be easy, but since we will reporting to approx 100 end-users monthly who have anywhere from 3-20 separate accounts each, a defined macro that will auto populate seems the best avenue to pursue~

Thanks! Phyllis

[captjwj]

Hi Phyllis,... Maybe I'm not following,.. but, the formatting won't be affected by the link. So, if the format will remain the same, but the fields will be filled or emply based on data or no... Then formatting any potentially used columns ahead of time isn't a problem. No? If there's no text, then there's no underlining... If there's text, then it's underlined. Perhaps some conditional formatting, based on the cell having data will resolve your issues... Not sure exactly where you're going,.. so.... maybe if you post a copy of your file or an example, I could better see what you mean.

Jerry

[peck55]

I'm probably not explaining correctly, but this solution won't work. The master file that the user report would link to will change every month. For instance, the Net Margin in Jul with the formatted line above and under is on row 25 and next month the Net Margin coming from the master file will be on line 35 as a simple example. Does that make sense?

Bucko is really on track with his solution so far. A single button push to auto populate a possible 20 tabbed reports via a macro makes sure that each report is an exact replica of what the master file contains, with little need to check every page for possible linked hiccups.

Thanks!

[BuckoAk]

Can the macro be written so that it overwrites what is on an existing tab if the tab name be the same? For instance, the tab which is renamed 'Operating Report'. Can the new import overwrite the data on that existing tab?

Yes.

I noticed this problen since I tried to 're-run' the import function with the the prior imported tab named Operating Report already in the workbook and it does not overwrite the existing tab. It creates a new tab, using the original file name and throws a 400 error.

The orginal code was never designed to over wright an existing sheet, this can and will be added per your comments

I also worked with the macro and tried to put the imported tab in specific spots in the workbook, but it seems stuck on putting it in front of Sheet1. No matter if I delete that Sheet1 and use another Sheet as Sheet1, or if I try to update the macro with a new Sheet name in place of the Sheet1 distintion in the macro.

The code is telling the imported sheet to be placed in front of sheet1.
If you want a specific order, please let me know that so I can put that in the code.
Or if it doesn't matter the next imported sheet will be added to the back.

Hopefully you won't tell me to go take a macro class and can help me work through these additional trials and errors` I'm learning a good bit simply by viewing your macro creation! Thanks again, Bucko` Phyllis

I started this journey, I will finish it.
Please keep in mind your project is a big undertaking, there will be lots of trials and errors.
It's a matter of weeding out the good parts that work and reworking the stuff that doesn't, so be patience and we will get something that your happy with and works well.

I will take a look at your latest file and start putting it all together including your comments for another review.
Stay tuned.

[peck55]

Thanks again for your detailed reply. I completely understand the size of this undertaking and am absolutely not in any kind of rush to accomplish. I am just very thankful for your skilled help and commitment to help me with this goal.

So regarding the latest file that I sent, I believe that having the macro refer to the file path that is noted in column A will be the best possible way to ensure that the analysts are easily able to update the reports as grants end and start in any one reporting period/year. And having the tab naming convention look to column B for this report's tab name. (column a/b order doesn't matter,,, but the contents/use of these columns do

As you can see from my latest file share, there are only a few master file general locations (x drive thru Admin), but the specific files for specific reports will vary. Hopefully in the future, both the grant reports and operating reports will 'land' in the same 'Drafts' folder, but we aren't there yet.

Regarding the imported sheet 'location' in this end user report, it seems the 'over-write' function should avoid this general need, however when a 'new' report tab is added, is it possible to have it land in the 'order' that the importdata tab lists? If not, then it can land before or after the importdata tab (though you stated above that it will land by default after the importdatatab). I would like importdata tab to be the 'last tab' in the worksheet. And it would be nice to eliminate the 'sheet1' blank tab.

I hope I have answered any questions you might have. I look forward to your reply when convenient`
Have a superb day,,, Phyllis

[BuckoAk]

Phyllis,

Give this a try,

[peck55]

Back again after a lengthy absence with quarter end and other priorities. So this Working Beta is a fabulous tool. It works perfectly for most of the file paths I add, however, it begins to error out when I attempt to bring in files. I'm tryiing to see what breaks the macro. I'm somewhat worried about it 'breaking' relatively easy, as the end users who will work with this product will not know how to correct it. I do notice in the macro, that there is a reference to '0151-68123' and many of my original location worksheets will not be formatted that way. Many are grant accounts and begin with a 'GCNCR' and are followed by anywhere from 4 to 6 digits/alpha characters. The grant accounts seem to be the ones that are producing the errors.

I am also running into a big problem that can hopefully be resolved as well as a final file share question that I need to work thru.

The big problem is this: I have a 'roll-up' report in the workbook that this macro will be included in. This main page roll-up report includes links to the various worksheets that I will import. When I run the macro and the new worksheet comes in and basically replaces what was there, it breaks the links on the main page roll up report, leaving me with the infamous 'ref' error everywhere.

Is there anyway to correct this?

My other thought is that this workbook will need to be sent to end users and as is, will need to be saved as an xlsm file, due to the macro tab. I'm not sure that will be a friendly file type for all my end users. Does this type of file open in prior Excel versions? Otherwise, I suppose I could add the 'importdata' tab to every file each month, perform the macro, remove the macro, then save it as an xls file and send? If you don't know this answer readily, I can play with these options to find the best workaround, however another question pops up re: copy/move this importdata macro tab into existing workbooks:

If I try to move/copy this workbook tab into another workbook, the 'toolbar tab' does not travel with it, rendering it useless. I thought I might be able to more easily copy this macro tab/worksheet to my working files vs moving all my working files to a copy of this macro tab. Does that make sense?

Thanks again! Phyllis

[BuckoAk]

I do notice in the macro, that there is a reference to '0151-68123' and many of my original location worksheets will not be formatted that way

This information needs to be filled out by you or someone on your end. The orignal info in the cells were for my testing purposes only and also example so you know what info is needed.
Replace the following with YOUR Standard Info
Cell A3 To A## = Network Address
Cell B3 To B## = Excel Tab Name To Retrieve From Other Workbook
Cell C3 To C## = Optional Renaming Section, If you want the tab to have the same name when it is imported then enter the name that is in cell B3.

This main page roll-up report includes links to the various worksheets that I will import

How are you importing? Is this using the Import Macro?
Are the Links embedded into the original sheet thats being Imported?
I may need to see this sheet to help identify an answer.

My other thought is that this workbook will need to be sent to end users and as is, will need to be saved as an xlsm file, due to the macro tab. I'm not sure that will be a friendly file type for all my end users. Does this type of file open in prior Excel versions? Otherwise, I suppose I could add the 'importdata' tab to every file eachmonth,perform the macro, remove the macro, then save it as an xls file and send? If you don't know this answer readily, I can play with these options to find the best workaround, however another question pops up re: copy/move this importdata macro tab into existing workbooks:

Depending on the Office Version: 2010, 2007 = Yes, 2003 thru XP = No; limited save rights.
This kind of information is necessary in the beginning of the Journey, but this can be fixed.
The fix for this is to use an xls file extension and save the macro within the workbook.

If I try to move/copy this workbook tab into another workbook, the 'toolbar tab' does not travel with it, rendering it useless. I thought I might be able to more easily copythis macro tab/worksheet to my working files vs moving all my working files to a copy of this macro tab. Does that make sense?

This will be fixed if we use the xls file extension.

[peck55]

Excel Forum WS example 20121023.xlsm

Attached is an example of the end-user workbook to include the macro tab, an example of 4 reports that would import from the macro tab and the 'rollup report' with the vlookup formulas referencing the 4 individual reports.

The macro keeps producing a run time error '1004' when I try to run it after adding the new file paths for the grant files. I'm not sure if you can replicate this error.

Regarding the links breaking when I run the macro, the links are vlookups built into the roll-up report tab that reference the 4 individual reports.

Normally, if I have this multi-tabbed report and would not use the import macro, I would open and copy the worksheet from the network drive location and paste over the top of the worksheet in this reporting workbook. Doing this copy/paste function does not 'break' the vlookup formulas on the rollup report tab. However when I run the macro, it does break that formula reference because the page it's looking to is being removed and replaced with your macro.

Hope this makes more sense. Thanks again! Phyllis

[punna111]

i want to export data from user excel file to master file. is this possible?

I have one excel file i am maintaining data on date wise.
we have another masterfile in master file one tab is representing my excel file data.
daily i am copying data from my excel, and pasting into master file.

is there any way/macro to automate it.

if i click a button which is on my excel file that data should be update in masterfile.
two files are on network drive only.
but, the condition is without opening the masterfile that data should be saved in masterfile.

can anyone help on this..........

[arlu1201]

punna111,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[BuckoAk]

peck55,

Try this.

[peck55]

Love the page format with the refresh and easy buttons. Sadly, attached is the errror I receive when clicking on either the single import sheet button or the all sheets import button. The error details of course change and note each of the records trying to import. (ie same error noting the 0151-68123 Account, the Service Center Acct, etc)
As background re: my process to update the file paths in Column A to be completely accurate (I leave out a few file path names in your example to help protect the identity of these records). To be sure to not make a typing mistake for file path name in column A, I copy/pasted the address line on the file address toolbar. I included only the name of the Workbook in Column A and did not include any reference in Column A file path to the workbook sheet name. The sheet name is only included in Column B. (Explaining my process for clarity to be sure I'm not missing anything).

Thanks again for your continued support and comittment to this project! You are indeed amazing!
Phyllis

[BuckoAk]

Love the page format with the refresh and easy buttons. Sadly, attached is the errror I receive when clicking on either the single import sheet button or the all sheets import button. The error details of course change and note each of the records trying to import. (ie same error noting the 0151-68123 Account, the Service Center Acct, etc)
As background re: my process to update the file paths in Column A to be completely accurate (I leave out a few file path names in your example to help protect the identity of these records). To be sure to not make a typing mistake for file path name in column A, I copy/pasted the address line on the file address toolbar. I included only the name of the Workbook in Column A and did not include any reference in Column A file path to the workbook sheet name. The sheet name is only included in Column B. (Explaining my process for clarity to be sure I'm not missing anything).

Phyllis,

Is the Macro Security poping up when you open the file?, try Refreshing the List and running the command again.
If this works close the excel file and Right Click on the excel file > Properties > UnBlock > Apply > OK.

My system already know the file, so I'm guessing the macro security is breaking part of the program on your end.
I ran it several times and could not get it to produce the example you gave using that tab.

On startup there is a section that refreshes and stores the data, if the macro security stops this section it might produce what your seeing.

[peck55]

When I open the file, I get the 'update link' popup, stating 'This workbook contains links to other data sources' and I have the option to update or don't update. Whether I click on update or don't update, I get a run-time error '1004'.

I don't see a macro security pop up. Refreshing the List is not changing anything.

I went into the file drop down menu and opened the trust center settings, where under the Macro settings, there were some blocks, so I unblocked all, closed all files, refreshed the list, still to no avail.

I'm including word doc of the macro break to see if that helps you. I also notice some odd items under Name Manager, which I have believe has caused me issues in the past. Could there be a problem there? Including an image of the name manager as well.

Thanks again` I'm stuck

[BuckoAk]

Not sure whats going on, try the updated file.
Nothing new added, I turned off the On workbook open function.
When the excel file opens click on the refresh button, then select a item from the drop down list, run the command.
Let me know the results, See below & double check the following

Things to note:
You stated that you copy and past from the address bar to prevent typing mistakes for the network address.
>Did you by chance double check the source sheet Tab name to make sure that it matches your receiving sheet Tab name?
>Be sure to refresh the list any time an item is added or removed.
>All Cells Must contain information i.e. A8 & B8 & C8 are Required Cells per Each Row.

• Network Address - Row A
• Source Tab Name - Row B
• Receiving Tab Name Or Rename - Row C

Name Manager:
My code is not using the Name Manger or anything in it.

Would it be possible to get a copy of the file(s) in question with all the private info stripped out, if so be sure not to rename or delete any tabs if possible.

[peck55]

Hi again,

Still getting the same silly error! Dang! Anyway, I've attached your last file, updated with the naming convention I used as well as a master file that I put together for the sake of this excercize that similates the records that need to be retrieved. Hopefully you can find what the cause is. I continue to appreciate your efforts here, as I understand this has become a thorn suddenly! I feel it is just one small thing that we are overlooking.

Things to note:
You stated that you copy and past from the address bar to prevent typing mistakes for the network address.
>Did you by chance double check the source sheet Tab name to make sure that it matches your receiving sheet Tab name? Yes, in fact I have even copy pasted the tab name to be sure.
>Be sure to refresh the list any time an item is added or removed. Yes, I refresh
>All Cells Must contain information i.e. A8 & B8 & C8 are Required Cells per Each Row. Yes, I always have accurate info in all columns A,B,C[/I]•Network Address - Row A
•Source Tab Name - Row B
•Receiving Tab Name Or Rename - Row C


Would it be possible to get a copy of the file(s) in question with all the private info stripped out, if so be sure not to rename or delete any tabs if possible. Yes, I've attached a mockup of the master file.

I am curious if I need to include the tabsheet name in my directory path in column A, but I even tried this and it didn't help

Thanks again!
Phyllis

[BuckoAk]

Phyllis,

I figured it out, I missed one variable.
Give it another go and let me know how it works on your end.

[peck55]

You most certainly did figure it out! So far so good. I'm at month end again, so a bit busy to really try it out with a few report changes, etc.... but I think we have a working model! Once again... multiple thanks! You are a rock star. I tried to add to your reputation again here, but can't (states I must spread the love around on this forum, however you are the only one I've been dealing with... so will just spread the love here in my reply!)

Thanks again,
Phyllis