xlFillDefault, based on values in ColA to ColZ

[ryguy7272]

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!

 

[ryguy7272]

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.

 

[Dave Peterson]

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.

 

[JLGWhiz]

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]

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]

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.