Autofill Columns In Row1 According To Last Filled Column In Row2

[GauciG]

Hi

I am looking for a macro that copies the formula in cell A1 and autofills row1 till the last filled column in row2.

Sub AutofillColumnsInRow1AccordingToLastFilledColumnInRow2()
Dim lastColumn As Long
lastColumn = Range(Columns.Count & 2).End(xlToLeft).Column
Range("A1").AutoFill Destination:=Range("A1:" & lastColumn & "1")
End Sub

Any suggestions
Kind regards
George

 

[Tim_Excel_]

Sub AutofillColumnsInRow1AccordingToLastFilledColumnInRow2()
    Dim lastColumn As Long
    LastCol = Range(Columns.Count & 2).End(xlToLeft).Column

Range("A1").Copy
Range(Cells(1, 1), Cells(1, LastCol)).Select).PasteSpecial xlPasteFormulas

End Sub

Hope this helps

 

[GauciG]

Sub AutofillColumnsInRow1AccordingToLastFilledColumnInRow2()
    Dim lastColumn As Long
    LastCol = Range(Columns.Count & 2).End(xlToLeft).Column

Range("A1").Copy
Range(Cells(1, 1), Cells(1, LastCol)).Select).PasteSpecial xlPasteFormulas

End Sub

Hope this helps

Thanks for reply Tim

I made some changes as follows:
Dim lastColumn - Dim lastCol
I removed the bracket after 'Select' in the last row of the code as I was getting an error

'New' code as follows

Sub AutofillColumnsInRow1AccordingToLastFilledColumnInRow22()
Dim lastCol As Long
lastCol = Range(Columns.Count & 2).End(xlToLeft).Column

Range("A1").Copy
Range(Cells(1, 1), Cells(1, lastCol)).Select.PasteSpecial xlPasteFormulas

End Sub

I am getting an error in line 2 of the code:
Run-time error '1004':
Method 'Range' of object'_Global' failed

Thanks in advance
George

 

[Peter_SSs]

You could also try this.

Sub Fill_Right()
  Range("A1", Cells(2, Columns.Count).End(xlToLeft).Offset(-1)).FillRight
End Sub

 

[Peter_SSs]

lastCol = Range(Columns.Count & 2).End(xlToLeft).Column


I am getting an error ..

That line would need to be

LastCol = Cells(2, Columns.Count).End(xlToLeft).Column

 

[Tim_Excel_]

Sub AutofillColumnsInRow1AccordingToLastFilledColumnInRow22()
Dim lastCol As Long
With ThisWorkbook.Worksheets(5) 'refer to the correct sheet
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range("A1").Copy
.Range(.Cells(1, 1), .Cells(1, lastCol)).PasteSpecial xlPasteFormulas
End With
End Sub

Tested and works

 

[Peter_SSs]

Tested and works

It shouldn't, since you are calculating lastCol from row 1, not row 2.

 

[GauciG]

Thanks a lot Peter. A one liner code that works like magic

Kind regards
George

 

[Peter_SSs]

Thanks a lot Peter. A one liner code that works like magic

Kind regards
George

You are very welcome.

 

[GauciG]

Thank you

Amended active sheet and row number as follows:

Sub AutofillColumnsInRow1AccordingToLastFilledColumnInRow22()
Dim lastCol As Long
With ActiveSheet 'refer to the correct sheet
lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
.Range("A1").Copy
.Range(.Cells(1, 1), .Cells(1, lastCol)).PasteSpecial xlPasteFormulas
End With
End Sub