Help Creating Macro for Selection, copying ranges

[Baiano42]

Hey guys and gals,

I'm trying to create a macro that will do the following: select a range from B46 to E46, do the Ctrl+Shft+Down function, copy that selection, and paste it in a new work sheet. Here is how far I've gotten so far:

Sub DynamicRange()
'Best used when first column has value on last row and first row has a value in the last column
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
'Find Last Row and Column
LastRow = Cells(Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = Selection.Resize(Selection.Rows.Count + 5, _
Selection.Columns.Count).Select
'Select Range
Range(Range("B46"), Cells(LastRow, LastColumn)).Select


Any help with getting it to be completed would be amazing! Thanks all.

Cheers,

Baiano42

[Baiano42]

I think I got it to work :

Range("B46:E46").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "B"
Range("C1").Select
ActiveCell.FormulaR1C1 = "C"
ActiveSheet.Name = "Given Name"
End Sub

[mana]

Sub test()
    Dim StartCell As Range

    Set StartCell = Range("B46:E46")
    
    With Worksheets.Add
        Range(StartCell, StartCell.End(xlDown)).Copy .Range("A1")
        .Rows(2).Delete
        .Range("b1:c1").Value = Array("B", "C")
        .Name = "Given Name"
    End With

End Sub