Hi

I have a number of workbooks, for which I would like to refresh the data
(pulling info from another workbook using MS Query) that is in each.

What I wish to end up with, in each workbook: is my data refreshed; filters
applied to the columns; and subtotalling applied.

I had produced a macro to do this and it did seem fine until it encounters a
workbook that has either filters or subtotalling already applied.

My problem is that the workbooks won't always be in the same format (i.e.
some will have these applied, others won't) when I want to update the
information.

What is the function to apply filtering and subtotalling if these are not
already applied - but if they are already applied, remove them and reapply
them after the data refresh?


Thanks

First, I wouldn't use Autofilter and subtotals on the same range. They don't
play nice since each uses visible rows to do their counts/sums/averages...

Option Explicit
Sub testme()

Dim wks As Worksheet
Set wks = Worksheets("Sheet1")

With wks
.AutoFilterMode = False
.Cells.RemoveSubtotal
End With

End Sub


BabyMc wrote:

Hi

I have a number of workbooks, for which I would like to refresh the data
(pulling info from another workbook using MS Query) that is in each.

What I wish to end up with, in each workbook: is my data refreshed; filters
applied to the columns; and subtotalling applied.

I had produced a macro to do this and it did seem fine until it encounters a
workbook that has either filters or subtotalling already applied.

My problem is that the workbooks won't always be in the same format (i.e.
some will have these applied, others won't) when I want to update the
information.

What is the function to apply filtering and subtotalling if these are not
already applied - but if they are already applied, remove them and reapply
them after the data refresh?

Thanks

--

Dave Peterson

Dave

Just a quick note to thank you for your suggestion - and apologies for not
replying sooner (despite requesting such, I do not seem to be notified of
replies to my posting) - I have been away and am now busy on other tasks.

However I will try your solution as soon as I have the opportunity.


"Dave Peterson" wrote:

First, I wouldn't use Autofilter and subtotals on the same range. They don't
play nice since each uses visible rows to do their counts/sums/averages...

Option Explicit
Sub testme()

Dim wks As Worksheet
Set wks = Worksheets("Sheet1")

With wks
.AutoFilterMode = False
.Cells.RemoveSubtotal
End With

End Sub


BabyMc wrote:

Hi

I have a number of workbooks, for which I would like to refresh the data
(pulling info from another workbook using MS Query) that is in each.

What I wish to end up with, in each workbook: is my data refreshed; filters
applied to the columns; and subtotalling applied.

I had produced a macro to do this and it did seem fine until it encounters a
workbook that has either filters or subtotalling already applied.

My problem is that the workbooks won't always be in the same format (i.e.
some will have these applied, others won't) when I want to update the
information.

What is the function to apply filtering and subtotalling if these are not
already applied - but if they are already applied, remove them and reapply
them after the data refresh?

Thanks

--

Dave Peterson
.