Excel 2010 VBA - runtime error 1004 - using match to copy range from one sheet to another

i can't see where i've gone wrong

Dim Sheet1RowNumber开发者_如何转开发 As Variant, Sheet28RowNumber As Variant, sStaffName As String
sStaffName = Worksheets(1).Cells(7, 1).Value
Sheet28RowNumber = Application.Match(sStaffName, Worksheets(28).Range("a:a"), 0)
Sheet1RowNumber = Application.Match(sStaffName, Worksheets(1).Range("a:a"), 0)
Worksheets(1).Range(Cells(Sheet1RowNumber, 2), Cells(Sheet1RowNumber, 8)) =        Worksheets(28).Range(Cells(Sheet28RowNumber, 2), Cells(Sheet28RowNumber, 8))

VBA doesn't like it when your refer to multi-cell ranges from a sheet that isn't currently selected/activated. (Oddly, this is not a problem for single-cell ranges...) Anyhow, that's what's causing your error in this case. This statement refers to multi-cell ranges on two different sheets:

Worksheets(1).Range(Cells(Sheet1RowNumber, 2), Cells(Sheet1RowNumber, 8)) = _ 
    Worksheets(28).Range(Cells(Sheet28RowNumber, 2), Cells(Sheet28RowNumber, 8))

You can split it up into two lines by storing the range to be copied in a buffer (temp below). Before each statement, activate the appropriate sheet. This works:

Dim Sheet1RowNumber As Variant, Sheet28RowNumber As Variant, sStaffName As String
Dim temp As Variant

sStaffName = Worksheets(1).Cells(7, 1).Value
Sheet28RowNumber = Application.Match(sStaffName, Worksheets(2).Range("a:a"), 0)
Sheet1RowNumber = Application.Match(sStaffName, Worksheets(1).Range("a:a"), 0)

Worksheets(2).Activate
temp = Worksheets(2).Range(Cells(Sheet28RowNumber, 2), Cells(Sheet28RowNumber, 8))
Worksheets(1).Activate
Worksheets(1).Range(Cells(Sheet1RowNumber, 2), Cells(Sheet1RowNumber, 8)) = temp