my workbook reference is not working

G

Guest

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
 
D

Don Guillett

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)"
 
G

Guest

Hi
not tested but try:
"=LOOKUP(df,'[Trend source.xls]Stats!R1C4:R34C4,
[Trend source.xls]Stats'!R1C5:R34C5)"
 
G

Guest

Are you saying that all of the syntax can be combined, or is not needed at all?



Don Guillett said:
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
(e-mail address removed)
Papa Jonah said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
Hi
not tested but try:
"=LOOKUP(df,'[Trend source.xls]Stats!R1C4:R34C4,
[Trend source.xls]Stats'!R1C5:R34C5)"


Papa Jonah said:
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
Click to expand...
Click to expand...
 
D

Don Guillett

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
(e-mail address removed)
Papa Jonah said:
Are you saying that all of the syntax can be combined, or is not needed at all?



Don Guillett said:
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
(e-mail address removed)
Papa Jonah said:
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

lookup isn't working 2
Filldown Formula 1
Copy and Paste select data to another workbook. 7
Excel macro error when workbook is shared 0
How to write a macro that will clear validated cells to blank? 3
Running macro in one workbook that refers to variable in another 3
My Macro does NOT Delete ROWS?? 1
Runtime Error '-2147417848 (80010108)' 3

Top