You can't select a range on a sheet unless it is the active sheet.



Private Sub CommandButton1_Click()

Dim i As Integer
Dim j As Integer

Application.Goto Reference:="Alpha"
Sheets("Setup").Range("g3").Select
i = Selection.End(xlDown).row

j = i - 2
Endrange = "A" & j
Sheets("Extract").Select
Sheets("Extract").Range("A1:" & Endrange).Select
Selection.Copy

End Sub
------------------------------------
Better would be
-------------------------------------
Private Sub CommandButton1_Click()

Dim i As Integer
Dim j As Integer

i = Sheets("Setup").Range("g3").End(xlDown).row - 2
Sheets("Extract").Range("A1A:" & i).Copy
Destination:=Worksheets("SomeSheet").Range("M11")



End Sub


--
Regards,
Tom Ogilvy



"BruceG" wrote in message
...
JL & Tom,

That was part of it, it was zeroing out so I changed the count to row and
adjusted my formual but it still error 1004 out on the 2nd last line. I
held
my cursor over Endrange and it was showing as A46. What am I not doing
right?

To recap what I am trying to do - I have data trhat is on sheet Setup I
want
to know the count of rows involved of this data which varies day by day so
I
can copy formulas defines as FormRow in the Extract sheet down this count.
The formulas start on Row 1 in the Extract Sheet but the data start on Row
3
of the Setup sheet.
Thanks

Bruce

Private Sub CommandButton1_Click()

Dim i As Integer
Dim j As Integer

Application.Goto Reference:="Alpha"
Sheets("Setup").Range("g3").Select
i = Selection.End(xlDown).Row

j = i - 3
Endrange = "A" & j

Sheets("Extract").Range("A1:" & Endrange).Select **Error 1004 Here
Endrange
came up as A46

Looking at copy syntax should I have it select range "FormRow" (a1:l1)
then copy to my destination which is the same sheet but for this example
down
to a46?

Selection.Copy

End Sub


"Tom Ogilvy" wrote:

Your current problem is that


i = Selection.End(xldown).Count

i will always equal 1 and j = i -1 is zero

so Endrange = "A0"

which is an invalid range.

I suspect what you want is

i = Selection.End(xldown).Row

--
Regards,
Tom Ogilvy



"BruceG" wrote in message
...
Bob & JL, Thank for your help but it still does not work. It says Error
1004
on that line even after I tried both of y'all answers.

Could it be my whole macro be wrong and it is just hitting at the end?
Here
is the latest copy -

Thanks for your help. B

Private Sub CommandButton1_Click()

Dim i As Integer
Dim j As Integer

Application.Goto Reference:="Alpha"
Sheets("Setup").Range("g3").Select
i = Selection.End(xlDown).Count

j = i - 1
Endrange = "A" & j
Could this be wrong, this is not declared, should it be? What
would I
call it?
Sheets("Extract").Range("A1:" & Endrange).Select
Selection.Copy

End Sub


"JLatham" wrote:

If it gives you more trouble after that fix, try this instead:
Sheets("Extract").Range("A1:" & Endrange).Select
Selection.Copy

Sometimes Excel has a problem copying what isn't selected.

"BruceG" wrote:

I am getting a run error 1004 on the last line - what am I not
getting?

What I want to do is go to Point "Alpha" count all the rows
including
but
backing out 1 for the total line then go to Sheet Extract and copy a
line of
formulas (defined as range "Formula") down the sheet count number
that
was
just counted and adjusted by 1. USing a Click button to perform this
operation.

Any help would be greatly apprecioated.

BruceG

Private Sub CommandButton1_Click()

Dim i As Integer
Dim j as Integer

Application.Goto Reference:="Alpha"
Sheets("Setup").Range("g3").Select
i = Selection.End(xlDown).Count

j = i - 1

Endrange = "A" & j
Sheets("Extract").Range("A1:Endrange").Copy Error Line

End Sub