| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
my workbook reference is not working
I have a workbook that uses a macro to copy itself, save it to another file,
then populates sheets with formulas and such. Everything works great until a look up statement. Below, I have pasted the troubling code in the macro. The referenced sheet, "Trending Source" is the original workbook that was copied and is still open. Dim myrange As Range Dim mysheet As Worksheet Worksheets("Data (altered)").Select Range("F14").Select ' Set myrange = Range("f14") ' Set mysheet = Worksheets("Data (altered)") Range("F14").Select 'run time error 1004 occurs in following statement ActiveCell.FormulaR1C1 = _ "=LOOKUP(df,[Trend source.xls]Stats!R1C4:R34C4, [Trend source.xls]Stats!R1C5:R34C5)" Range("F15").Select . . . Any ideas? TIA |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
my workbook reference is not working
Hi
not tested but try: "=LOOKUP(df,'[Trend source.xls]Stats!R1C4:R34C4, [Trend source.xls]Stats'!R1C5:R34C5)" "Papa Jonah" wrote: I have a workbook that uses a macro to copy itself, save it to another file, then populates sheets with formulas and such. Everything works great until a look up statement. Below, I have pasted the troubling code in the macro. The referenced sheet, "Trending Source" is the original workbook that was copied and is still open. Dim myrange As Range Dim mysheet As Worksheet Worksheets("Data (altered)").Select Range("F14").Select ' Set myrange = Range("f14") ' Set mysheet = Worksheets("Data (altered)") Range("F14").Select 'run time error 1004 occurs in following statement ActiveCell.FormulaR1C1 = _ "=LOOKUP(df,[Trend source.xls]Stats!R1C4:R34C4, [Trend source.xls]Stats!R1C5:R34C5)" Range("F15").Select . . . Any ideas? TIA |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
my workbook reference is not working
Are you saying that all of the syntax can be combined, or is not needed at all?
"Don Guillett" wrote: Could it be Trending Source vs Trend Source? It appears that you are referring to: a workbook named Trend Source a worksheet named Stats BTW you don't need all this selecting Worksheets("Data (altered)").Range("F14").FormulaR1C1 = _ "=LOOKUP(df,[Trend source.xls]Stats!R1C4:R34C4, [Trend source.xls]Stats!R1C5:R34C5)" -- Don Guillett SalesAid Software "Papa Jonah" wrote in message ... I have a workbook that uses a macro to copy itself, save it to another file, then populates sheets with formulas and such. Everything works great until a look up statement. Below, I have pasted the troubling code in the macro. The referenced sheet, "Trending Source" is the original workbook that was copied and is still open. Dim myrange As Range Dim mysheet As Worksheet Worksheets("Data (altered)").Select Range("F14").Select ' Set myrange = Range("f14") ' Set mysheet = Worksheets("Data (altered)") Range("F14").Select 'run time error 1004 occurs in following statement ActiveCell.FormulaR1C1 = _ "=LOOKUP(df,[Trend source.xls]Stats!R1C4:R34C4, [Trend source.xls]Stats!R1C5:R34C5)" Range("F15").Select . . . Any ideas? TIA |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
my workbook reference is not working
Frank,
I don't know why, but the ' seems to be the keey although I needed to add a couple more. I needed one between the first "Stats" and "!" as well as before the second "[Trend source" thanks "Frank Kabel" wrote: Hi not tested but try: "=LOOKUP(df,'[Trend source.xls]Stats!R1C4:R34C4, [Trend source.xls]Stats'!R1C5:R34C5)" "Papa Jonah" wrote: I have a workbook that uses a macro to copy itself, save it to another file, then populates sheets with formulas and such. Everything works great until a look up statement. Below, I have pasted the troubling code in the macro. The referenced sheet, "Trending Source" is the original workbook that was copied and is still open. Dim myrange As Range Dim mysheet As Worksheet Worksheets("Data (altered)").Select Range("F14").Select ' Set myrange = Range("f14") ' Set mysheet = Worksheets("Data (altered)") Range("F14").Select 'run time error 1004 occurs in following statement ActiveCell.FormulaR1C1 = _ "=LOOKUP(df,[Trend source.xls]Stats!R1C4:R34C4, [Trend source.xls]Stats!R1C5:R34C5)" Range("F15").Select . . . Any ideas? TIA |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
my workbook reference is not working
I'm saying that when you get your ' 's properly set for the spacing in
the names, that you can make it a ONE liner as I did. -- Don Guillett SalesAid Software "Papa Jonah" wrote in message ... Are you saying that all of the syntax can be combined, or is not needed at all? "Don Guillett" wrote: Could it be Trending Source vs Trend Source? It appears that you are referring to: a workbook named Trend Source a worksheet named Stats BTW you don't need all this selecting Worksheets("Data (altered)").Range("F14").FormulaR1C1 = _ "=LOOKUP(df,[Trend source.xls]Stats!R1C4:R34C4, [Trend source.xls]Stats!R1C5:R34C5)" -- Don Guillett SalesAid Software "Papa Jonah" wrote in message ... I have a workbook that uses a macro to copy itself, save it to another file, then populates sheets with formulas and such. Everything works great until a look up statement. Below, I have pasted the troubling code in the macro. The referenced sheet, "Trending Source" is the original workbook that was copied and is still open. Dim myrange As Range Dim mysheet As Worksheet Worksheets("Data (altered)").Select Range("F14").Select ' Set myrange = Range("f14") ' Set mysheet = Worksheets("Data (altered)") Range("F14").Select 'run time error 1004 occurs in following statement ActiveCell.FormulaR1C1 = _ "=LOOKUP(df,[Trend source.xls]Stats!R1C4:R34C4, [Trend source.xls]Stats!R1C5:R34C5)" Range("F15").Select . . . Any ideas? TIA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Absolute Reference not working | Excel Worksheet Functions | |||
| External reference not working | Excel Worksheet Functions | |||
| External reference not working? | Excel Discussion (Misc queries) | |||
| Sheet Reference is not working | Excel Worksheet Functions | |||
| workbook linking cells not working within a workbook | Excel Discussion (Misc queries) | |||
Linear Mode
