Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to take
some cell values in Invoice, open Stats and place them in various cells.
works fine as a macro. However if I put the code in a control button (click
to run) then it comes up with a 1004 error. played around making sure it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro - so
it's no big deal but can anyone explain why this happens (any why it's not
relevant in Access)

thanks
bruce

Could you post your code for us

"BLJ" wrote:

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to take
some cell values in Invoice, open Stats and place them in various cells.
works fine as a macro. However if I put the code in a control button (click
to run) then it comes up with a 1004 error. played around making sure it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro - so
it's no big deal but can anyone explain why this happens (any why it's not
relevant in Access)

thanks
bruce

Hi below is the gist

Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

the error occurs when it hits the "rows" . As mentioned, works fine just as
a plain macro, but not as code within a control ... which I gather is the
point I just don't know why

Bruce

Could you post your code for us

"BLJ" wrote:

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to
take
some cell values in Invoice, open Stats and place them in various cells.
works fine as a macro. However if I put the code in a control button
(click
to run) then it comes up with a 1004 error. played around making sure it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro - so
it's no big deal but can anyone explain why this happens (any why it's
not
relevant in Access)

thanks
bruce

this works for me
Private Sub CommandButton1_Click()
Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range ("A1").Select


End Sub

"BLJ" wrote:

Hi below is the gist

Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

the error occurs when it hits the "rows" . As mentioned, works fine just as
a plain macro, but not as code within a control ... which I gather is the
point I just don't know why

Bruce

Could you post your code for us

"BLJ" wrote:

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to
take
some cell values in Invoice, open Stats and place them in various cells.
works fine as a macro. However if I put the code in a control button
(click
to run) then it comes up with a 1004 error. played around making sure it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro - so
it's no big deal but can anyone explain why this happens (any why it's
not
relevant in Access)

thanks
bruce

hmmmm.... tried the code with two new clean files, new command button and
still get the error - says

run-time error '1004'
select method of Range class failed

debug stops on Rows.........

put the code as a macro and merely tools-macro-run ........ works fine

????????

bruce

this works for me
Private Sub CommandButton1_Click()
Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range ("A1").Select


End Sub

"BLJ" wrote:

Hi below is the gist

Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

the error occurs when it hits the "rows" . As mentioned, works fine just
as
a plain macro, but not as code within a control ... which I gather is
the
point I just don't know why

Bruce

Could you post your code for us

"BLJ" wrote:

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to
take
some cell values in Invoice, open Stats and place them in various
cells.
works fine as a macro. However if I put the code in a control button
(click
to run) then it comes up with a 1004 error. played around making sure
it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro -
so
it's no big deal but can anyone explain why this happens (any why it's
not
relevant in Access)

thanks
bruce

Unqualified ranges in a General module will refer to the activesheet.

But unqualified ranges behind a worksheet will refer to the worksheet owning the
code.

Workbooks.Open Filename:="C:\stats.xls"
workbooks("stats.xls").Activate
workbooks("stats.xls").worksheets("whatsthesheetna me").Rows(3).Insert

I would guess that the workbook you just opened is already active.

So you cold drop the select and activate:

Workbooks.Open Filename:="C:\stats.xls"
workbooks("stats.xls").worksheets("whatsthesheetna me").Rows(3).Insert




BLJ wrote:

Hi below is the gist

Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

the error occurs when it hits the "rows" . As mentioned, works fine just as
a plain macro, but not as code within a control ... which I gather is the
point I just don't know why

Bruce

Could you post your code for us

"BLJ" wrote:

Hi, bit new to excel but have been writing VBA in Access for years.

I've got two workbooks "invoice" and "stats". I've writen a macro to
take
some cell values in Invoice, open Stats and place them in various cells.
works fine as a macro. However if I put the code in a control button
(click
to run) then it comes up with a 1004 error. played around making sure it
was activated etc but always failed.

The work around was to make clicking the button merely run the macro - so
it's no big deal but can anyone explain why this happens (any why it's
not
relevant in Access)

thanks
bruce




--

Dave Peterson

"BLJ" wrote in message
u...
Hi below is the gist

Workbooks.Open Filename:="C:\stats.xls"
Windows("stats.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

the error occurs when it hits the "rows" .

maybe you have a problem with context, since you have no object qualifier
for rows()

have you tried

dim wb as workbook, ws as worksheet
set wb=Workbooks("\stats.xls")
set ws=wb.activesheet
ws.rows("3:3").select