Macro: Watch a cell and copy if changed Solved/Closed

Question

Hello, 

The problem in a nutshell is that I have 5 pivot tables on one worksheet, each with a field called "date" above the main text. Each day the information is updated and we look at the information for the previous day, which unfortunately means selecting the correct date in each pivot table. Fair enough this only takes a minute or so but my curiosity is roused and i think that there is a macro that can do this simple task for me.

I have of course already tried typing as lookup formula into the cells that i want to change but excel forbids this action within a pivot table.

What i need is a Macro that;

1) Watches for if i alter the cell relating to the Date field in the first pivot table on the page and,

2) Proceeds to type the same text into the cells relating to the next 4 tables' date fields. 

Clearly, this may be a slight challenge as a copy/paste action cannot be executed within a pivot table field.

I eagrely await a response



Configuration: Windows XP / Internet Explorer 7.0

rizvisa1 · Answer

Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc 
A N D post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too

IneptMacro · Answer

[URL=http://wikisend.com/download/475026/Book1.xls]Book1.xls[/URL]

OK, this is the link to the WIKISEND file share. (Hope it works)

The file is a simplistic and rather jovial instance of the problem I am trying to solve. I shall now describe the issue again using references from the example workbook...

Ok, so the file contains two sheets with information in them, the first is the raw data and the second is a sheet containing multiple pivot tables based upon the data. Please assume that to combine all of the information into one pivot table is impractical as the real world instance of this would result in a whole A4 page of data which is not conducive to the visual management aspect of the project. Now, every morning either myself or my admin guy must alter the "date" field shown as row's 1, 8 and 15 in the example. Each has to be altered separately but is required to be the same date for the eventual printout. 

So, my question is regarding a macro to firstly, recognise when the date field from row 1 is changed and second, type the same date into the other "date" fields on the sheet. This problem cannot be solved by creating a "=b1" formula in the date fields at row 8 and row 15 (try it, the result is an error message).

Part two of the problem, which only occured to me when compiling the example sheet, is that changing the dates will require the Pivot tables to occupy more space in order to show the extra information that "2/1/2000" would show. Is there a way to automatically add the required number of columns or delete them as they are needed? I feel I must note that this is not an issue i have on the real world workbook but it would be nice to know if there is an answer :)

I hope my added description helps

rizvisa1 · Answer

I am very raw when it comes to pivot table

For your first issue, I dont know how the system would know that date has changed

lets say that you use G1 cell to change the date

So in macro, you go to sheet where the pivot tables and the G1 cell is ( where the you enter the date). In your sample that would be sheet4

define this function

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


as


Private Sub Worksheet_Change(ByVal Target As Range)

    If (Target.Address <> "$G$1") Then Exit Sub
    
    On Error GoTo Err_Handler
    
    Application.EnableEvents = False

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = CDate(Target)
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Date").CurrentPage = CDate(Target)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Date").CurrentPage = CDate(Target)
    
Exit_Sub:
    Application.EnableEvents = True
    Exit Sub
    
Err_Handler:

    GoTo Exit_Sub
End Sub


For 2nd part, I am sure there is. Just have to figure out how. Did I mention that PIVOT table is my weakest point

Macro: Watch a cell and copy if changed

3 responses

Similar discussions

Subscribe To Our Newsletter!