Autofill macro - variable destination cells

K

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)
 
M

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
 
K

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.
 
D

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?
 
K

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!!
 
D

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.
 
K

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.
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top