Create macro that copy paste's data

[Jyggalag]

Hi all,

I am having a huge issue at the moment with some macros i created (through record macro).

Basically, I have this excel sheet:

I tried to record some macro's, where once I press unfreeze, they post this formula:

=IF(ISBLANK(B9),"",TODAY()) for the left one
=IF(ISBLANK(C9),"",TODAY()) for the right one

And once I press unfreeze, they simply copy paste the date and paste it onto itself as a value, so it is "frozen" and removes the formula.

However, whenever I create these macros, they work once I record 2 of them, but once I make all 4 macro, they end up collectively bugging and showing the error above.

It is true, that cell B9 and C9 are a part of a pivot table, however, the formulas work fine by themselves and the macro is not making any changes to the cells in the pivot table.

So my question is, if somebody can please help me a optimize 2 formulas that freezes and unfreezes (based on the formula above) a cell value?

Would greatly appreciate it so I can finalize my macros!

Thank you so much all

Kind regards,
Jyggalag

 

[Pilgrim_Paul]

Your concept worked fine for me with the simple macros I wrote.

Check your macros that you are not accidentally pasting into B9 and/or C9, instead of your intended targets outside the pivot table.

 

[Jyggalag]

H

Your concept worked fine for me with the simple macros I wrote.

Check your macros that you are not accidentally pasting into B9 and/or C9, instead of your intended targets outside the pivot table.

Hi,

I am getting this error:

I recorded the macros for freeze and unfreeze and in VBA they look like this:

Any idea why they do not work?

 

[Jyggalag]

Your concept worked fine for me with the simple macros I wrote.

Check your macros that you are not accidentally pasting into B9 and/or C9, instead of your intended targets outside the pivot table.

And if I try the unfreeze macro (which should definitely work), I get this error:

 

[Jyggalag]

I fixed it!

It was because when I create the macros, I have to manually click cell C7 and B7 first. I forgot to do this in some of the macros, because I was already in the cell when I started recording.

My bad, apologies for the confusion! Very new to macro recording

Your concept worked fine for me with the simple macros I wrote.

Check your macros that you are not accidentally pasting into B9 and/or C9, instead of your intended targets outside the pivot table.

 

[Pilgrim_Paul]

Congrats!