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

Sheets("Extract").Range("A1:" & Endrange).Copy

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BruceG" wrote in message
...
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

Change that to
Sheets("Extract").Range("A1:" & Endrange).Copy

You need to to concatenate the calculated address Endrange with the first
part instead of trying to do it the way you did. Excel is taking Endrange as
a literal the way you have it and has no clue as to what you are talking
about.

"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

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

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

Could be that the value in i (and subsequently in j) is causing a problem.
Check the value of i, j and Endrange just before the attempt to use Endrange
to create the range to copy.

Easy way is to just insert
Stop
as a command just after the Endrange="A" & j
statement. You can then hover the cursor over the variable names and their
value will be displayed, or use the Immediate window to "Print i, j,
Endrange" (without quotes) to see what those 3 values are.
If i ends up being just 1 then j will be zero, and A0 is not a valid address.

Did you try the .Select followed by .Copy statements?

"BruceG" wrote:

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

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

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

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