Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-24-2015, 02:29 PM
Jess709 Jess709 is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2013
Novice
Macro to Pull Data From Multiple Files
 
Join Date: Apr 2015
Posts: 13
Jess709 is on a distinguished road
Default Macro to Pull Data From Multiple Files

This is my first time posting on this forum, so please excuse my ignorance regarding this process. I need assistance with building a Macro. I've attached the sample files.



I am attempting to automate a process which is currently manually entered into Excel. We receive daily balance sheets from our 21 stores and I need to have certain figures pulled from the balance sheets into two other Excel files. I've included an attachment of the sample files. Please assume that I will house all the files in the same folder, to make things simpler.

What I need to accomplish is:
Pull the contents of cell C3 from the "Daily Balance" file and enter it into cell B4 in the "sales Sheet" file. I also need the contents of C3 from the "Daily Balance" file entered into B17 in the "Long Form April" file.

This would be an example of 1 day's balance sheet. I would need to duplicate the macro to complete this same task for the other days of the month. This would mean that C3 on the "Daily Balance" spreadsheet would remain constant, but the cells in the "Sales Sheet" and "long form April" would change based on the day of the month in which the daily balance sheet is for.

Please let me know if any additional information is needed to provide assistance.

Thank you in advance!!
Attached Files
File Type: xlsm Daily Balance 4.15.15.xlsm (12.9 KB, 9 views)
File Type: xlsm Long Form APRIL.xlsm (12.9 KB, 10 views)
File Type: xlsm SALES SHEET.xlsm (64.5 KB, 9 views)
Reply With Quote
  #2  
Old 05-11-2015, 10:17 AM
charlesdh charlesdh is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

In the file for Long April". I do not see it as being date sensitive.
Can you provide info on this.
Reply With Quote
  #3  
Old 05-11-2015, 10:41 AM
Jess709 Jess709 is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2013
Novice
Macro to Pull Data From Multiple Files
 
Join Date: Apr 2015
Posts: 13
Jess709 is on a distinguished road
Default

The data would need to be put into the line corresponding with the day of the month, which is listed in column A.
Reply With Quote
  #4  
Old 05-12-2015, 10:26 AM
charlesdh charlesdh is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Thanks for the info.
To me I think your best approach would be have a "Stand alone" program. Being you will have different workbooks to open.
Will all of your files be in the same folder in your system? If so it would be easier to program.
Also when you want to run the program will you want to select the "Daily Balance" for that day?
Reply With Quote
  #5  
Old 05-12-2015, 10:36 AM
Jess709 Jess709 is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2013
Novice
Macro to Pull Data From Multiple Files
 
Join Date: Apr 2015
Posts: 13
Jess709 is on a distinguished road
Default

Charles,
Here are the answer to your 2 questions.

1. Right now the files are not all in the same folder, but they can be if that is easiest.

2. Yes, I will want to pull figures from that day's "daily balance" sheet and have those figures put into the "long form april" and "sales sheet" files. There is a balance sheet file for every day from 21 different stores and each store has their own long form file.

Thanks!
Reply With Quote
  #6  
Old 05-12-2015, 10:57 AM
charlesdh charlesdh is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

HI,

Thanks for the update.
I'll see what I can do. It may take several days.
Someone else on the forum may also help. That would be fine.
Reply With Quote
  #7  
Old 05-12-2015, 11:03 AM
Jess709 Jess709 is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2013
Novice
Macro to Pull Data From Multiple Files
 
Join Date: Apr 2015
Posts: 13
Jess709 is on a distinguished road
Default

Fantastic, thank you so much for your help. There is no specific deadline for this, just something I wanted to try out to improve a manual process.
Reply With Quote
  #8  
Old 05-12-2015, 11:15 AM
charlesdh charlesdh is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

One more question.
How are the workbooks that you are using ( updating) named?
Reply With Quote
  #9  
Old 05-12-2015, 12:14 PM
Jess709 Jess709 is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2013
Novice
Macro to Pull Data From Multiple Files
 
Join Date: Apr 2015
Posts: 13
Jess709 is on a distinguished road
Default

The "long form" file that gets updated is named "Long Form" and then the Month. The sales sheet will be named based on which ad sale the figures are from. For instance the most recent one was named "Spectacular Spring Sales Sheet #2"
Reply With Quote
  #10  
Old 05-13-2015, 11:34 AM
charlesdh charlesdh is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,


I understand the "Long" form. But, for the "Sales Sheet" I do not quite get it.
When you open the "Balance Sheet" is there a location that will tell you if it's for the
Quote:
"Spectacular Spring Sales Sheet #2"
There needs to be away to determine which Sales sheet to open.
Reply With Quote
  #11  
Old 05-13-2015, 12:08 PM
Jess709 Jess709 is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2013
Novice
Macro to Pull Data From Multiple Files
 
Join Date: Apr 2015
Posts: 13
Jess709 is on a distinguished road
Default

Right now the "sales sheet" file is not saved with any specific info in the file name. I plan to update date to correspond with the name of the ad, such as "Spectacular Spring Sales Sheet #2"
Reply With Quote
  #12  
Old 05-13-2015, 12:42 PM
charlesdh charlesdh is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

I need to know of a way to code for the "Sales Sheet". Right now I have a code that will allow you to select the "Balance Sheet" in the folder on your drive. And when you open the Balance sheet I was planning on it to open the "Long Form" and what ever the "Sales sheet" is. That is why I wanted to know how to look for the Sales sheet.
Do want to be able to select the "Sale Sheet" then have the code run. Or, I can set the code that would allow you select the files that you want to update.
Reply With Quote
  #13  
Old 05-13-2015, 12:50 PM
Jess709 Jess709 is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2013
Novice
Macro to Pull Data From Multiple Files
 
Join Date: Apr 2015
Posts: 13
Jess709 is on a distinguished road
Default

It would probably be cleaner if I can select the sheets I want to update.
Reply With Quote
  #14  
Old 05-16-2015, 11:53 AM
charlesdh charlesdh is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Jess709,

Hi, am about done with your file. But, I need someway to determine the "Sale" sheet workbook.
You have a workbook partly named "Long Form" I can key in on that. But, as mention I need to find away to key in on the "Sale" workbook.
Reply With Quote
  #15  
Old 05-18-2015, 06:04 AM
Jess709 Jess709 is offline Macro to Pull Data From Multiple Files Windows 7 32bit Macro to Pull Data From Multiple Files Office 2013
Novice
Macro to Pull Data From Multiple Files
 
Join Date: Apr 2015
Posts: 13
Jess709 is on a distinguished road
Default

How about if you use "Sales Sheet April"? Then each month the sales sheet name would be updated.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to Pull Data From Multiple Files Run a macro on multiple docx. files Peter Carter Word VBA 27 12-15-2022 04:10 PM
Macro to pull data out of a Word table shanemarkley Word VBA 20 05-07-2015 01:36 PM
How Do I Pull Slides from Multiple Files rain264 PowerPoint 6 04-21-2015 12:11 PM
Macro to Pull Data From Multiple Files looking for macro for multiple files bolk Word 3 05-03-2011 05:46 AM
Macro to Pull Data From Multiple Files macro to pull data from multiple files psrs0810 Excel 2 10-25-2010 01:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:11 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