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]

Okay, I'm sure that I did exactly what you stated. I closed all files. Then opened a windows explorer window with my two excel files in it. Then I opened the Macro Excel file. Started 'Record Macro' and named it 'Open_Dashboard' and stored macro in New Workbook. Clicked 'Ok'.

Then I double clicked the 'Dashboard' Excel file in the windows explorer window. When this file opened I clicked on the 'Dashboard' tab, clicked on another tab 'Validation' and then clicked on the 'Dashboard' tab again. Minimised this window to get back to the Macro Excel file and click 'Stop Recording'. Here is the code:

Sub Open_Dashboard()
'
' Open_Dashboard Macro
'

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

 

[Joe4]

OK, I guess I should have been more explicit. You should NOT be using Windows Explorer for any of this.
And we are only working with the two files we have, no new files.

So we have the:
- Excel workbook with the Macros
- Excel workbook with the Dashboard sheet

Close out all Excel workbooks EXCEPT the Excel workbook with the Macro we are working on.
From THAT workbook, turn on the Macro Recorder, and record the steps I laid out, but being sure to open the Excel workbook with the Dashboard sheet from your current session of Excel (NOT using Windows Explorer).

If you don't end up with a "Workbooks.Open" command somewhere in your recorded code, you have not done it correctly.

 

[Guy27B]

Okay, I think I've done it right this time. The 'Windows' line of code is only because I had to click back onto the first excel (Macro one) file to stop the recording.


Code:
Sub Open_Dashboard()
'
' Open_Dashboard Macro
'

'
Workbooks.Open Filename:= _
"C:\Users\Guy.Poland\Competition and Markets Authority\Academy - Essential Learning\Essential Learning MASTER TRACKER v2.xlsx"
Sheets("Dashboard").Select
Range("A5").Select
Windows("Essential Learning CMA GRAPH (MACRO).xlsm").Activate
End Sub

 

[Guy27B]

Oh, I meant to say that I also click into cell A5 on the Dashboard worksheet - as this is the cell with the value I need. I just wanted to see what the code would be for this.

 

[Guy27B]

Got this to work so far. It opens the Dashboard workbook at the (HR) tab, select cell A5 and displays its value in a Message box. But it results in that same out of range error when it comes to the Sheets("Dashboard").Activate line. So it will open the excel file and read it, but results in an error when I ask it to goto another tab/worksheet!

Private Sub Update_Graph_from_Dashboard()

Dim rng As Range
Dim r As Long
Dim mcrWorkbook As Workbook
Dim DashboardWorkbook As Workbook
Set mcrWorkbook = ActiveWorkbook
Dim shts As String
Dim Data As Double



' Exit if today is not the fifteenth day of the month
If Day(Date) <> 27 Then Exit Sub

' Check to see if record has already been updated (if so, exit sub)
Set rng = Sheets("Graph").Range("A:A")
If Application.WorksheetFunction.CountIf(rng, Date) > 0 Then Exit Sub

' Update Graph sheet with new record
r = Sheets("Graph").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Graph").Range("A" & r).Value = Date


' Open Dashboard
Set DashboardWorkbook = Workbooks.Open(Filename:="C:\Users\Guy.Poland\Competition and Markets Authority\Academy - Essential Learning\Essential Learning MASTER TRACKER v2.xlsx")

Range("A5").Select
shts = Range("A5").Value
MsgBox (shts)



Sheets("Dashboard").Activate

 

[Guy27B]

Sorry, meant to say that I did this most recent code from a brand new Macroworkbook and started from scratch. The error always seems to occur when I ask it to go to a different tab after opening the Dashboard workbook file! I'm totally confused by this!

 

[Joe4]

I don't know. It makes no sense to me.
Without having access to the files, I have nothing else to offer.

 

[Guy27B]

Joe4 - you've been an absolute star and definitely went above and beyond to help me here. Thank you so much for all your time, consideration and effort. By way of a final update I wanted to let you know that I spoke with the IT department today asking if anyone could help. They replied that the use of Macro's is blocked by our default ICT security policies. Perhaps this is why the macro wouldn't work for us! I would have been wise to check this with IT at the start. A lesson learnt! All the best and thanks again.

 

[Joe4]

You are welcome.