#1
|
|||
|
|||
Can a daily created worksheet update existing worksheets?
Simple version: Each day a worksheet is created with revenue data. This data is then used to update 22 other standing worksheets. Can Excel be configured to automate this process? Perhaps with macros? Details: Data is collected daily from a series of ATMs. This data is then used to update individual spreadsheets for each machine location. Looking to update the entire spreadsheet set up and wondering if there is a way to automate some of it. I know I can link from many worksheets (files) to one, but can it be done from one to many when the one is newly created (named) each day? |
#2
|
|||
|
|||
Yes ... what you are asking can be done.
Are the existing worksheets that need to be updated all located in the same workbook ? If not ... can they be moved to the same workbook (makes it a lot easier to work with). To get an accurate answer it would be best to post sample workbooks with just a little data to work with. The data does not have to be anything confidential. |
#3
|
|||
|
|||
That's good news.
I have uploaded some sample pages. The Sample - Worksheet is in one file and new data is entered daily. This is saved with a title like "Worksheet 02.05.2018". Then I manually enter that gather data into 22 individual spreadsheets like the second tab. These cannot be combined into one workbook. I would like to not have to enter the data manually but rather have Excel move the data on command. I am expecting to need to open each individual sheet as they would need to be reviewed. Hope this helps with understanding where I need to go. |
#4
|
|||
|
|||
I am hoping that I can get some direction for where to find more information about linking workbooks in this way.
|
#5
|
|||
|
|||
We can see the layout of an individual 1 #xx Coin sheet that you've posted
no doubt the layout of the 2 & 3 #xx Coin sheets are different. How about if in the Individual sample(s), for each column you give the row number of the Revenue cell to be copied ? I suspect macros are the way to go with this but consistency is essential. Hopefully there are only 3 templates to deal with. |
#6
|
|||
|
|||
In the samples, the Worksheet is newly created each day with the file name following the pattern of 'Worksheet mm.dd.yyy' and this is where the data for the day is gathered.
Then there are currently 22 of the individual sheets that the data needs to be distributed into. How can I get the individual sheets to update automatically? |
#7
|
|||
|
|||
@BYahr
how do you currently do this ? |
#8
|
|||
|
|||
Currently, I manually open each of the 22 spreadsheets and enter the data from the day's file "Worksheet mm.dd.yyyy" where the data has been gathered. I've been working on this for quite a while in the spare time between other projects. I have learned a lot. What I have will work between the worksheet and one spreadsheet that I am using as a sample. I think I'll need to store a unique macro in each of the individual spreadsheets which will require the macro to identify the correct worksheet file.
|
#9
|
|||
|
|||
Just thinking out loud here...
You could manually open another workbook that has a list of your individuals, and contains macros to copy and paste as required for the individuals. Using the file picker dialog Application.FileDialog(msoFileDialogFilePicker) you could set the "Worksheet mm.dd.yyyy" file to be used. Then cycle through the list of individuals one at a time calling the appropriate macro to copy and paste the required data for that particular individual. The number of macros required for the individuals was kinda what was eluded to in post 5 PS: row 5 of your sample worksheet has only 21 individuals |
#10
|
|||
|
|||
This might work. I'll have to think about how to get all the pieces put together.
|
#11
|
|||
|
|||
This has taken quite awhile to get working. Thanks for all the ideas and information. I have likely taken a long way around the issue but it is currently working. I've tried the 'file picker' direction but it doesn't seem to be available in this my version of Excel.
Each individual ATM location has it's own macro tied to a button on the combined worksheet. The date issue is handled by selecting the date on the worksheet, copy it to a neighboring cell and change the format from dd/mm/yyyy to dd.mm.yyyyy. Using DIM statements to identify the current day's workbook, then proceeding through the many steps to move data from the worksheet to the destination cells. This has been 'fun' to learn. There are a few more things I can envision Excel being able to accomplish for me and I'm still working on those. Again thank you to the forum for all the assistance. I'm sure I could not have done it without you. |
Tags |
excel links |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
combining worksheets of same xls into a single worksheet | pavan.polish | Excel | 2 | 03-14-2018 06:48 PM |
How to copy workbooks from different worksheets into one new worksheet | abbani | Excel | 3 | 12-12-2016 04:09 AM |
Combining different worksheets into a single worksheet. | jimmy2016 | Excel Programming | 4 | 10-15-2016 09:05 AM |
Combine values from different worksheets into a single worksheet. | jimmy2016 | Excel | 2 | 10-06-2016 09:15 AM |
Combine or merge multiple worksheets into one worksheet | timomaha | Excel | 1 | 07-21-2014 01:02 PM |