Copy Rows from other Sheet

[adelspark]

Hello All,

I thought I'd be smart and create a quick recorded macro to select/copy some rows in sheet 'A' and then paste them into sheet 'B' and sort. It wasn't so quick and easy, because it fails at the Rows("6:220").Select line when it runs. Strange, because that's the way it recorded it (see below).

Any thoughts?

Thanks in Advance


Private Sub Worksheet_Activate()
'
Sheets("Grouped Register").Select
Rows("6:220").Select
Selection.Copy
Sheets("Numerical Register").Select
Range("A6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Numerical Register").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Numerical Register").Sort.SortFields.Add Key:= _
Range("C6:C220"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Numerical Register").Sort
.SetRange Range("B6:N220")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A6").Select
End Sub

 

[machopicho]

Try:

Private Sub Worksheet_Activate()
'
Sheets("Grouped Register").Rows("6:220").Copy Destination:=Sheets("Numerical Register").Range("A6")
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Numerical Register").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Numerical Register").Sort.SortFields.Add Key:= _
Range("C6:C220"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Numerical Register").Sort
.SetRange Range("B6:N220")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A6").Select
End Sub

 

[adelspark]

A big thank you from Australia, Machopicho; you even re-wrote the entire code for me and it works beautifully! I see where I went wrong now.

By the way, that thumbnail photo of me doing 'via ferrata' was taken two months ago only about 100km from Varese. Even more bizarre is that next year we are travelling to Machu Picchu. It's a small but surprising world we live in.

Thanks again

 

[machopicho]

Nothing!