Copy formulas in B to all columns with header

[HURTMYPONY]

I've made a macro that copies formulas down to the last used row, but never across columns!

I have a macro that fills a dynamic number of salespeople names as column headers.

I would like to copy the formulas in Column B and paste them across all other columns that have a column header(i.e. salesperson's name).

The number of rows is fixed (about 40 rows) and the headers start on Row 4 in Column B. They are written so they retain their proper references when "dragged" across manually, but I want them to populate horizontally automatically to any column that has a header in Row 4!

This must be simple, but I cannot figure it out.

 

[HURTMYPONY]

This is what works for me when finding the Last Row and pasting across the columns (on a different sheet in a different project).

LR2 = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row
Sheets("sheet2").Range("G2:AA2").Copy Sheets("sheet2").Range("G3:AA" & LR2)

That fills in all the formulas in G2:AA2 all the way down to the last populated row in A very nicely.

I just don't know the syntax to make it do the opposite: count the columns and paste the formulas in B across all columns dynamically populated by a header in Row 4.

I tried changing it to this, but no luck:

LC = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
LastColumn = LC
Sheets("Sheet2").Range("B4:B18").Copy Sheets("Sheet2").Range("B4:B18" & LC)

THIS DOES ABSOLUTELY NOTHING. Sorry for the crappy code, I am not familiar with the language and "CookBook" pieces together ignorantly until I have a solution.

 

[VoG]

Try like this

Sub test()
Dim LC As Long
With Sheets("Sheet2")
    LC = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range("B8:B40").Copy Destination:=.Range(.Cells(8, 3), .Cells(8, LC))
End With
End Sub

 

[HURTMYPONY]

Try like this

Sub test()
Dim LC As Long
With Sheets("Sheet2")
    LC = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range("B8:B40").Copy Destination:=.Range(.Cells(8, 3), .Cells(8, LC))
End With
End Sub

Woot! Thank you, thank you! It didn't work out of the box since the ranges were different, but I deciphered your syntax and came up with this, which seems to be working:

Sub test()
Dim LC As Long
With Sheets("Sheet2")
LC = .Cells(4, Columns.Count).End(xlToLeft).Column
.Range("B5:B40").Copy Destination:=.Range(.Cells(5, 3), .Cells(5, LC))
End With
End Sub

Since my my headers actually start on row 4 and my copy range actually was b5:b40, the slight modification to your code seems to do the trick. Thank you so much!

I will test it some more, but this looks perfect.