Forum Discussion
Marco/VBA filter based on the name of sheets and copy paste filtered data to correspondent sheets
Hope everyone is doing well.
I was trying to find the VBA code which will allow me to do below and concerned the possibility.
1. There is one worksheet “raw data”. and other sheets named by different code with same format and template.
2. Trying to record a Macro by copying the name of each sheet in a row and autofilter column A in “raw data” sheet. Then copy the filtered data without headers and column A to correspondent name of sheet.
3. If there’s no data after filtering then skip to next step, start with new filter.
Chances are there is no data after filter column A in “raw data” sheets, I added Range("B3:W").Selection.SpecialCells(xlCellTypeVisible).Copy
, but somehow came to error and also struggled with the step 3, If worksheet is Nothing Then...
Wondering if any one have ideas about this, already attached sample file too,
thanks a million.
used Power Query on the Main Tab
Created reference queries
Renamed and filtered the reference queries according to the data filtered on them
when you update the main tab then press ctrl+alt+F5 it will refresh the master query as well as all the reference queries according to what they're filtered to
to add a new reference query:
click data then the icon in the red square to display the query navigation
double click the master query
in the queries navigation panel right click the master query then select reference
Rename the query to the data that you will filter for that reference query, then click close and load then save the file
cheers
5 Replies
- don't forget to mark your post as answered so it will help other users searching for a similar solution.
cheers used Power Query on the Main Tab
Created reference queries
Renamed and filtered the reference queries according to the data filtered on them
when you update the main tab then press ctrl+alt+F5 it will refresh the master query as well as all the reference queries according to what they're filtered to
to add a new reference query:
click data then the icon in the red square to display the query navigation
double click the master query
in the queries navigation panel right click the master query then select reference
Rename the query to the data that you will filter for that reference query, then click close and load then save the file
cheers
- Sorry, I just found out it's for creating new sheet for filtered data.
pls disregard my last reply, thanks a lot. - Many thanks for your alternative plan, it's literally more simple and understandable.
But kind of confused about last step and didn't apprehend its purpose( select "reference" and new query created...). I'm sorry if I asked a dumb question.
thanks a million!!it is not a dumb question, you are just communicating the area of your confusion to make a clarification and that is a best practice.
The reference queries that are named KEL30 for example is the query that will be loaded into the worksheet, as is with all the other reference queries that's why the sheet names are the same names of the reference queries.
Try it. Put new information on existing designations as well as information on new designation, then press ctrl+alt+F5, then look at the designation worksheets that you added new information, then create a new reference query for the new designation that you added for fun and for practice.
When you update the Master Sheet with new information, those existing reference queries will dynamically (automatically) update including the filtering.
If the master sheet gets updated with a new designation, for example TZZ01, you will only need to create a reference query for that particular designation and load it into worksheet in the workbook and once you save the workbook the next time you update it it will automatically update the new information to the existing reference queries and all you have to do is create a reference query to new designations that are in the master worksheet.
cheers
