Create a Excel Macro to copy formulas down across variable/unknown number of columns?

Question

I would like to create a single macro in excel that I can reuse on other sheets to copy the formulas down from from B4:B? down to Row number X.

I will be pasting various rows of data into Column A of several different worksheets. I will have a button on each sheet that I'd like to label "Copy Formulas". I have written formulas to parse out the text into anywhere from 3 to 250 columns, depending on the sheet. I would like the macro to highlight from B4 to Selection.End(xlToRight) then copy that selection down until the last row of data in column A.

I was thinking of something like this but I keep getting an error on line 6.

    Dim strCurrentSheet As String
    Dim LastRow As Integer
    Dim LastCol As Integer
    LastRow = Range("A4").End(xlDown).Row
    LastCol = Range("B4").End(xlToRight).Column
    Range(Cells(4, 2), Cells(4, LastCol)).AutoFill _ 
    Destination:=Range(Cells(5, 2), Cells(LastRow, LastColumn))

Solution

Instead of a VBA macro to do so, consider using Excel tables (Insert->Table) from the start - it has this functionality more or less build in:

If you enter a formula an empty column in a table (or an adjacent column next to it), Excel will automatically apply the formula to all rows. And if you add/copy data at any point, it will automatically expand the table and therefore also apply the formulas in all columns!

Comments

[harvey onethousand]

Not sure about the Copy Cells part - Are the 2,4 examples you gave in the code transposed? I have updated my question above with some code and I am getting an error on the last line.