$$Excel-Macros$$ Re: how can I color with the help of list I simple want intersection to be color if cell of intersection
Thank you... :) On Wednesday, February 18, 2015 at 10:06:47 AM UTC+5:30, Mandeep baluja wrote: awesome looping thanks :) i will apply it on a huge data by changing the parametres. On Tuesday, 17 February 2015 16:10:37 UTC+5:30, Mandeep Baluja wrote: help required !! -- 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.
$$Excel-Macros$$ In need of an Excel macro to filter selected data and copy it incrementally to a central file
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.
Re: $$Excel-Macros$$ In need of an Excel macro to filter selected data and copy it incrementally to a central file
From: dza49 dvpe...@gmail.com To: excel-macros@googlegroups.com Sent: Thursday, February 19, 2015 12:20 PM Subject: $$Excel-Macros$$ In need of an Excel macro to filter selected data and copy it incrementally to a central file 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? Yes, provided: the value in A1 is in the same format as your autofilter Criteria1 and. your line: Criteria1:==ShtNo should be: Criteria1:== ShtNo otherwise: =ShtNo means literally the LETTERS ShtNo where = ShtNo means to concatenate the = sign with the VALUE of the variable ShtNo and: Sheets(ShtNo).Select selects a sheet called ShtNo while: Sheets(ShtNo).Select Selects a sheet called the value of the variable ShtNo 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? It SHOULD work to copy/paste (try recording a macro, selecting an area MUCH larger than the maximum you would expect). However, I'd prefer to loop through the entire sheet and PROGRAMATICALLY check for values equivalent to the required criteria. (I hate using the clipboard to copy data INTO the clipboard and pasting the data FROM the clipboard) 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. I usually set up two row counters: rReport, rSource then, set rReport to the first blank row in your Central file and loop through the Source file, only incrementing the ReportRow when you are copying data. Keep in mind that you can use something like: For Col = 1 to LastCol Workbooks(Central.xlsb).Sheets(ShtNo).Cells(rReport,C).value = Workbooks(11009 - FY2015 Store Planning File.xlsb).Sheets(ShtNo).Cells(rSource,C).Value Next Col will work without selecting the different workbooks and sheets. And even something like: Workbooks(11009 - FY2015 Store Planning File.xlsb).Close works without Activating the workbook. 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
