Repeating macro across all worksheets

[JP10]

Hope someone can help me.

My base macro works fine when I run it on one worksheet (with any name), but when I run the macro below, which based on my research should run across all the workbooks, it only runs on the worksheet I'm on. My workbook will have a different number of worksheets, with different names everytime (it's a data extract I need to format).

Sub ExportMaster()

Dim xsheet As Worksheet
For Each xsheet In ThisWorkbook.Worksheets

Call Export
Next xsheet
End Sub

Am I missing something?

I'm wondering if it has something to do with this section of the Macro as it has 'activesheet' commands:

Selection.AutoFilter
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Add2 _
Key:=Range("A8"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Thanks in advance

 

[bebo021999]

That requires you must be in that sheet, to filter then sort

For Each xsheet In ThisWorkbook.Worksheets
   xsheet.Activate
   Call Export
Next xsheet

 

[JP10]

Hope someone can help me.

My base macro works fine when I run it on one worksheet (with any name), but when I run the macro below, which based on my research should run across all the workbooks, it only runs on the worksheet I'm on. My workbook will have a different number of worksheets, with different names everytime (it's a data extract I need to format).

Sub ExportMaster()

Dim xsheet As Worksheet
For Each xsheet In ThisWorkbook.Worksheets

Call Export
Next xsheet
End Sub

Am I missing something?

I'm wondering if it has something to do with this section of the Macro as it has 'activesheet' commands:

Selection.AutoFilter
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Add2 _
Key:=Range("A8"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Thanks in advance

 

[JP10]

Sorry I don't know how to delete a post - it didn't work, it bugged at:
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields. _ Clear

 

[Alex Blakenburg]

The code @bebo021999 solves your initial issue by activating each sheet. The new issue you have is most likely because some of the sheets already have autofilter turned on and Selection.Autofilter which is intended to turn it on actually turns it off.
Try changing the 1st line with the 2nd line as shown below.

'Selection.AutoFilter
If ActiveSheet.AutoFilterMode = False Then Range("A8").AutoFilter

 

[JP10]

Thank you, but it then bugged at 'Range("A9").AutoFilter' with error:

 

[Alex Blakenburg]

We can't see your data and you haven't mentioned wanting to exclude any sheets.
In the above you seem to have changed my reference to A8 which I took from your search key range to A9.
To get the error you are getting it is likely that you have at least one sheet with A9 empty (and the cells immediately surrounding it)
Since you are relying on activating the sheet it should stop on the problem sheet and be fairly obvious which sheet doesn't have a data range around A9

 

[JP10]

Hi,

Sorry I'm playing with 2 similar (but different) datasets, but the logic is the same. In this one the filter is on A9, all the worksheets for this dataset are exactly the same, none have filters already on, A9 has the same data in it in everyone IF the macro runs in order for each sheet.

Could the order be an issue? Will it run in order across each worksheet? The macro is very long so I didn't put the whole thing in, but at the start i clear column A, and cut paste from another column (with the date in it), if it doesn't run in order there is no date to sort by in A9

If ActiveSheet.AutoFilterMode = False Then Range("A9").AutoFilter
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields. _
Clear
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields. _
Add2 Key:=Range("A9"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

 

[Alex Blakenburg]

When it errors out which sheet is the active sheet and what is in A9 ?

 

[JP10]

It was staying on the first sheet in the workbook, but just trying again, it is trying to run it in my 'Personal.XLSB' workbook - even when i run it from the workbook i want it to run on.

I have no idea what's going on - unless it's been trying to do it all along, and I just haven't worked out what it's doing before!