xlFillDefault, based on values in ColA to ColZ
in [Programming]
|
From: ryguy7272 on 10 May 2010 20:02 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 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! > > > > > > -- > > Dave Peterson > . >
From: Dave Peterson on 10 May 2010 21:27
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 |