Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble referring to a (dynamic) named range on another Excel shee
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").V alue = ActiveWorkbook.Sheets("Internal_PPM").Range("PrevM o").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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble referring to a (dynamic) named range on another Excel shee
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" wrote in message ... 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").V alue = ActiveWorkbook.Sheets("Internal_PPM").Range("PrevM o").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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble referring to a (dynamic) named range on another Excel
Think it worked. Thanks.
"JLGWhiz" wrote: 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" wrote in message ... 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").V alue = ActiveWorkbook.Sheets("Internal_PPM").Range("PrevM o").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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to specific cell in named range in Excel formula | Excel Worksheet Functions | |||
Referring to a named range | Excel Programming | |||
Trouble with dynamic named range | Excel Programming | |||
How do I use indirect when referring to a named range in a closed | Excel Worksheet Functions | |||
referring to a named range on another worksheet | Excel Programming |