AutoFill question

[Guest]

I'm trying to autofill to the cells to the right to keep the dates going when
entering new data with a macro every month. So without a macro I would
normally select the two cells (e.g. A1 and B1) with the previous two month
headers in them and autofill to the right so that it gives me the next month.
How can I do this in a macro when the cells are changing every time? I can
get to the point of where the two cells are highlighted but need to know how
to autofill. I thought that it would be something like this:

Selection.AutoFill Destination:=Selection.Offset(0, 1), Type:=xlFillDefault

 

[Bernie Deitrick]

This is how to do it if you have selected the 2 cells:

Sub Autofill1()
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))
End Sub


And this is how to do it if you don't want to select the two cells (assumes that column A is
otherwise blank)

Sub Autofill2()
Dim myR As Range
Set myR = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Resize(2, 1)

On Error Resume Next
myR.AutoFill Destination:=Range _
(myR, myR.Offset(0, 1).End(xlDown).Offset(0, -1))

End Sub


HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi Bernie,

Sorry for the confusion. I wanted to autofill to the right. So effectively
just right fill. But only one cell and not right until the end. It's
basically something that needs done once a month. So if I have Jan in cell
A1 and Feb in cell B1 then the next time the macro is run I want it to put
Mar in the next cell. But I need it to right fill taking the lead from the
previous cells rather than just have script that puts dates that you have
requested.

Thanks

Carl

 

[Bernie Deitrick]

Carl,

I'm sorry, I was confused... I still haven't had my coffee....

Sub AF1()
Selection.AutoFill Destination:=Range _
(Selection, Selection.Resize(1, 3))
End Sub

OR

Sub AF2()
Dim myR As Range
Set myR = Cells(1, Columns.Count). _
End(xlToLeft).Offset(0, -1).Resize(1, 2)

On Error Resume Next
myR.AutoFill Destination:=Range _
(myR, myR.Resize(1, 3))

End Sub


will work.

Bernie
MS Excel MVP

 

[Guest]

Perfect! Thanks!

Carl,

I'm sorry, I was confused... I still haven't had my coffee....

Sub AF1()
Selection.AutoFill Destination:=Range _
(Selection, Selection.Resize(1, 3))
End Sub

OR

Sub AF2()
Dim myR As Range
Set myR = Cells(1, Columns.Count). _
End(xlToLeft).Offset(0, -1).Resize(1, 2)

On Error Resume Next
myR.AutoFill Destination:=Range _
(myR, myR.Resize(1, 3))

End Sub


will work.

Bernie
MS Excel MVP