Hello! With regards to consolidation file location point, yes that path (C:\Users\test\Desktop\Labour\test) is where I'd like the file to be saved to in the end.
As for the "source" files if you will, (aka the files maintained by stores). These files will be saved in different folders on the network and yes, I will want to consolidate specific sheets from each store's file. Each file will have a unique name but each of the sheets will be labeled the same way (ie week number like 26, 27, 28, etc.) For the purpose of this test, we can use the assume that the source files and the consolidation macro will all reside in the same directory (C:\Users\test\Desktop\Labour\test). The file names are 11000,xlsx, 11001,xlsx and 11002.xlsx. Becuase in the end the source files will reside in different locations on the network having a macro that consolidates these specific files would be great! Again, the only area I'm struggling with is how to use a cell reference in a macro to then dictate which sheets need to be pulled into the consolidation file. Thanks for your help! On Tuesday, 4 November 2014 13:52:07 UTC, Paul Schreiner wrote: > > I'm sure I can put together something that will work cleanly. > > Rather than make assuptions though, I'll ask a couple of questions: > > in the folder: > C:\Users\test\Desktop\Labour\test > > will you be consolidating specific sheets from ALL of the files in this > location > (that do now begin with "Week.." or all are type .xlsx?) > > We can specify the files on the worksheet: > 11000 > 11001 > 11002 > > or we can simply process all files in the folder. > > I'll make some sample files for testing and then put together a macro for > you. > > *Paul* > ----------------------------------------- > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John Wesley* > ----------------------------------------- > > *From:* dza49 <dvp...@gmail.com <javascript:>> > *To:* excel-...@googlegroups.com <javascript:> > *Sent:* Tuesday, November 4, 2014 8:11 AM > *Subject:* $$Excel-Macros$$ Cell Reference to consolidate specifically > named tabs into one spreadsheet > > Hello, > > I have been given a task where I have say 10 spreadsheets located on the > network which have the same filename and tab naming convention. Each > spreadsheet will have a tab for each week of the year that contains reports > that I'd like to consolidate. For example, each spreasdsheet will have the > week number listed as tab name and I want to pull the same week's > information into one file to have one spreadsheet with each location's > report for the week. > > Here is the VBA code for the very simple flow that I want to replicate on > larger scale: > > http://pastebin.com/99WG6nVz > > It basically opens each location's spreadsheet, grabs week 27's report and > then consolidates them into one file that I save as a new copy called "week > 27 update.xlsm". > > Here is where I need some help. > > I'd like to set up the original "MACRO.xlsm" file with a reference to the > week number in a cell so that I could select which tabs to copy over. For > example, the above macro pulls week 27 from each of the spreadsheets, I'd > like to make this dynamic in such that I could enter the week number and > then use that cell value to dictate which week would be copied over. For > clarification, each tab is named at number like 27, 28, 29, etc. The last > bit of custom work would be to use that same cell value and save the file > of the newly consolidated files to a location. The above example is "week > XX update.xlsm" where XX is the cell value. > > I hope that all makes sense. > > Thank you in advance! > > Dza > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros...@googlegroups.com <javascript:>. > To post to this group, send email to excel-...@googlegroups.com > <javascript:>. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
