VBA Fill Right Formula for Selected Range to Last Column

[Finalfight40]

Hi All

I have currently been working on a Macro but i'm having trouble with the section i have highlighted below. The section i'm struggling with has been copy and pasted and adapted from a previous Mr Excel thread but it was from 2012. (Please see link below)

https://www.mrexcel.com/forum/excel-questions/614828-vba-how-autofill-left-right.html

Currently it will copy fill from cell B3 to the right but i would like it to take into account the small line of code below and fill the selected area to the right.

Range("B3", Range("B" & Rows.Count).End(xlUp)).Select

Any help or advice here is appreciated.

Sub SheetNames()


    Sheets("Overtime Calculator Template").Select
    Application.CutCopyMode = False
    Sheets("Overtime Calculator Template").Copy After:=Sheets(13)
    Sheets("Overtime Calculator Templat (2)").Select
    Sheets("Overtime Calculator Templat (2)").Name = "Overtime Calculator"
    Range("C4").Select
Sheets("Overtime Calculator").Select
For i = 1 To Sheets.Count - 2
    Cells(2, i + 1) = Sheets(i).Name
Next i
    Range("A3").Select
    Sheets(1).Select
    Range("A3", Range("A" & Rows.Count).End(xlUp)).Copy
    Sheets("Overtime Calculator").Select
    ActiveSheet.Paste
    Range("B3").Select
' From https://excelsemipro.com/2011/09/fill-down-a-formula-with-vba/
    Dim rng As Range
    Dim rngData As Range
    Dim rngFormula As Range
    Dim rowData As Long
    Dim colData As Long
' Set the ranges
    Set rng = ActiveCell
    Set rngData = rng.CurrentRegion
' Set the row and column variables
    rowData = rngData.CurrentRegion.Rows.Count
    colData = rng.Column
' Set the formula range and fill down the formula
    Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
    rngFormula.FillDown
[B]    Range("B3", Range("B" & Rows.Count).End(xlUp)).Select[/B]
[B]    Dim lastcolumn As Long[/B]
[B]    lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column[/B]
[B]    Range("B3").AutoFill Destination:=Range(Cells(3, 2), Cells(3, lastcolumn))[/B]


    End Sub

 

[Fluff]

How about

Dim lastcolumn As Long
Dim lastRow As Long
   lastRow = Range("B" & Rows.Count).End(xlUp).Row
    lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column
   Range("B3:B" & lastRow).AutoFill Range("B3", Cells(lastRow, lastcolumn))

 

[Finalfight40]

How about

Dim lastcolumn As Long
Dim lastRow As Long
   lastRow = Range("B" & Rows.Count).End(xlUp).Row
    lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column
   Range("B3:B" & lastRow).AutoFill Range("B3", Cells(lastRow, lastcolumn))

Fluff, you sir are a legend.

Thank you for this, i've been trying to play around with the previous code to try to get it to work for a couple of hours.

Works perfectly.

 

[Fluff]

Glad to help & thanks for the feedback