Good day all, first let me say that I'm not an excel wiz though I can learn quickly. I have a business which uses a daily sales sheet. Every day I open a new sheet (01,02,03, etc..) for each new day of the month. So I've got now about 6 years of individual sheets for every day. What I need is to pull a certain range of data from each of those sheets in it's corresponding monthly folder. Is this possible and would anyone be so kind as to help me out with this please? Thank you.
Ryan
What!
Are you saying that you have circa 2200 daily workbook files split across 72 folders or 72 monthly files one in each of 72 folders?
Is the range of data you want to grab in the same position in every sheet?
Are the workbooks/sheets named consistently, i.e. do they contain a reference to the day, month & year?
How many sales records in a typical sheet. Going forward I'd urge you to record everything in one workbook and preferably a single sheet to facilitate further analysis.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hello Richard, cheers for your response. Yes that's exactly what I'm saying. The way we set up our sales sheet was on a daily excel sheet ages ago and it seems to work great for what we need. Unfortunately this pulling data arose when we needed to get the information from the sheets. I suppose we should have thought ahead a bit!
Yes all the sheets are labelled the as the day of the month i.e, 01,02,03,04th and are kept in a monthly folder, which are in turn kepts in a yearly folder and the name of the sheet and column of data is consistent in every sheet.
Is there a way do pull out all that data? Even if it's a matter or breaking it down into monthly chunks it'd be better than copy and pasting from the daily!
Appreciate your help
Ryan
Ok - I love a challenge....
How much data is on each individual sheet? How many rows and columns? Can you attach a sample of two sheets that are in the same folder? How about March 10 and March 11 in your 2009 folder.
The other question is... what answer are you trying to answer? Total sales? Transactions per day? Inventory control?
Let us see a sheet or two and noodle on the problem awhile... To attach a sample.. click on Go Advanced and then on the Paper Clip Icon above the advanced message area.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hello, attached is a random file from one day previous. Note that file 03 denotes the day of that month. So I have 03 in a march folder and also 03 in an april folder. Will this make things more difficult? What I need to extract from each file is the data in the daily sales by order sheet titled: Name, Address, Phone Number, Post Code, Transaction Type and Marketing. Ideally I'd like that into one master database. Thank you all.
Ok Phaedrus, this problem is doable. It will take some VBA code. Do you want the answer in a large Excel spreadsheet or would Access be better for you?
To get a better answer... What are the exact names of the files and folders? Folder of "2012" has in it "Jan", "Feb" and in these are "1", "2" (or are they "01", "02")
Also - are there any other files in those folders with different and other names?
I'd suggest you add to the data accumulation the Year, Month and Day the transactions happened.
Are you Phaedrus because of Zen and the Art? I loved that book!!
Hi thanks for the reply. Yes I loved that book too and have been using that name ever since! Well I don't know access at all so excel us better cheers. In each monthly file there are the days 01,02,03...and then I have a few other files in there for products, postcodes, monthly summary(only products and financial). Ideally I could integrate this macro into my monthly sales summary and then have another file with all the monthly data which could have another macro to compile a look at the year.
Still having a really hard time figuring this one out..I bet there's someone out there that could do this in 10 minutes!
Hi,
I'm assumed that you only have excel files in these folder and that the data is in the structure you provided in a sheet called "Daily Sales by Order". The following macro will open each file in each folder consecutively, extract the data, put it in the new master sheet, close the file and move on to the next file.
To be honest, all the opening and closing of files could take some time and so I would advise you to do this in batches until you know how long the whole process will take. There are also other ways of extracting data from closed workbooks but it would take me some time to be proficient enough to help you out.
Hope this helps.
abousetta
Please Login or Register to view this content.
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Hi Phaedrus,
Is a path like this: "C:\.......\.......\2006\January.xlsx" and the sheets "01","02" etc
or this:"C:\.......\........\2006\January\01.xlsx" and the sheets "Daily Sales by Order"
or something else??
And how do you want to pull the records???
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks