VBA More Efficient Fill Down

[Littlemalky]

Is there anyway to make this more efficient. This is the only way I could get this problem to work: I'm inserting a formula into a cell I can't call upon directly because it's dynamic, and then I'm selecting the cell, copying that cell, and telling it to paste down in the next 23 rows below it.
It seems redundant to me, but I can't figure out anything else.

Workbooks("" & Format(DateSerial(Year(Date), (Month(Date) - 1), Day(Date)), "yyyy.mm") & " summary.xlsx").Worksheets("Domestic").[A5].End(xlToRight).Offset(0, 1).Formula = _
        "=IFERROR(VLOOKUP(A:A,'[" & Format(DateSerial(Year(Date), (Month(Date) - 1), Day(Date)), "mm-yyyy") & " Domestic.xlsx]Summary'!$A:$D,4,0),0)"
    Workbooks("" & Format(DateSerial(Year(Date), (Month(Date) - 1), Day(Date)), "yyyy.mm") & " summary.xlsx").Worksheets("Domestic").[A5].End(xlToRight).Select
    Workbooks("" & Format(DateSerial(Year(Date), (Month(Date) - 1), Day(Date)), "yyyy.mm") & " summary.xlsx").Worksheets("Domestic").[A5].End(xlToRight).Copy _
        Destination:=Workbooks("" & Format(DateSerial(Year(Date), (Month(Date) - 1), Day(Date)), "yyyy.mm") & " summary.xlsx").Worksheets("Domestic").Range(Selection, Selection.Cells(23))

 

[Jonmo1]

You can put the formula into all the cells in one line...

change
Offset(0, 1).Formula = "...
to
Offset(0, 1).Resize(23,1).Formula = "...

Then you don't need to copy/paste the formula to the rest of the rows.


Also, another simplification is to use the DateAdd Function...

This
DateSerial(Year(Date), (Month(Date) - 1), Day(Date))
Can be written as
DateAdd("m", -1, Date)


Hope that helps.

 

[Littlemalky]

Wow, you are a lifesaver. Those tips were amazing. Thank you so much.

 

[Jonmo1]

Glad to help, thanks for the feedback.