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.
