xlFillDefault, based on values in ColA to ColZ
 

I guess Im not going about this the right way. Im trying to find the last
used row, based on Column Z, then fill-down, the contents in A and 1 row
above to Z and 1 row above. For instance, if Z17 has data in it, I want to
copy down A16:Z16 into A17:Z17.

Heres the code Im trying:
Dim dynarow As Long
dynarow = Worksheets("Worksheet").Cells(Rows.Count, "Z").End(xlUp).Row
Selection.AutoFill Destination:=Range("A" & dynarow & ":Z" & dynarow + 1),
Type:=xlFillDefault

I keep getting the following error: €˜AutoFill method of range class failed
I guess the range is not defined properly.

Im completely open to suggestions as to how to do this.

Thanks!


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

xlFillDefault, based on values in ColA to ColZ
 

Hi Ryan, I deleted the Type:=xlFillDefault and it worked fine.



"ryguy7272" wrote in message
...
I guess I'm not going about this the right way. I'm trying to find the
last
used row, based on Column Z, then fill-down, the contents in A and 1 row
above to Z and 1 row above. For instance, if Z17 has data in it, I want
to
copy down A16:Z16 into A17:Z17.

Here's the code I'm trying:
Dim dynarow As Long
dynarow = Worksheets("Worksheet").Cells(Rows.Count, "Z").End(xlUp).Row
Selection.AutoFill Destination:=Range("A" & dynarow & ":Z" & dynarow + 1),
Type:=xlFillDefault

I keep getting the following error: 'AutoFill method of range class failed'
I guess the range is not defined properly.

I'm completely open to suggestions as to how to do this.

Thanks!


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

xlFillDefault, based on values in ColA to ColZ
 

Thanks JLGWhiz. I tried that. Still doesn't work.
Any other ideas? I have some cells with Data Validation and some cells with
formatting applied. This is why I want to select a range and FillDown.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JLGWhiz" wrote:

Hi Ryan, I deleted the Type:=xlFillDefault and it worked fine.



"ryguy7272" wrote in message
...
I guess I'm not going about this the right way. I'm trying to find the
last
used row, based on Column Z, then fill-down, the contents in A and 1 row
above to Z and 1 row above. For instance, if Z17 has data in it, I want
to
copy down A16:Z16 into A17:Z17.

Here's the code I'm trying:
Dim dynarow As Long
dynarow = Worksheets("Worksheet").Cells(Rows.Count, "Z").End(xlUp).Row
Selection.AutoFill Destination:=Range("A" & dynarow & ":Z" & dynarow + 1),
Type:=xlFillDefault

I keep getting the following error: 'AutoFill method of range class failed'
I guess the range is not defined properly.

I'm completely open to suggestions as to how to do this.

Thanks!


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


.

xlFillDefault, based on values in ColA to ColZ
 

You're specifying the worksheet when you find the dynarow, but you're relying on
the current selection in the next line.

I'd use:

Dim dynarow As Long
with worksheets("Worksheet")
dynarow = .Cells(.Rows.Count, "Z").End(xlUp).Row
.cells(dynarow,"A").resize(1,26).AutoFill _
Destination:=.cells(dynarow,"A").resize(2,26), Type:=xlFillDefault
end with

If that doesn't help, I'd make sure that dynarow was what I thought it was
supposed to be by adding a
Msgbox dynarow
before the autofill line.



ryguy7272 wrote:

I guess Im not going about this the right way. Im trying to find the last
used row, based on Column Z, then fill-down, the contents in A and 1 row
above to Z and 1 row above. For instance, if Z17 has data in it, I want to
copy down A16:Z16 into A17:Z17.

Heres the code Im trying:
Dim dynarow As Long
dynarow = Worksheets("Worksheet").Cells(Rows.Count, "Z").End(xlUp).Row
Selection.AutoFill Destination:=Range("A" & dynarow & ":Z" & dynarow + 1),
Type:=xlFillDefault

I keep getting the following error: €˜AutoFill method of range class failed
I guess the range is not defined properly.

