Autofill macro - variable destination cells
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" wrote:
If you're using this code:
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Then you're looking at column A.
Kell2604 wrote:
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" wrote:
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 wrote:
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)
--
Dave Peterson
--
Dave Peterson
|