Very helpful walkthrough, out of curiosity can it include where it will make different worksheet based on another criteria before saving as a new workbook?
Yes, this type of coding is totally flexible, create another subroutine or several subroutines to do more extracts to a new worksheet (Worksheet = thisworkbook.Worksheets.Add) before saving the workbook.
@@BusinessProgrammer means using listview control, fill data from Excel sheet and then from search box on form required data fill in listview and then export to other excel sheets like reports generated
Thank for the detailed demonstration and discussion around the thought process of developing the code. I have question on a variation of the extraction of the filter output for the individual cities. Instead of creating a new workbook (or worksheet as you described in an earlier response), how would you develop the code to extract the filter results into an existing worksheet (in the same workbook as your data); placing each new city results in a column next to the previous city data?
Hi Tye, I haven't looked at this video recently, but to extract data into the same workbook just add a sheet to the workbook for that purpose either manually or in code "Thisworkbook.worksheets.add" then you could use a for each next loop or a for loop to add the cities in columns.
Can you please explain what you are doing at 2:08? I'm getting the same issue as @Vanessa Bryant where im getting an "empty" value for GetUniqueListOfCities and rng Return is getting a value of "nothing". When i run the code and debug it highlights the: rngData.AdvancedFilter xlFilterCopy, rngCrit, rngExt, True I find the comments you provided for her and the other person difficult to understand and cant really figure out what is preventing my code to run.
OK, so at 2:08 and going forward, I'm performing an extract on the data (i.e. an advanced filter), now providing you've created an accurate filter (e.g. city is "Denver" and you actually have a "Denver") in your data, then the rows containing "Denver" would be output to your extract range. In this subroutine that I've converted to a function, I grab that extracted range by creating a range object that's the same dimensions as the extracted range and call that range rngReturn. I then send the data that's in rngReturn back to the calling function by settting the function name = to rngReturn.Value. If your code is highlighting that line of code then you've done something wrong (captain obvious, I know), check that your data criteria and extract ranges follow my data rules that I would have explained, Your data range must have a header and must not "touch" any other ranges, criteria range is at least two rows (header and at least one data row) and the extract row is only the header row. Try replicating my video IN DETAIL and see if that works and keep making tiny changes until it breaks, I've got four videos (see link below) on the advanced filter so make sure you understand how it works inside out. Advanced Filter Playlist.... th-cam.com/video/KBvcNfTPYj4/w-d-xo.html
As I am working through this coding, the locals window is showing an "empty" value for GetUniqueListOfCities and rng Return is getting a value of "nothing". I have been working through this step by step and am unsure what I am doing wrong. Can you assist with this?
Hi Vanessa I will be providing more videos in the near future on this but re range programming .... (just copying in some of my response to Hannah below..). The basic rule for getting data from a spreadsheet into a variant array is that the range of data is applied to an empty array. so for example.... dim vData as Variant dim rngData as range set rngData = someWorksheet.Range("B2:K17"). or whatever range you want vData = rngData.value ' rngData having been assigned to a range containing data My complete VBA playlist with plenty of examples of range programming can be found at @t It's difficult to know what you've done wrong without being able to look over your shoulder, but the code for this is available from my website when you opt in on the Advanced Filter page. Hope that helps.
Would it be possible if your data you want to split is in two different sheets and (e.g Sheet 1:Sales Qty master & Sheet 2:Sales Value Master) and you want to split it by cities but we want both sheets in the resulting files of split according to city so Las Vegas should have both sheets in one file and similarly for other cities.
Hi Nitesh I would first write a macro to consolidate both master sheets into one (for example a temporary sheet) then run the advanced filter on that temporary sheet in order to split out by city. Have a look at my videos on consolidation ... th-cam.com/video/_0qfRaVY5v0/w-d-xo.html th-cam.com/video/lc8zFJEfZmU/w-d-xo.html
Really helful but i need a VBA code for an excel file which i find impossible to do so need your help, please provide any contact information so that i can share the excel file with you.
~I tried to contact through your website, but received an error message, so I'll try here!~ Hi Sean, First & foremost, thank you for putting out these videos out into the world. For the last month I have been trying to figure out a way to split a data set based on a variant (to then send through automatic emails), and this is the first time I've felt like a solution is within reach. I've watched your video a dozen times and am trying to use it with my data set, but when I get to the portion where my variant should show up in the locals window (minute 4:29 in your video), I receive a message that says "GetUnique Empty." I'm very new to macros and struggling to troubleshoot the problem. For Context, my data set is in columns A:M, my variant is in column A, and some unqiue values contain symbols (for example, Amazing Retailer Co. (USA) ). Could any of these things prevent the code from working? I would really appreciate any help you can provide. Please let me know if you can assist :) Cheers, Hannah
Hi Hannah The basic rule for putting data in a spreadsheet into a variant array is that the range of data is applied to an empty array. so for example.... dim vData as Variant dim rngData as range set rngData = someWorksheet.Range("B2:K17"). or whatever range you want vData = rngData.value ' rngData having been assigned to a range containing data My complete VBA playlist with plenty of examples of range programming can be found at th-cam.com/play/PLQORZjfSPqSk9mBfHC7ecyz2EeVeJN6ZQ.html Hope this helps.
Thanks for this, the walk-through was great and helped me so much. Greatly appreciated.
Glad you found it helpful John
It's Mahendra Tita from India, great code with oops function and best clearly video and tutorial. Thank you for sharing.
You're welcome Mahendra
Thank you for great tutorial !
You're welcome, Bojan
Very helpful walkthrough, out of curiosity can it include where it will make different worksheet based on another criteria before saving as a new workbook?
Yes, this type of coding is totally flexible, create another subroutine or several subroutines to do more extracts to a new worksheet (Worksheet = thisworkbook.Worksheets.Add) before saving the workbook.
Great coding, I am from India, nice and great tutorial. Thank you for sharing
You're welcome Mahendra
@@BusinessProgrammer Can you please make tutorial for data import in listview and export data from listview using Excel vba. Thank you again.....
@@MahendraTita can you please explain what you mean by “list view”?
@@BusinessProgrammer means using listview control, fill data from Excel sheet and then from search box on form required data fill in listview and then export to other excel sheets like reports generated
Thank for the detailed demonstration and discussion around the thought process of developing the code. I have question on a variation of the extraction of the filter output for the individual cities. Instead of creating a new workbook (or worksheet as you described in an earlier response), how would you develop the code to extract the filter results into an existing worksheet (in the same workbook as your data); placing each new city results in a column next to the previous city data?
Hi Tye, I haven't looked at this video recently, but to extract data into the same workbook just add a sheet to the workbook for that purpose either manually or in code "Thisworkbook.worksheets.add" then you could use a for each next loop or a for loop to add the cities in columns.
Can you share the entire code please? Very Helpful information! Thank you!
Can you please explain what you are doing at 2:08? I'm getting the same issue as @Vanessa Bryant where im getting an "empty" value for GetUniqueListOfCities and rng Return is getting a value of "nothing". When i run the code and debug it highlights the: rngData.AdvancedFilter xlFilterCopy, rngCrit, rngExt, True
I find the comments you provided for her and the other person difficult to understand and cant really figure out what is preventing my code to run.
OK, so at 2:08 and going forward, I'm performing an extract on the data (i.e. an advanced filter), now providing you've created an accurate filter (e.g. city is "Denver" and you actually have a "Denver") in your data, then the rows containing "Denver" would be output to your extract range.
In this subroutine that I've converted to a function, I grab that extracted range by creating a range object that's the same dimensions as the extracted range and call that range rngReturn.
I then send the data that's in rngReturn back to the calling function by settting the function name = to rngReturn.Value.
If your code is highlighting that line of code then you've done something wrong (captain obvious, I know), check that your data criteria and extract ranges follow my data rules that I would have explained, Your data range must have a header and must not "touch" any other ranges, criteria range is at least two rows (header and at least one data row) and the extract row is only the header row.
Try replicating my video IN DETAIL and see if that works and keep making tiny changes until it breaks, I've got four videos (see link below) on the advanced filter so make sure you understand how it works inside out.
Advanced Filter Playlist....
th-cam.com/video/KBvcNfTPYj4/w-d-xo.html
As I am working through this coding, the locals window is showing an "empty" value for GetUniqueListOfCities and rng Return is getting a value of "nothing". I have been working through this step by step and am unsure what I am doing wrong. Can you assist with this?
Hi Vanessa
I will be providing more videos in the near future on this but re range programming .... (just copying in some of my response to Hannah below..).
The basic rule for getting data from a spreadsheet into a variant array is that the range of data is applied to an empty array. so for example....
dim vData as Variant
dim rngData as range
set rngData = someWorksheet.Range("B2:K17"). or whatever range you want
vData = rngData.value ' rngData having been assigned to a range containing data
My complete VBA playlist with plenty of examples of range programming can be found at
@t
It's difficult to know what you've done wrong without being able to look over your shoulder, but the code for this is available from my website when you opt in on the Advanced Filter page.
Hope that helps.
Would it be possible if your data you want to split is in two different sheets and (e.g Sheet 1:Sales Qty master & Sheet 2:Sales Value Master) and you want to split it by cities but we want both sheets in the resulting files of split according to city so Las Vegas should have both sheets in one file and similarly for other cities.
Hi Nitesh
I would first write a macro to consolidate both master sheets into one (for example a temporary sheet) then run the advanced filter on that temporary sheet in order to split out by city.
Have a look at my videos on consolidation ...
th-cam.com/video/_0qfRaVY5v0/w-d-xo.html
th-cam.com/video/lc8zFJEfZmU/w-d-xo.html
@@BusinessProgrammer Thanks Sean..I'll have a look at it.
Would it be possible to get all the information into separate tabs of a new workbook instead of one workbook for each city?
Absolutely, just create the new workbook and loop creating new tabs rather than workbooks, set wks = workbook.worksheets.add
Can we have code to add this all sheets in one workbook
Really helful but i need a VBA code for an excel file which i find impossible to do so need your help, please provide any contact information so that i can share the excel file with you.
Hi Azhar
My website is BusinessProgrammer.com
~I tried to contact through your website, but received an error message, so I'll try here!~
Hi Sean,
First & foremost, thank you for putting out these videos out into the world. For the last month I have been trying to figure out a way to split a data set based on a variant (to then send through automatic emails), and this is the first time I've felt like a solution is within reach. I've watched your video a dozen times and am trying to use it with my data set, but when I get to the portion where my variant should show up in the locals window (minute 4:29 in your video), I receive a message that says "GetUnique Empty."
I'm very new to macros and struggling to troubleshoot the problem. For Context, my data set is in columns A:M, my variant is in column A, and some unqiue values contain symbols (for example, Amazing Retailer Co. (USA) ). Could any of these things prevent the code from working?
I would really appreciate any help you can provide. Please let me know if you can assist :)
Cheers,
Hannah
Hi Hannah
The basic rule for putting data in a spreadsheet into a variant array is that the range of data is applied to an empty array. so for example....
dim vData as Variant
dim rngData as range
set rngData = someWorksheet.Range("B2:K17"). or whatever range you want
vData = rngData.value ' rngData having been assigned to a range containing data
My complete VBA playlist with plenty of examples of range programming can be found at
th-cam.com/play/PLQORZjfSPqSk9mBfHC7ecyz2EeVeJN6ZQ.html
Hope this helps.