Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter more than 1 worksheet
I have a workbook that contains 4 worksheets, all with lots of data on them.
All have autofilter set up and what I am hoping to achieve is to set the criteria on 1 worksheet in the first column (say in cell B1) that will then become the default criteria on all the other worksheets. I found the following code through google but can't get it to work - I assume the first macro can be pasted into a module but I'm not sure what to do with the second bit. OR is there an easier way Thanks in advance for any help Private Sub Worksheet_Calculate() MsgBox ActiveSheet.AutoFilter.Filters(1).Criteria1 End Sub 'Having tht criteria, it can then be applied to any other filter in any other sheet. 'CODE set wsThis = Activesheet for each ws in worksheets if ws.name < wsThis.name then ws.autofilter.filter(1).criteria = right(wsthis.autofilter.filter(1).criteria,len(wst his.autofilter.filter(1).criteria)-1 ) end if next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter more than 1 worksheet
This macro works. Use mid function in lieu of right function to remove = sign
from beginning of criteria because allowing the default for number of characters to select includes all characters from 2nd one so the length of the criteria does not matter. Note: I have always found that the sheet needs to be activated before the filter can be set in the macro. Your first macro is designed to autorun with an event but from your query you don't seem too confident in that area so I have simply used one you can paste into a module which you can run from Tools, macro etc or you can put in a command button. Dim InitFilter 'Initial Filter Setting Dim InitSheet 'Name of Initial Sheet Sub Multi_Sht_Auto_Filt() InitFilter = Mid(ActiveSheet.AutoFilter.Filters(1).Criteria1, 2) InitSheet = ActiveSheet.Name For Each sht In Worksheets If sht.Name < InitSheet Then 'No need reset initial sheet sht.Select Selection.AutoFilter Field:=1, Criteria1:=InitFilter End If Next End Sub "Gazza" wrote: I have a workbook that contains 4 worksheets, all with lots of data on them. All have autofilter set up and what I am hoping to achieve is to set the criteria on 1 worksheet in the first column (say in cell B1) that will then become the default criteria on all the other worksheets. I found the following code through google but can't get it to work - I assume the first macro can be pasted into a module but I'm not sure what to do with the second bit. OR is there an easier way Thanks in advance for any help Private Sub Worksheet_Calculate() MsgBox ActiveSheet.AutoFilter.Filters(1).Criteria1 End Sub 'Having tht criteria, it can then be applied to any other filter in any other sheet. 'CODE set wsThis = Activesheet for each ws in worksheets if ws.name < wsThis.name then ws.autofilter.filter(1).criteria = right(wsthis.autofilter.filter(1).criteria,len(wst his.autofilter.filter(1).criteria)-1 ) end if next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter more than 1 worksheet
Thanks OssieMac
This works well enough for what I want and as you suggest I will assign the macro to a command button. Leads on to a couple of things though (still getting to grips with this VBA stuff) 1 The macro crashes if there is a sheet in the workbook that does not have the autofilter set up (doesn't have any data that I need filtering but I can remove it for now. So I need to find a method that will trap this error or presumably I can pass the sheet names to the proceedure somehow. 2 As you say I'm not too confident either about event triggering procedures either so that's something else to put on my TODO list. It's very helpful though having people like yourself who take the time and trouble to help others out with this stuff. best regards Gazza "OssieMac" wrote in message ... This macro works. Use mid function in lieu of right function to remove = sign from beginning of criteria because allowing the default for number of characters to select includes all characters from 2nd one so the length of the criteria does not matter. Note: I have always found that the sheet needs to be activated before the filter can be set in the macro. Your first macro is designed to autorun with an event but from your query you don't seem too confident in that area so I have simply used one you can paste into a module which you can run from Tools, macro etc or you can put in a command button. Dim InitFilter 'Initial Filter Setting Dim InitSheet 'Name of Initial Sheet Sub Multi_Sht_Auto_Filt() InitFilter = Mid(ActiveSheet.AutoFilter.Filters(1).Criteria1, 2) InitSheet = ActiveSheet.Name For Each sht In Worksheets If sht.Name < InitSheet Then 'No need reset initial sheet sht.Select Selection.AutoFilter Field:=1, Criteria1:=InitFilter End If Next End Sub "Gazza" wrote: I have a workbook that contains 4 worksheets, all with lots of data on them. All have autofilter set up and what I am hoping to achieve is to set the criteria on 1 worksheet in the first column (say in cell B1) that will then become the default criteria on all the other worksheets. I found the following code through google but can't get it to work - I assume the first macro can be pasted into a module but I'm not sure what to do with the second bit. OR is there an easier way Thanks in advance for any help Private Sub Worksheet_Calculate() MsgBox ActiveSheet.AutoFilter.Filters(1).Criteria1 End Sub 'Having tht criteria, it can then be applied to any other filter in any other sheet. 'CODE set wsThis = Activesheet for each ws in worksheets if ws.name < wsThis.name then ws.autofilter.filter(1).criteria = right(wsthis.autofilter.filter(1).criteria,len(wst his.autofilter.filter(1).criteria)-1 ) end if next |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter more than 1 worksheet
No problems doing that. I don't mind helping people get started. However if
the answer is helpful then don't forget to answer yes to the question "Was this post helpful" at the bottom of the screen. Select my last post and do the same. Sub Multi_Sht_Auto_Filt() InitFilter = Mid(ActiveSheet.AutoFilter.Filters(1).Criteria1, 2) InitSheet = ActiveSheet.Name For Each sht In Worksheets If sht.Name < InitSheet Then 'No need reset initial sheet sht.Select If ActiveSheet.AutoFilterMode Then 'Confirms Autofilter on the sheet. Selection.AutoFilter Field:=1, Criteria1:=InitFilter End If End If Next End Sub And if you want to make it better still, the following line of code could be inserted at the beginning of your code so that it will confirm if the AutoFilter selection has been made. (ie. Not set to All) before running the macro. You could make it an If Then Else with a msgbox in the Else to tell the user to set the filter before running the macro. If ActiveSheet.FilterMode Then 'Confirms that selection has been made. "Gazza" wrote: Thanks OssieMac This works well enough for what I want and as you suggest I will assign the macro to a command button. Leads on to a couple of things though (still getting to grips with this VBA stuff) 1 The macro crashes if there is a sheet in the workbook that does not have the autofilter set up (doesn't have any data that I need filtering but I can remove it for now. So I need to find a method that will trap this error or presumably I can pass the sheet names to the proceedure somehow. 2 As you say I'm not too confident either about event triggering procedures either so that's something else to put on my TODO list. It's very helpful though having people like yourself who take the time and trouble to help others out with this stuff. best regards Gazza "OssieMac" wrote in message ... This macro works. Use mid function in lieu of right function to remove = sign from beginning of criteria because allowing the default for number of characters to select includes all characters from 2nd one so the length of the criteria does not matter. Note: I have always found that the sheet needs to be activated before the filter can be set in the macro. Your first macro is designed to autorun with an event but from your query you don't seem too confident in that area so I have simply used one you can paste into a module which you can run from Tools, macro etc or you can put in a command button. Dim InitFilter 'Initial Filter Setting Dim InitSheet 'Name of Initial Sheet Sub Multi_Sht_Auto_Filt() InitFilter = Mid(ActiveSheet.AutoFilter.Filters(1).Criteria1, 2) InitSheet = ActiveSheet.Name For Each sht In Worksheets If sht.Name < InitSheet Then 'No need reset initial sheet sht.Select Selection.AutoFilter Field:=1, Criteria1:=InitFilter End If Next End Sub "Gazza" wrote: I have a workbook that contains 4 worksheets, all with lots of data on them. All have autofilter set up and what I am hoping to achieve is to set the criteria on 1 worksheet in the first column (say in cell B1) that will then become the default criteria on all the other worksheets. I found the following code through google but can't get it to work - I assume the first macro can be pasted into a module but I'm not sure what to do with the second bit. OR is there an easier way Thanks in advance for any help Private Sub Worksheet_Calculate() MsgBox ActiveSheet.AutoFilter.Filters(1).Criteria1 End Sub 'Having tht criteria, it can then be applied to any other filter in any other sheet. 'CODE set wsThis = Activesheet for each ws in worksheets if ws.name < wsThis.name then ws.autofilter.filter(1).criteria = right(wsthis.autofilter.filter(1).criteria,len(wst his.autofilter.filter(1).criteria)-1 ) end if next |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter more than 1 worksheet
No problems doing that. I don't mind helping people get started. However
if the answer is helpful then don't forget to answer yes to the question "Was this post helpful" at the bottom of the screen. Select my last post and do the same. Since the OP is not posting from the microsoft communities, he probably has no idea what you are talking about. -- Regards, Tom Ogilvy "OssieMac" wrote in message ... No problems doing that. I don't mind helping people get started. However if the answer is helpful then don't forget to answer yes to the question "Was this post helpful" at the bottom of the screen. Select my last post and do the same. Sub Multi_Sht_Auto_Filt() InitFilter = Mid(ActiveSheet.AutoFilter.Filters(1).Criteria1, 2) InitSheet = ActiveSheet.Name For Each sht In Worksheets If sht.Name < InitSheet Then 'No need reset initial sheet sht.Select If ActiveSheet.AutoFilterMode Then 'Confirms Autofilter on the sheet. Selection.AutoFilter Field:=1, Criteria1:=InitFilter End If End If Next End Sub And if you want to make it better still, the following line of code could be inserted at the beginning of your code so that it will confirm if the AutoFilter selection has been made. (ie. Not set to All) before running the macro. You could make it an If Then Else with a msgbox in the Else to tell the user to set the filter before running the macro. If ActiveSheet.FilterMode Then 'Confirms that selection has been made. "Gazza" wrote: Thanks OssieMac This works well enough for what I want and as you suggest I will assign the macro to a command button. Leads on to a couple of things though (still getting to grips with this VBA stuff) 1 The macro crashes if there is a sheet in the workbook that does not have the autofilter set up (doesn't have any data that I need filtering but I can remove it for now. So I need to find a method that will trap this error or presumably I can pass the sheet names to the proceedure somehow. 2 As you say I'm not too confident either about event triggering procedures either so that's something else to put on my TODO list. It's very helpful though having people like yourself who take the time and trouble to help others out with this stuff. best regards Gazza "OssieMac" wrote in message ... This macro works. Use mid function in lieu of right function to remove = sign from beginning of criteria because allowing the default for number of characters to select includes all characters from 2nd one so the length of the criteria does not matter. Note: I have always found that the sheet needs to be activated before the filter can be set in the macro. Your first macro is designed to autorun with an event but from your query you don't seem too confident in that area so I have simply used one you can paste into a module which you can run from Tools, macro etc or you can put in a command button. Dim InitFilter 'Initial Filter Setting Dim InitSheet 'Name of Initial Sheet Sub Multi_Sht_Auto_Filt() InitFilter = Mid(ActiveSheet.AutoFilter.Filters(1).Criteria1, 2) InitSheet = ActiveSheet.Name For Each sht In Worksheets If sht.Name < InitSheet Then 'No need reset initial sheet sht.Select Selection.AutoFilter Field:=1, Criteria1:=InitFilter End If Next End Sub "Gazza" wrote: I have a workbook that contains 4 worksheets, all with lots of data on them. All have autofilter set up and what I am hoping to achieve is to set the criteria on 1 worksheet in the first column (say in cell B1) that will then become the default criteria on all the other worksheets. I found the following code through google but can't get it to work - I assume the first macro can be pasted into a module but I'm not sure what to do with the second bit. OR is there an easier way Thanks in advance for any help Private Sub Worksheet_Calculate() MsgBox ActiveSheet.AutoFilter.Filters(1).Criteria1 End Sub 'Having tht criteria, it can then be applied to any other filter in any other sheet. 'CODE set wsThis = Activesheet for each ws in worksheets if ws.name < wsThis.name then ws.autofilter.filter(1).criteria = right(wsthis.autofilter.filter(1).criteria,len(wst his.autofilter.filter(1).criteria)-1 ) end if next |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AUTOFILTER IN A PROTECTED WORKSHEET | Excel Discussion (Misc queries) | |||
autofilter into another worksheet | Excel Discussion (Misc queries) | |||
autofilter and worksheet protection | Excel Programming | |||
Using Autofilter on a Protected Worksheet | Excel Discussion (Misc queries) | |||
Autofilter More than One Worksheet | Excel Programming |