Trouble referring to a (dynamic) named range on another Excel shee

[jrbor76]

On one page of my Excel workbook, my macro automatically selects the cell
containing the previous month from a list in column A. I gave that cell a
range name, "PrevMo", in VBA. (I had my macro go to the end of the column and
select the cell, as the previous month will change over time.)
On another page of the same workbook, I have another list of months, and I
am trying to pick the one cell out of that column that matches the "PrevMo"
cell from the first sheet. (I used the EOMONTH command to format all these
cells on both sheets to the end of the month, and used mmm 'yy format for
their eventual display on charts.)
However, my macro keeps giving me a 1004 run-time error when I try to
compare the months on the second sheet to the named range on the first sheet.
I've been trying quotes around everything, I've been including the workbook
name in the code (although I know it's not strictly necessary as I'm only
using one workbook right now). Here's the code I'm using along with comments.
Apologies in advance, it's probably something ridiculously mundane. I've
used VBA quite a bit but not for a few years. I'm rusty.

Sheets("Internal_PPM").Select '[first sheet I mentioned]
Range("A3").Select '[start of the column of months]
ActiveCell.End(xlDown).Select '[goes to the end of column of months,
which will always be the month prior to current month]
PrevMo = ActiveCell.Address '[names the cell containing the previous
month]
Sheets("12_Month_PC").Select '[second sheet I mentioned]
ActiveWorkbook.Sheets("12_Month_PC").Range("A1").Value =
ActiveWorkbook.Sheets("Internal_PPM").Range("PrevMo").Value ['this is where
I keep getting errors. Cell A1 in the 12_Month_PC sheet is blank. Months in
this sheet are in column A, but several cells down. Trying to set cell A1 in
the second sheet equal to the previous month as done in the first sheet, but
getting nowhere.]

Any advice is appreciated.

 

[JLGWhiz]

I think that instead of making PrevMo = ActiveCell.Address, I would make it
equal ActiveCell.Value

PrevMo = ActiveCell.Value
Sheets("12_Month_PC").Range("A1") = PrevMo

That should put the same data in 12_Month_PC!A1 as was on the other sheet at
the end of the column. Then you could use the Find method or a For...Next
loop to locate a match to Range("A1") pf Sheets("12_Month_PC").

 

[jrbor76]

Think it worked. Thanks.

I think that instead of making PrevMo = ActiveCell.Address, I would make it
equal ActiveCell.Value

PrevMo = ActiveCell.Value
Sheets("12_Month_PC").Range("A1") = PrevMo

That should put the same data in 12_Month_PC!A1 as was on the other sheet at
the end of the column. Then you could use the Find method or a For...Next
loop to locate a match to Range("A1") pf Sheets("12_Month_PC").

On one page of my Excel workbook, my macro automatically selects the cell
containing the previous month from a list in column A. I gave that cell a
range name, "PrevMo", in VBA. (I had my macro go to the end of the column
and
select the cell, as the previous month will change over time.)
On another page of the same workbook, I have another list of months, and I
am trying to pick the one cell out of that column that matches the
"PrevMo"
cell from the first sheet. (I used the EOMONTH command to format all these
cells on both sheets to the end of the month, and used mmm 'yy format for
their eventual display on charts.)
However, my macro keeps giving me a 1004 run-time error when I try to
compare the months on the second sheet to the named range on the first
sheet.
I've been trying quotes around everything, I've been including the
workbook
name in the code (although I know it's not strictly necessary as I'm only
using one workbook right now). Here's the code I'm using along with
comments.
Apologies in advance, it's probably something ridiculously mundane. I've
used VBA quite a bit but not for a few years. I'm rusty.

Sheets("Internal_PPM").Select '[first sheet I mentioned]
Range("A3").Select '[start of the column of months]
ActiveCell.End(xlDown).Select '[goes to the end of column of months,
which will always be the month prior to current month]
PrevMo = ActiveCell.Address '[names the cell containing the previous
month]
Sheets("12_Month_PC").Select '[second sheet I mentioned]
ActiveWorkbook.Sheets("12_Month_PC").Range("A1").Value =
ActiveWorkbook.Sheets("Internal_PPM").Range("PrevMo").Value ['this is
where
I keep getting errors. Cell A1 in the 12_Month_PC sheet is blank. Months
in
this sheet are in column A, but several cells down. Trying to set cell A1
in
the second sheet equal to the previous month as done in the first sheet,
but
getting nowhere.]

Any advice is appreciated.