Capture Value from Changing Cell

[Guy27B]

I have set up a spreadsheet with a dashboard. Cell A5 contains a percentage that changes on a daily basis. I would like to capture the value of A5 on the 1st of each month and put it in a table and then create a line graph from this table. Is there a formula I can use to capture A5's value, and put it in a table where it is fixed and doesn't change when A5 changes? Thanks

 

[Guy27B]

By 'visible' do you mean that it is not hidden? I've included a screenshot below of the tab/worksheet - it is not hidden. It's just a normal worksheet. I've also included a screenshot of the editors properties for both Excel files - in case you can spot something odd there?

 

[Guy27B]

I also tried entering a value of 1 into cell A16 on a different worksheet 'Validation' in the Dashboard Workbook and it resulted in the same error. So I don't think that the problem is with the Dashboard worksheet. Maybe it's a problem with the Dashboard spreadsheet file itself?

 

[Guy27B]

The Dashboard file is a normal .xlsm file.

 

[Joe4]

I see nothing there that looks like it should be causing that error.

Maybe try the following from your other "Macro" Workbook:
1. Turn on the Macro Recorder
2. Open the other workbook
3. Select the "Dashboard" sheet (if it opens directly to the "Dashboard" sheet, select some other sheet, and then re-select the "Dashboard" sheet).
4. Stop the Macro Recorder
5. View the code you just recorded, and paste it here (you can black out your file name, if needed)

 

[Joe4]

The Dashboard file is a normal .xlsm file.

In addition to my previous post, is there any event procedure code in this file?
That would be VBA code you see in the "ThisWorkbook" or the various "Sheet" modules in that workbook?

Another possibility is that this file has become corrupted, in which case you may want to try to re-create it from a new workbook.

 

[Guy27B]

There are no macros or VBA code in the Dashboard Excel file - just lots of formulas, but no VBA.


Code from the Macro:
Sub Open_Dashboard()
'
' Open_Dashboard Macro
'

'
Windows("Book1").Activate
Sheets("Dashboard").Select
Windows("Essential Learning CMA GRAPH (MACRO).xlsm").Activate
End Sub

 

[Joe4]

There are no macros or VBA code in the Dashboard Excel file - just lots of formulas, but no VBA.

Is there is no VBA code in it, why does it have a "xlsm" extension instead of an "xlsx" extension?

Sub Open_Dashboard()
'
' Open_Dashboard Macro
'

'
Windows("Book1").Activate
Sheets("Dashboard").Select
Windows("Essential Learning CMA GRAPH (MACRO).xlsm").Activate
End Sub

That does not look right to me.
If you record yourself opening file, you should see a "Workbooks.Open..." command like this:

Sub Macro2()
'
' Macro2 Macro
'

'
    ChDir "C:\TEMP"
    Workbooks.Open Filename:="C:\TEMP\Book1111.xlsx"
End Sub

 

[Guy27B]

Okay, thanks.
The Dashboard workbook does not have any VBA code and the file extension is .xlsx. The other excel file is the mcrWorkbook with the VBA code we are working on and has an extension of .xlsm. Apologies - I confused this earlier.

 

[Guy27B]

I had recorded the Macro from the .xlsm macroWorkbook.

 

[Joe4]

OK, then can you please try to do what I asked earlier?
Make sure the Tracker file with the Dashboard sheet is closed before running these steps, and please run them exactly as stated:

try the following from your other "Macro" Workbook:
1. Turn on the Macro Recorder
2. Open the other workbook (the tracker file with the Dashboard worksheet)
3. Select the "Dashboard" sheet (if it opens directly to the "Dashboard" sheet, select some other sheet, and then re-select the "Dashboard" sheet).
4. Stop the Macro Recorder
5. View the code you just recorded, and paste it here (you can black out your file name, if needed)