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

 

[Joe4]

You are most welcome!
Glad I was able to help.

 

[Guy27B]

Hi Joe4 - sorry to trouble you again, but my '2 spreadsheet solution' has resulted in an issue and the Macro needs adjusting. I've done my best to edit it but my efforts keep resulting in 'Error 9 Subscript out of range'. The problem is that the cell A5 is now in a separate workbook/excel file. I'm at a loss as to how to get the A5 value from this other workbook (Master Tracker.xlsx) and write the value to my original ("Essential Learning") workbook/worksheet. The file path for the new excel file/workbook with A5 in it is C:\Users\MY NAME - Essential Learning\Essential Learning MASTER TRACKER v2.xlsx. The relevant Cell A5 is in a worksheet/tab called "Dashboard". I need to open this second workbook (Master Tracker), go to the "Dashboard" worksheet, grab the numerical value (a percentage) from A5, close the Workbook (Master Tracker), and write A5's value to my first Workbook ("Essential Learning"), on a worksheet called "Graph" in column B as before. Is this something you can help me with please - I'm really stuck! Thanks

 

[Joe4]

OK, that is a departure from the original question, which just dealt with one workbook. Typically, you would want to start a new thread with that new variation, especially since so much time has past. However, if you post the code you tried, and let me know which line is returning the error, I may be able to help you fix it.

 

[Guy27B]

Ah I apologise Joe4. This was my first request on the forum. When I came up with the solution of using a second spreadsheet so that I wouldn't have to redo the Power Automate due to saving the spreadsheet as a Macro spreadsheet, it changed the scope for this Macro and I hadn't considered that. Thanks for your response and I really appreciate any help you can offer. Here is the code - you'll see I tried lots of variations with no luck. I've commented out all the lines that I tried. Basically, I just don't know what I'm doing but I'm trying to learn. I did get it to open the second workbook but when I tried to activiate the 'Dashboard' tab, that's when it kept giving me the error: So, from 'Read the Data from A5' onwards is what I tried.

Thanks Joe4.



Private Sub Workbook_Open()

Dim rng As Range
Dim r As Long
' Dim objWorkbook As Workbook
' Dim var As Double




' Exit if today is not the fifteenth day of the month
If Day(Date) <> 15 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 Essential Learning MASTER TRACKER v2
' Set objWorkbook = Workbooks.Open("C:\Users\MY NAME - Essential Learning\Essential Learning MASTER TRACKER v2.xlsx")


' Read the Data from A5
' Sheets("Graph").Range("B" & r).Value = Workbooks.Open("C:\Users\MY NAME - Essential Learning\Essential Learning MASTER TRACKER v2.xlsx")
' Sheets("Dashboard").Range("A5").Value


' ThisWorkbook.Sheets(Sheet2).Select
' Cells(1, 5).Value = var
' MsgBox (var)
' Worksheets("Dashboard").Activate
' var = Range("A5").Value
' MsgBox Sheets("Dashboard").Cells(1, 5)
' Sheets("Dashboard").Cells(1, 5).Select
' objWorkbook.Worksheets("Dashboard").Range ("A5")
' objWorkbook.Close



Sheets("Graph").Range("B" & r).Value = Sheets("Dashboard").Range("A5").Value

' Save changes
ActiveWorkbook.Save

' Close Excel
Application.Quit

End Sub

 

[Joe4]

See if this little tidbit of code helps you solve your issue.
When dealing with mutliple workbooks, the one containing the VBA code and the one I am opening, I like to set/use Workbook object variables.
You already did that with the one you are opening, i.e.

Dim objWorkbook As Workbook
...
Set objWorkbook = Workbooks.Open("C:\Users\MY NAME - Essential Learning\Essential Learning MASTER TRACKER v2.xlsx")

To use a workbook object variable for the file containing the VBA code, I usually have that code right at the very top before I open the other one, i.e.

Dim mcrWorkbook As Workbook

Set mcrWorkbook = ActiveWorkbook

Note that before you try selecting the sheet in a workbook, that workbook MUST first be active (and you cannot do it all in one step -you cannot select a worksheet in a non-active workbook).

So, after you open your "objWorkbook" file, if you want to select the "Dashboard" sheet on the macro file, you could do:

mcrWorkbook.Activate
Sheets("Dashboard").Activate

And you can easily bounce back-and-forth between workbooks using:

mcrWorkbook.Activate

and

objWorkbook.Activate

Armed with that knowledge, take another crack at it and see how you do.

 

[Guy27B]

Hi Joe4

Need more guidance please. I'm doing something wrong and not sure what?

I did try the code tidbits as you suggested but I keep getting the same error when I try to activate the 'dashboard' worksheet in the second file. There are two excel files: the first contains a graph with the macro code. The second contains data with a worksheet 'Dashboard' with a value in cell A5 that I'm trying to grab once a month and write it to the graph file in the worksheet 'Graph'. My aim is to have Windows Task Scheduler open the first (graph) excel file once a month automatically, which triggers the macro, which opens the 2nd excel file and grabs the Dashboard A5 value. then it writes the date in column A and the value in column B of the Graph file, saves, and closes.
Here is the code so far and the 'Sheets("Dashboard").Activate results in an 'subscript out of range' error. I appreciate any help you can offer. Thanks

 

[Guy27B]

Here is the code:
Private Sub Workbook_Open()

Dim rng As Range
Dim r As Long
Dim mcrWorkbook As Workbook
Dim GraphWorkbook As Workbook

' Dim var As Double


' Exit if today is not the fifteenth day of the month
If Day(Date) <> 26 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 Essential Learning MASTER TRACKER v2


Set GraphWorkbook = Workbooks.Open("C:\Users\MY NAME - Essential Learning\Essential Learning MASTER TRACKER v2.xlsx")
Set GraphWorkbook = ActiveWorkbook

GraphWorkbook.Activate
Sheets("Dashboard").Activate

 

[Joe4]

So the "Dashboard" sheet is in your "MASTER TRACKER" workbook, right?

Then you should just have to use:

' Open Essential Learning MASTER TRACKER v2
Set GraphWorkbook = Workbooks.Open("C:\Users\MY NAME - Essential Learning\Essential Learning MASTER TRACKER v2.xlsx")

Sheets("Dashboard").Activate

Since opening the workbook makes THAT the active workbook, you should not need to re-activate it before selecting the "Dashboard" sheet.

You shouls also capture the macro workbook at the top of your code, so you can easily reference it in later in your code, i.e.

Private Sub Workbook_Open()

Dim rng As Range
Dim r As Long
Dim mcrWorkbook As Workbook
Dim GraphWorkbook As Workbook

Set mcrWorkbook = ActiveWorkbook

 

[Guy27B]

Really sorry Joe4, but I'm getting lost here. I am confusing the variables for the two excel files. I've changed them now: The first excel file is the one with the Macro code, and the Graph tab/worksheet. The second file is the one with the Dashboard and the value in A5 that I'm trying to grab.

I understand that as I step through the code from top, line by line that I am begining with the (McrWorkbook) as active as the code writes todays date in the 'Graph' worksheet in column A. This is working great.

But then when I go to the next steps, I can open the 2nd excel file, activate it, but the last line still results in the same error - subscript out of range.

Private Sub Workbook_Open()

Dim rng As Range
Dim r As Long
Dim mcrWorkbook As Workbook
Dim DashboardWorkbook As Workbook

' Dim var As Double


' Exit if today is not the fifteenth day of the month
If Day(Date) <> 26 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 Essential Learning MASTER TRACKER v2

Set DashboardWorkbook = Workbooks.Open("C:\Users\MY NAME - Essential Learning\Essential Learning MASTER TRACKER v2.xlsx")
Set DashboardWorkbook = ActiveWorkbook
Sheets("Dashboard").Activate

 

[Joe4]

You didn't make any of the changes I showed you in my previous post:

Private Sub Workbook_Open()

Dim rng As Range
Dim r As Long
Dim mcrWorkbook As Workbook
Dim DashboardWorkbook As Workbook

Set mcrWorkbook = ActiveWorkbook

' Dim var As Double


' Exit if today is not the fifteenth day of the month
If Day(Date) <> 26 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 Essential Learning MASTER TRACKER v2
Set GraphWorkbook = Workbooks.Open("C:\Users\MY NAME - Essential Learning\Essential Learning MASTER TRACKER v2.xlsx")

Sheets("Dashboard").Activate

If you get an error on the "Sheets("Dashboard").Activate" line, then that means that either you do not have a sheet named "Dashboard" in your MASTER TRACKER workbook, or it is hidden.