Copy a block of data to another worksheet

[jmmac]

I have a workbook for scheduling purposes. On sheet1 ("Staff") I compile availability. I'd like the macro to sort the data according to column H (availability), select and copy the block of data at the top (anything that has a value in column H), then transfer this info to another sheet ("Schedule"), and delete the transferred rows on sheet1. Everything is working except the copy portion of the code - it sorts properly, selects the proper information, then gets stuck on moving it to another sheet. I keep getting an error on line 28 [Sheets("Schedule").Range("A" & LastRow2).Select]. Below is the code I have in Module 1:

Sub Archive()

  Dim LastRow As Long
  Dim LastRow2 As Long
  Dim objRows As Range
  Dim sendObjTo As Range
  Application.ScreenUpdating = False
    
  Worksheets("Staff").Sort.SortFields.Clear
  Worksheets("Staff").Sort.SortFields.Add Key:=Range("H1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
  With ActiveWorkbook.Worksheets("Staff").Sort
    .SetRange Range("A1:I1500")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  
  LastRow = Range("H:H").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Set objRows = Sheets("Staff").Range("A2:I" & LastRow)
  objRows.Select
    Selection.Copy
    
  LastRow2 = Sheets("Schedule").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Sheets("Schedule").Range("A" & LastRow2).Select
  objRows.PasteSpecial (xlPasteAll)
  objRows.Delete
Application.ScreenUpdating = True
End Sub

 

[AlphaFrog]

I'm guessing the Sheets("Schedule") is not the Active sheet

You cannot select the range on the worksheet unless the worksheet is already selected.

Try this...

  LastRow = Range("H:H").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  [COLOR=darkblue]Set[/COLOR] objRows = Sheets("Staff").Range("A2:I" & LastRow)
  objRows.Select
    Selection.Copy
    
  LastRow2 = Sheets("Schedule").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[B]  Sheets("Schedule").Select
  Range("A" & LastRow2).Select[/B]
  Selection.PasteSpecial (xlPasteAll)
  objRows.Delete

Or better yet, no need to select anything to copy\paste if the sheets and ranges are fully qualified.

  LastRow = Sheets("Staff").Range("H:H").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  [COLOR=darkblue]With[/COLOR] Sheets("Staff").Range("A2:I" & LastRow)
    .Copy
    LastRow = Sheets("Schedule").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Schedule").Range("A" & LastRow + 1).PasteSpecial xlPasteAll
    
    .Delete
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

 

[jmmac]

This made it all work, thank you greatly for your help!