I am having some trouble with a macro that manipulates a Pivot Table The PT has a filter applied for a Week Commencing date The code selects a week commencing date taken from a cell The range referenced is formatted to a date format and is as below I get the following when this tries to execute I have tried with changing Format(Range("BCR_WC"), "dd/mm/yyyy")to Format(Range("BCR_WC"), "mm/dd/yyyy") Bu this messes up the filters available within the PT and add an additional option of "12/02/2024" which even upon a refresh, doesn't go away. If I manually select the filter in the PT to the date and then run the code, that line executes with no error. Anyone iwth an idea on how to fix? TIA

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Pivot Tables & Dates

Thread starter mikeymay
Start date Dec 3, 2024

mikeymay

Well-known Member

Dec 3, 2024

I am having some trouble with a macro that manipulates a Pivot Table

The PT has a filter applied for a Week Commencing date

The code selects a week commencing date taken from a cell

The range referenced is formatted to a date format and is as below

I get the following when this tries to execute

I have tried with changing

VBA Code:

Format(Range("BCR_WC"), "dd/mm/yyyy")

VBA Code:

Format(Range("BCR_WC"), "mm/dd/yyyy")

Bu this messes up the filters available within the PT and add an additional option of "12/02/2024" which even upon a refresh, doesn't go away.

If I manually select the filter in the PT to the date and then run the code, that line executes with no error.

Anyone iwth an idea on how to fix?

TIA