Autofill Multiple Columns - VBA

[Bobilly]

[MENTION]Hi newbie to VBA here,

I am trying to create a macro that takes a formula created in Cell G3 drags it to M3, and then auto fills to the bottom of the last row. The way I was working on it was previously recording and making some adjustments, I cannot find a way to have it auto fill to the last row of the data. Data set rows change frequently, this is what I have so far. (Note you can ignore the G2 portion - that is for a separate autofill part of the Macro)

Range("G2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-6]"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:M2"), Type:=xlFillDefault
Range("G2:M2").Select
Range("G3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]="""",R[-1]C,RC[-6])"
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:M3"), Type:=xlFillDefault
Range("G3:M3").Select
Selection.AutoFill Destination:=Range("G3:M67")
Range("G3:M67").Select
Range("A1:V67").Select
Range("G3").Activate

Thank you for any help![/MENTION]

 

[James006]

Hi,

You could test following

Sub CopyRng()
  Range("G3").FormulaR1C1 = "=IF(RC[-6]="""",R[-1]C,RC[-6])"
  Range("G3").Copy Destination:=Range("G3:M3")
  Range("G3:M3").Copy Destination:=Range("G4:G67")
End Sub

Hope this will help

 

[Bobilly]

Hi James,

Thanks for following up, the test did pull through but my problem is that I would like autofill destination to copy to the last row of the data. (ex: this set of data goes to row 2870 not just 67) and it varies from data pull. Thanks for any input!

 

[Fluff]

How about

Sub Bobbilly()
   Range("G2:M2").FormulaR1C1 = "=RC[-6]"
   Range("G3", Range("A" & Rows.Count).End(xlUp).Offset(, 12)).FormulaR1C1 = "=IF(RC[-6]="""",R[-1]C,RC[-6])"
End Sub

This uses column A to determine the last row, but than can be changed if needed.

 

[Bobilly]

Fluff, thanks for providing that, it helps clean things up. What would be the portion of VBA text needed to autofill the contents in (G3:M3) Down to the last row?

Thank you both for your help!

 

[Fluff]

It already does that based on col A

 

[Bobilly]

Fluff,

When I run that I get the functions filling from the added 6 rows G to L, but it is throwing in a function in the already set data in column M as well as adding functions to S:X.

It isn't copying and dropping down the data to the last row in columns G:L (the new 6 rows created.)

Current State in VBA.

Sub DataCleanUp()
'Unmerge and unwrap text & create 6 columns to the right of F
ActiveSheet.Cells.UnMerge
ActiveSheet.Cells.WrapText = False
Columns("G:L").Select
Selection.insert Shift:=xlToRight

Range("G3").FormulaR1C1 = "=IF(RC[-6]="""",R[-1]C,RC[-6])"
Range("G2:L2").FormulaR1C1 = "=RC[-6]"
Range("G3", Range("A" & Rows.Count).End(xlUp).Offset(, 12)).FormulaR1C1 = "=IF(RC[-6]="""",R[-1]C,RC[-6])"
Range("G3").Copy Destination:=Range("G3:L3")
Range("G3:L3").Copy Destination:=Range("G" & Rows.Count).End(xlUp).Offset(, 12)

End Sub

 

[Fluff]

It needs to be

Sub DataCleanUp()
'Unmerge and unwrap text & create 6 columns to the right of F
ActiveSheet.Cells.UnMerge
ActiveSheet.Cells.WrapText = False
Columns("G:L").Select
Selection.insert Shift:=xlToRight
    

    Range("G2:L2").FormulaR1C1 = "=RC[-6]"
    Range("G3", Range("A" & Rows.Count).End(xlUp).Offset(, 11)).FormulaR1C1 = "=IF(RC[-6]="""",R[-1]C,RC[-6])"

    
End Sub

 

[Bobilly]

Perfect, thank you so much. I really appreciate your help, learned a lot from it. Hope you have a great day

 

[Fluff]

You're welcome & thanks for the feedback