Autofill macro - variable destination cells

[Kell2604]

Hi guys...

I have a macro that needs to:
1) Copy static range of formulas
2) navigate to first empty cell in row/column
3) Paste data in that empty cell
4) Autofill to end of data as determined by prior column.

Everything works great until I get to the autofill part. I think I must be
missing something obvious but I'm just STUCK!! Any thoughts you have would be
GREATLY appreciated. I have hunted around the message boards but not seeing
what I need. I can make this work if I am pasting the formulas into a static
range and then autofilling. The problem seems to be that my destination
range is different everytime. And so the macro needs to find the empty cell
(which I have working) and then autofill from there.

Range("AH2:AM2").Select
Selection.Copy
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
lRow = Cells(Rows.Count, "AG").End(xlUp).Row
Range("AH:AM")AutoFill Destination:=Range("AH:AM" & lRow)

 

[Mike H]

Hi,

I'm not sure I understand correctly but does this help

Range("AH2:AM2").Copy Destination:=Range("A" & Cells(Rows.Count,
"A").End(xlUp).Row + 1)
lrow = Cells(Rows.Count, "AG").End(xlUp).Row
Range("AH2:AM2").AutoFill Destination:=Range("AH2" & ":AM" & lrow)


Mike

 

[Kell2604]

Well...this half works. It's still autofilling from AH2 and I need it to
autofill after it navigates to the first empty cell as determined by column
AG). It could be AH5 one time and AH28 the next. The end row of data
changes daily.

 

[Dave Peterson]

Maybe....

Dim NextRow As Long
Dim LastRow As Long

With Worksheets("Sheetnamehere")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row

.Range("ah2:am2").Copy _
Destination:=.Cells(NextRow, "AH")

'AH to AM is 6 columns
.Cells(NextRow, "AH").Resize(1, 6).AutoFill _
Destination:=.Range(.Cells(NextRow, "AH"), _
.Cells(LastRow, "AM"))
End With

But you can copy the formulas into the receiving range in your paste and drop
the autofill:

Dim NextRow As Long
Dim LastRow As Long

With Worksheets("Sheetnamehere")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
LastRow = .Cells(.Rows.Count, "AG").End(xlUp).Row

.Range("ah2:am2").Copy _
Destination:=.Cells(NextRow, "AH").Resize(LastRow - NextRow + 1)

End With




Are you really using columns A and AG to determine the next and last row?

 

[Kell2604]

No...

My formulas are in AH2 - AM2
I use column AG only to determine my last row.

I'll try your suggestions - thanks so much!!

 

[Dave Peterson]

If you're using this code:

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

Then you're looking at column A.

 

[Kell2604]

SORRY - looks like I may have grabbed the wrong code...

Range("AH2:AM2").Select
Selection.Copy
Cells(Rows.Count, 1).End(xlUp).Offset(1, 33).Select
ActiveSheet.Paste
** Now it should autofill from here to the last occupied row as determined
by column AG.

 

[Dave Peterson]

You're still using column A to determine that next row. But you're pasting in
that cell that's one row down and 33 to the right.

SORRY - looks like I may have grabbed the wrong code...

Range("AH2:AM2").Select
Selection.Copy
Cells(Rows.Count, 1).End(xlUp).Offset(1, 33).Select
ActiveSheet.Paste
** Now it should autofill from here to the last occupied row as determined
by column AG.