Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-30-2023, 11:15 AM
BYahr BYahr is offline Can a daily created worksheet update existing worksheets? Windows 11 Can a daily created worksheet update existing worksheets? Office 2021
Novice
Can a daily created worksheet update existing worksheets?
 
Join Date: Mar 2023
Posts: 16
BYahr is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 03-31-2023, 11:18 AM
Logit Logit is offline Can a daily created worksheet update existing worksheets? Windows 10 Can a daily created worksheet update existing worksheets? Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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.
Reply With Quote
  #3  
Old 04-01-2023, 12:46 PM
BYahr BYahr is offline Can a daily created worksheet update existing worksheets? Windows 11 Can a daily created worksheet update existing worksheets? Office 2021
Novice
Can a daily created worksheet update existing worksheets?
 
Join Date: Mar 2023
Posts: 16
BYahr is on a distinguished road
Default

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.
Attached Files
File Type: xlsx Sample workbooks.xlsx (34.7 KB, 14 views)
Reply With Quote
  #4  
Old 04-12-2023, 10:17 AM
BYahr BYahr is offline Can a daily created worksheet update existing worksheets? Windows 11 Can a daily created worksheet update existing worksheets? Office 2021
Novice
Can a daily created worksheet update existing worksheets?
 
Join Date: Mar 2023
Posts: 16
BYahr is on a distinguished road
Default

I am hoping that I can get some direction for where to find more information about linking workbooks in this way.
Reply With Quote
  #5  
Old 04-13-2023, 09:58 AM
NoSparks NoSparks is offline Can a daily created worksheet update existing worksheets? Windows 10 Can a daily created worksheet update existing worksheets? Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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.
Reply With Quote
  #6  
Old 04-13-2023, 11:56 AM
BYahr BYahr is offline Can a daily created worksheet update existing worksheets? Windows 11 Can a daily created worksheet update existing worksheets? Office 2021
Novice
Can a daily created worksheet update existing worksheets?
 
Join Date: Mar 2023
Posts: 16
BYahr is on a distinguished road
Default

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?
Reply With Quote
  #7  
Old 04-15-2023, 10:52 AM
NoSparks NoSparks is offline Can a daily created worksheet update existing worksheets? Windows 10 Can a daily created worksheet update existing worksheets? Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

@BYahr
how do you currently do this ?
Reply With Quote
  #8  
Old 06-28-2023, 10:12 AM
BYahr BYahr is offline Can a daily created worksheet update existing worksheets? Windows 11 Can a daily created worksheet update existing worksheets? Office 2021
Novice
Can a daily created worksheet update existing worksheets?
 
Join Date: Mar 2023
Posts: 16
BYahr is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 06-30-2023, 10:27 AM
NoSparks NoSparks is offline Can a daily created worksheet update existing worksheets? Windows 10 Can a daily created worksheet update existing worksheets? Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #10  
Old 07-04-2023, 06:35 AM
BYahr BYahr is offline Can a daily created worksheet update existing worksheets? Windows 11 Can a daily created worksheet update existing worksheets? Office 2021
Novice
Can a daily created worksheet update existing worksheets?
 
Join Date: Mar 2023
Posts: 16
BYahr is on a distinguished road
Default

This might work. I'll have to think about how to get all the pieces put together.
Reply With Quote
  #11  
Old 09-08-2023, 07:10 AM
BYahr BYahr is offline Can a daily created worksheet update existing worksheets? Windows 11 Can a daily created worksheet update existing worksheets? Office 2021
Novice
Can a daily created worksheet update existing worksheets?
 
Join Date: Mar 2023
Posts: 16
BYahr is on a distinguished road
Default

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.
Reply With Quote
Reply

Tags
excel links

Thread Tools
Display Modes


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
Can a daily created worksheet update existing worksheets? How to copy workbooks from different worksheets into one new worksheet abbani Excel 3 12-12-2016 04:09 AM
Can a daily created worksheet update existing worksheets? Combining different worksheets into a single worksheet. jimmy2016 Excel Programming 4 10-15-2016 09:05 AM
Can a daily created worksheet update existing worksheets? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft