I need to copy and paste each day's sales data by month for whole year into another workbook. How do i do this without having to open and copy paste from each book? The data for each day has to be in columns and has to go across . Thanks
I need to copy and paste each day's sales data by month for whole year into another workbook. How do i do this without having to open and copy paste from each book? The data for each day has to be in columns and has to go across . Thanks
It would probably help if you provided a few samples of what you are working with - and what you want?
Sounds like you have many files to pull from...please give more detail on that too?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks for your response.
Attached is a sample file with before and after tabs. Before tabs has the data that i need to copy and put it in the format as shown in the after tab. I need to copy each days's sale as shown in the before tab and then paste it into the after tab which shows each day across columns for the month of January 2014. Thanks for your help.
Not sure I understand what you want? Looks to me, apart from a few mis-matched rows, that is what you wlready have - are you not just repeating yourself?
If you want to just directly reference on After, what you have on Before, just use...
=Before!A17
Without any row labels, I see no way to "find" what you want
Okay, perhaps i didnt explain this correctly. What you are seeing in the sample worksheet is the 2014 data that is already done by someone else. I need to replicate this for 2015 data.
here is the issue: Each days data shown here in the columns is in a separate file by itself so i need to open each day's file and copy and paste it into a new workbook ...i have to do that for each day, each month for the 12 months. That will be a lot of files to open and copy and paste it manually. That is why i was asking the forum for any quicker way to do this? Thanks
wow - why???? It would be SO much simpler if all the data was in 1 file - and preferably in 1 sheet - that is how excel is designed to work bestEach days data shown here in the columns is in a separate file by itself
excel can easily pull in data from another file (even if it is closed), but you need to hard-code the file name into the formula.
Agreed. I am new at this company and for now i have to go with the flow.
Can you please show me how to hard code the file name into the formula. Thanks
That would be something like this...
='[Sales.xlsb]Walk in Sales'!D2
File Name
='[Sales.xlsb]
Sheet name
Walk in Sales'!
Cell Reference
D2
Keep in mind though that you will have to hard code that for EVERY file.
Another option would be to use INDIRECT() to reference the file name from a date somewhere, but that ONLY works on open workbooks (the source), unless you want to install the MoreFunc add-in, which will allow access to closed work books
find it here...
http://www.ashishmathur.com/tag/morefunc/
I am sorry i am not able to follow this formula. Will this only give me value in cell D2? how do i use this to get values from all 12 mnoths and for each day? Thanks
Like I said, you will need to include each file name (manually) into the formula in the 1st row in each column - then you can just copy them all down.
Because you have so many files (1/day) there really is no easy formula way to do this
Okay Thanks. Appreciate your response.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks