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]

Hi Joe4 - I apologies. I did try what you told me and put the reference to the active workbook at the top of the code, but when the code resulted in the error, I changed it back again. I've put it back in and run it again, but I still get the same error. I did begin to wonder, like you, about the name of my 'Dashboard' worksheet. I even tried changing it in the code to Sheets ("Sheet2").Activate instead, but both names result in the same error.

 

[Joe4]

Why do you keep putting that:

Set DashboardWorkbook = ActiveWorkbook

line back in there?
I keep removing it, so I am not sure why you keep adding it back in.
You don't need it, you are alredy setting your workbook object variable in the previous line.

Temporarily replace that last section (putting in the correct file path and name) with this and copy/paste a screen print of what it returns:

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

Dim i As Long
Dim shts As String

For i = 1 To DashboardWorkbook.Worksheets.Count
    shts = shts & DashboardWorkbook.Sheets(i).Name & "," & vbCrLf
Next i
   
MsgBox shts

 

[Guy27B]

haha, sorry Joe4. That must have been frustrating. I've taken it out now and I understand that the 'Workbooks.Open' line of code makes that Workbook the active one. Thank you.

I added in your new code and here is the Message box contents - it seems to have put the names of all of the worksheets for the excel DashboardWorkbook together! Never seen that before!

 

[Joe4]

OK, so it seems to confirm that you do have a sheet named "Dashboard" and there do not appear to be any extra spaces at the beginning or end of that sheet name.
Is the "Dashboard" sheet visible or hidden in that workbook?

If you remove the line I told you and all those temporaru lines, so that you are left with just this in that section:

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

do you still get that error on the last line above?

 

[Guy27B]

Done and results in same error.

 

[Joe4]

OK, let's find out which line of code it doesn't like.

Place a breakpoint on the first line of code in that procedure (specifically, this line):

If Day(Date) <>26 Then Exit Sub

If you are not sure how to do this, then see this:

MS Excel 2013: Set Breakpoint in VBA

This Excel tutorial explains how to set a breakpoint in the VBA debugging environment in Excel 2013 (with screenshots and step-by-step instructions).

www.techonthenet.com

Then, save and close the Workbook.

Now, open the workbook so that the code runs.
It should stop at the line with the breakpoint.

Once it does that, click on the F8 key repeatedly, to run your code one line at a time, keeping an eye which line of code you are on.
Note the line of code you are on when this error occurs.
This is the line of code we need to focus on.

 

[Guy27B]

Done. The error occurs on the last line: 'Sheets("Dashboard").Activate'.

 

[Joe4]

That really makes no sense to me!

It shouldn't make a difference, but what if you add this line before the last line:

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

In theory, you shouldn't need to activate that workbook, because opening that file should make it the active file.
But let's try explicitly activating it and see if that makes any difference.

 

[Guy27B]

Exactly the same. The error still occurs on the final line.

 

[Joe4]

OK, are you 100% sure that this "Dashboard" worksheet is visible when you open this file?
And there isn't anything "special" about this sheet, is there?

Other than that, I am afraid I there isn't anything else I can do or suggest without having access to this workbook to see exactly what is going on.