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

my workbook reference is not working
 

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

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

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

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

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