How to filter according to cell value using vba?

hsayus

New Member
Joined
Nov 14, 2018
Messages
7
I have two sheet in a workbook. I am using VBA code to filter the data using following code:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20160623
Dim xWs As Worksheet
On Error Resume Next
For Each xWs In Worksheets
xWs
.Range("A4").AutoFilter 1, "=001"
Next
End Sub</code>But now I want to use cell value in place of 001. How do I modify the code. If I have value in cell F1.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hello,

Have you tested:
Code:
[COLOR=#101094][FONT=Consolas][FONT=inherit]Sub[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] apply_autofilter_across_worksheets[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]()[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#858C93][FONT=Consolas][FONT=inherit]'Updateby Extendoffice 20160623[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Dim[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] xWs [/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]As[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Worksheet
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]On[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Error[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Resume[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Next[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]For[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Each[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] xWs [/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]In[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Worksheets
  xWs[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Range[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"A4"[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]).[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]AutoFilter [/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]1[/FONT][/FONT][/COLOR][FONT=Consolas][FONT=inherit][COLOR=#303336],[/COLOR][COLOR=#7d2727]Range("F1").Value[/COLOR][/FONT][/FONT][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Next xWs[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]End [/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Sub
[/FONT][/FONT][/COLOR]

HTH
 
Upvote 0
Question is of similar type as previous. Now I want to make a different Workbook for the file according to IDType (001,002,... and so on).In F1 if i enter the IDType it should save according to IDType in a folder. Can you please help me out in this Coding.
 
Upvote 0
Thank you once again. I don't think that code will save all sheet in different workbook. i.e. I have different sheet in a workbook which contains different data but one similar column is IDType. I have IDType in all the other sheet. Now if I input 001 in cell F1 in above code then it will filter in all the sheet in that workbook and I want to save only 001 in different workbook with file name 001 and soon on. I have found one link but it saves only one sheet. link:: https://www.howtoexcel.org/vba/how-...te-workbooks-based-on-the-values-in-a-column/
Please let me know.
 
Upvote 0
James006 can you help me out with the following: I don't think that code will save all sheet in different workbook. i.e. I have different sheet in a workbook which contains different data but one similar column is IDType. I have IDType in all the other sheet. Now if I input 001 in cell F1 in above code then it will filter in all the sheet in that workbook and I want to save only 001 in different workbook with file name 001 and soon on. I have found one link but it saves only one sheet. link:: https://www.howtoexcel.org/vba/how-t...s-in-a-column/
Please let me know.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top