VBA to pull data from multiple closed workbooks

good afternoon forum!

was needing some help with a VBA issue as i have only recently worked with VBA

I am putting together a 60 day report... pulling data from closed workbooks 60 days prior to current day.

in attached, i have the file name in cell A1 of sheet 1-60 (this cell uses TODAY function so will change daily)... example of complete filepath: C:\Users\Jean\Documents\Reports\Standard Daily Report 6-23-15.xlsm

was needing to paste sheet 1 of the closed workbooks (these sheets are titled "Stats for Day") to the appropriate sheet (1-60) of the attached workbook (with the exception of Row 1, because this contains file name... So everything from row 2 to the next row with empty cell)

all sheet titles of closed workbooks are consistent and the name of these workbooks are consistently in cell A1 of sheet 1-60 of attached workbook.

Any help is GREATLY appreciated!!!

My question would rather be - why do you have 60 worksheets? Have you considered putting ALL your data/inputs into 1 file/sheet, and then running summaries from that?

No, I did mean your live data, collecting that all in 1 workbook/worksheet.

If that is not practical (I understand it isnt always), then perhaps take a look au the Morefunc add-in, it allows use of INDIRECT from closed workbooks

when i send workbook to others... will they need morefunc to view the data?

Yes, it is an add-in that is specfic to that "install"

I have asked for help from other experts for you....have not encountered that before

Keith

two things

does your data for previous days change? it looks/sounds like you can just append the new days and delete the one that drops off instead or trying to get new set of 60 every single day?

secondly
are you comparing every sheet individually
would something like Ford mentioned in post #3 not work?
instead of working with 60 different sheets
all you would need is the date in one of the columns?
this is judging by the total column is only 41.... 41x60 =~2.5k lines is rather managable to have in one sheet

struggling to see how you can change hours worked in days you already "processed" ie 1 month ago? but i guess people can put in sheets late or be process it after the fact

if you dont mind me asking....how does your 60 files get updated?
could be part of the solution or part of the restriction

i wouldnt go across, i would go downwards
it would be easier to code and work with..ie if you want to sumif or countif all the ducks are aligned in one column

so the raw data you copy/paste into daily report
you don't do 60 times a day every day right?
if you are doing this 60's times a day, i would say you should include the exporting into summary as part of that exercise.

am i correct in saying you do this once a day every day? if this is the case could you not just add your newest day down the bottom and delete the first day from the top? that would be much faster and easier than using that many indirects

really don't think you should be using indirect for what you are doing anyway
wouldnt your spreadsheet run really slow with that many volatile formulas?
300x60x11 = 198k cells that would want to update every time you move something, if you do certainly hope you have set calculation mode to manual...

now that i know how the data is formed/created and used
will give it a crack tomorrow, shouldn't be to hard or long
what would help is some sample data (sanitised of course) so i dont have to type gibberish files out to test with

so best bet would be to manually copy/paste to bottom and delete top?
Would it be possible to install macro in daily that could do this?

would do this via macro
would do two actually
one to do a full reset...ie all 60 to get started and in case you have to reset the data for whatever reason
one for daily update (delete the last one insert a new one)

unless you are using it elsewhere i wouldn't even bother with that sheet 2 you have with the today()-60 formulas, you could do that all in VBA

Originally Posted by keith740

once complete, i will have many formulas pulling data... will the deleting bottom and adding top affect these formulas?

sorry 100 quetions...

depends on what your formulas are...i would say not really...formulas should update accordingly...

in all honesty for what you are trying to do i would even chuck the data into a table and create a pivot table out of it

see attached file
so i went with updating will add the latest down the bottom (meaning the oldest is up the top), as you aren't going to use pivot tables for time being i made a empty row between daily reports as seperator

update daily will ask you if you want to delete the latest date or not then continue to add todays file
coded to work with today only
it can be changed so you select a file instead but thats later if you want it
will take columns A to M (reduce/extend as required)

update all
will ask you if you want to delete all summary's you have on your sheet..ie give you clean slate
will go thru 60 files to update
any file missing/not found will be written in the updateall sheet so you can find out which ones didnt work

for both codes i choose to go with pasting values instead of leaving formulas in
it may be the cause you want some formulas in some columns but that can be fine tuned later...as your dummy file just gave me a lot of external links
i didn't want that to be cluttering factor at least in testing phase

Re: will affect other VBA's
it should not, from what i gather that are directly on your daily file

i went and put alot of comments on the code itself so please go and read thru them so you understand what is doing what
let me know if anything is unclear

those buttons arent really required but i put them for easier use...change/delete etc as you require

one thing you may need to do is

Please Login or Register  to view this content.

change the line to xlsm in your case
the sample file was xlsx so i used it for testing

ps you have a lot of random (yet similar) conditional formats in your sample sheet (seems like you cut and paste all over the place)...excessive conditional formats can really slow performance

i should of put this first but as always when experimenting with vba on live data
save a copy first

copy Column AJ of sheet titled CopyPaste1 of daily to new sheet of this workbook?

sounds probably but i dont know exactly what your asking to do

in the daily i have a sheet titled CopyPaste1
Need to copy column AJ from the current day (same day as insert button) daily from this sheet (Daily, CopyPaste1) and paste to 60 day workbook (the one you just installed the VBA) sheet 3
could we add this to the insert button
Or maybe make a whole new button (Whichever easiest for you)


i will be using this sheet later to run SUMIFS

im horrible at explaining lol (im sure you know from previous posts..)

But once again if too much trouble, no worries

yes sounds feasible
you would just plonk in extra code to copy and paste additional data

with your sheet copypaste1
where is the data?...how is it structured?
i hope its the same number of rows as the daily report
also since your daily report has more than 1 sheet...would suggest additional re-coding to avoid having issues if you accidentally (or not) save your daily report on a different sheet

glad to have helped

It is more rows unfortunately as we are growing so quickly, this column is list of ALL employees.

you can copy it across but it wont align? if it doesnt align you wont know which sheet it belongs to?
or is this purely for the daily one to see if you have missed anyone? ie rowcheck?

if that is the case then yes i would suggest you check the daily file instead of the summary file (which is what i think you are doing now?)

if you do have the time i would suggest you to look into learning pivot tables for analysis work
if can understand vba then pivot tables should not be a big jump
anyways best of luck

this sheet will be used for SUMIFS, AVERAGEIFS and whatever else i can find that works to pull total KPI for 1 week, 2 weeks, 30 and 60 days (Which im sure will be posting questions on lol... i have most but the %'s are tricky, but i think i got it)

indeed i do plan on learning pivot tables... i actually took advanced excel i college, but at time i never thought i would use it soo... now using excel every day, go figure...

plan on retaking these classes very soon!

TY again Humdingaling!!