Im completely open to suggestions as to how to do this.

Thanks!



--

Dave Peterson

xlFillDefault, based on values in ColA to ColZ
 

Don't understand why you still got the message. I only changed the sheet
name to one of my sheets and deleted the xlFillType type because it
alutomatically uses default. it no other type is specefied. I listed a row
of data on row 16 and ran the macrow. It filled in row 17, A - Z. But, it
did show that the constant xlFillDefault is not recognized by that internal
class. There are a lot of those out there. Some programmer used the
constant in their write op of how it works, but omitted it from the class
when it was developed.


"ryguy7272" wrote in message
...
Thanks JLGWhiz. I tried that. Still doesn't work.
Any other ideas? I have some cells with Data Validation and some cells
with
formatting applied. This is why I want to select a range and FillDown.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JLGWhiz" wrote:

Hi Ryan, I deleted the Type:=xlFillDefault and it worked fine.



"ryguy7272" wrote in message
...
I guess I'm not going about this the right way. I'm trying to find the
last
used row, based on Column Z, then fill-down, the contents in A and 1
row
above to Z and 1 row above. For instance, if Z17 has data in it, I
want
to
copy down A16:Z16 into A17:Z17.

Here's the code I'm trying:
Dim dynarow As Long
dynarow = Worksheets("Worksheet").Cells(Rows.Count, "Z").End(xlUp).Row
Selection.AutoFill Destination:=Range("A" & dynarow & ":Z" & dynarow +
1),
Type:=xlFillDefault

I keep getting the following error: 'AutoFill method of range class
failed'
I guess the range is not defined properly.

I'm completely open to suggestions as to how to do this.

Thanks!


--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


.

xlFillDefault, based on values in ColA to ColZ
 

Thanks Dave! That did it!! I think the source and the destination have to
be the same size. I read something about this today; can't find the link
now. I tried all sorts of things but nothing worked until I tried your code
Dave!

Thanks again!!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Dave Peterson" wrote:

You're specifying the worksheet when you find the dynarow, but you're relying on
the current selection in the next line.

I'd use:

Dim dynarow As Long
with worksheets("Worksheet")
dynarow = .Cells(.Rows.Count, "Z").End(xlUp).Row
.cells(dynarow,"A").resize(1,26).AutoFill _
Destination:=.cells(dynarow,"A").resize(2,26), Type:=xlFillDefault
end with

If that doesn't help, I'd make sure that dynarow was what I thought it was
supposed to be by adding a
Msgbox dynarow
before the autofill line.



ryguy7272 wrote:

I guess Im not going about this the right way. Im trying to find the last
used row, based on Column Z, then fill-down, the contents in A and 1 row
above to Z and 1 row above. For instance, if Z17 has data in it, I want to
copy down A16:Z16 into A17:Z17.

Heres the code Im trying:
Dim dynarow As Long
dynarow = Worksheets("Worksheet").Cells(Rows.Count, "Z").End(xlUp).Row
Selection.AutoFill Destination:=Range("A" & dynarow & ":Z" & dynarow + 1),
Type:=xlFillDefault

I keep getting the following error: €˜AutoFill method of range class failed
I guess the range is not defined properly.

Im completely open to suggestions as to how to do this.

Thanks!



--

Dave Peterson
.

xlFillDefault, based on values in ColA to ColZ
 

The source and destination aren't the same in this code:

.cells(dynarow,"A").resize(1,26).AutoFill _
Destination:=.cells(dynarow,"A").resize(2,26), Type:=xlFillDefault

The .resize(1,26) says to start with 1 row by 26 columns.

The .resize(2,26) gives you 2 rows by 26 columns.

I still think it's got something to do with the worksheet or the selection.

If the correct range were selected, I think your original code would have worked
fine. But I'd do my best not to rely on that selection being correct.


ryguy7272 wrote:

Thanks Dave! That did it!! I think the source and the destination have to
be the same size. I read something about this today; can't find the link
now. I tried all sorts of things but nothing worked until I tried your code
Dave!

Thanks again!!
Ryan---



--

Dave Peterson