PDA

View Full Version : [SOLVED] VB, Excel 2007, Issue with autofill & copy



weenie
07-09-2017, 02:28 PM
Range("D2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.AutoFill Destination:=Range("D2:D6"), Type:=xlFillSeries
Range("D2:D6").Select
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Selection.Copy Destination:=Range("D7:D" & lastrow)

I'm want to:


autofill D2 to D6 numbers 1-5 then
Copy cells D2-D6 and copy down to last row
Using Col B to find the last row


From above code it copies D2-D6 (#1-5) to next 5 cells, D7-D11 only.

How do I copy to last row?

Thanks,
weenie

weenie
07-09-2017, 02:30 PM
on my screen shows blue emojis in between D2. Not sure why but here it is D2 to D6 then "D7:D"

mana
07-10-2017, 05:33 AM
Option Explicit

Sub test()
Dim lastrow As Long
Dim i As Long
Dim n As Long

lastrow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To lastrow
Cells(i, "d").Value = n Mod 5 + 1
n = n + 1
Next

End Sub

weenie
07-11-2017, 05:29 PM
Thank you! Appreciate your help. It worked beautifully

weenie

mdmackillop
07-12-2017, 04:10 AM
Hi Weenie
For clarification.
Your code will work if LastRow cell value will be 5, e.g Row 26.
Try to avoid Selecting cells. This will "overfill" the range to complete the sequence

Sub Test()
Dim c As Range
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Set c = Range("D2")
c = 1
c.AutoFill Destination:=c.Resize(5), Type:=xlFillSeries
For i = 5 To LastRow Step 5
c.Resize(5).Copy c.Offset(i)
Next
End Sub

weenie
07-14-2017, 11:56 AM
Thank you.

weenie