PDA

View Full Version : Help Creating Macro for Selection, copying ranges



Baiano42
06-28-2019, 07:38 AM
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
06-28-2019, 12:09 PM
I think I got it to work :thumb:

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
06-28-2019, 06:13 PM
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