Hey thanks Paul, I really appreciate this. I was able to fix the macro for steps 1 & 2 and they are working brilliantly.
For step number three, your solution makes sense but I'm not sure what the VBA code would be or where to put it. Here is the code I have today that cycles through two stores at the moment. http://pastebin.com/tJx9EaNi I'm not sure how or where to add "row counters" so I've just said to add the firs store data to the central file (named *Hours Consolidation Macro.xlsb*) to row 4 and then add the second store data starting at row 1000 which is more than enough space to not overwrite the first store data. It's a bit simple and could work but I'd like to attempt your solution where the data is added incrementally. I should note that the tab in which the data consolidation will happen is in the workbook *Hours Consolidation Macro.xslb* and in the tab called *data*. I am only using ShtNo for the filter reference in the store files. Thanks for your help. Daniel On Thursday, 19 February 2015 17:20:27 UTC, dza49 wrote: > > Hello, > > I have a couple of unique requests for a macro that I'm attempting to > build. > > The situation is as follows, I have the same file located in different > locations on the network where different departments enter in their data by > week. I need to go into each of these locations, filter the data to a > specific week of my choosing, copy the resulting selected rows of data and > copy the results to a central location each week incrementally so that the > location data is complied into one file and not over write itself. > > I've attempted to start the macro here but I'm running into issues: > > http://pastebin.com/u9yzKSH5 > > Here are the main issues I'm running into: > > 1) Filter the location data by the week of my choosing - I'd like to start > with the macro file where I can enter the week I want to choose into a cell > and have the macro reference this figure when filtering the location data. > I've attempted this in another macro and it worked fine but doesn't appear > to be working for me here. The specific code I'm trying to use is: > > Dim ShtNo As String, NewWb, wbName > > ShtNo = ThisWorkbook.Sheets(1).Range("A1").Value > If (ShtNo & "X" = "X") Then > MsgBox "No Sheet Specified" > Exit Sub > End If > > Will ShtNo work as the reference to be used to filter the data? > > Once I have filtered the results by the above criteria I need to copy the > selected data into my central location. I think I run into two issues here: > > 2) Once filtered, how can I ask excel to only grab the filtered data. > From the macro I recorded it naturally just references the specific area I > highlighted and copied over which will be different for each location's > file. Is there specific code that I should use here to pull the filtered > reference data (not the header row)? How can I make this a dynamic > statement rather than the static area I've listed in the macro currently? > > 3) Once the data has been filtered and copied I'll need to add this data > to a central location. Because I'll be pulling this information from > several locations i'll need to add this information incrementally or below > the previous data. For example, location A data is copied to the central > file and takes up rows 2 through 98. When the macro cycles and moves onto > location B's data i'd like for it to be copied to the central file starting > at row 99. How can I make it such that the macro knows to incrementally > add to the central data and not overwrite the existing information. > > Thanks in advance for your help. > > Daniel > > -- 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.
