Excel Crash When Copying row through VBA

Question

Newbie here on Excel VBA. I would just like to ask how to fix the error I'm getting? I'm trying to copy an entire row base on a criteria, and paste it on another sheet (same workbook). Here's my code:

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim endRow As Range

Set ws = Sheets("FIELD OFFICE DATABASE")
Set ws1 = Sheets("Transferred Items")

set endRow = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

ws.Activate
ws.Unprotect "321321"
Range("B2").Select

Do Until endRow
    If ActiveCell.Value = Me.cmbemn.Text Then
        ActiveCell.Rows("B:S").Select
        Selection.copy
        ws1.Activate
        ws1.Unprotect "321321"
        endRow.Select
        ActiveSheet.Paste
    Else
        ActiveCell.Offset(1, 0).Select
    End If
Loop

What happens is its either my Excel crashes, or an error pops up saying "Object required". But mostly it crashes.

Please help! Thanks!

Answer 1

Unprotecting your worksheet cancels the copy/paste operation.

Protect your worksheets with the following parameter,

UserInterfaceOnly:=true
'example:
ws1.Protect password:="321321", UserInterfaceOnly:=true

Now you can do anything you want to do to them in VBA without unprotecting them

Answer 2

Try changing the loop termination condition to something like:

Do Until ActiveCell.Address = endRow.Address

I think you could terminate earlier with:

Set endRow = ws.Range("B" & Rows.Count).End(xlUp)

You also should be explicit in which worksheet you are working with or else the currently activesheet will be used. I have assumed you are working with ws.

And Rows should not specify columns within it:

ActiveCell.Rows("B:S").Select

It should have a Rows specification. Potentially you could do something like:

ActiveCell.EntireRow.Copy

Or as per later comment,

currCell.EntireRow.Copy

There doesn't need to be a .Select then a second line for the copy. You can do in one line.

Note also @Jeeped's comment on Unprotect.

My preference would be to specify a loopRange e.g.

Set loopRange = ws.Range("B2:B" & ws.Range("B" & Rows.Count).End(xlUp).Row)

and then loop over that range.

Dim currCell As Range

For Each currCell in loopRange

    If currCell.Value .....do stuff

Next currCell

This has the added benefit of getting rid of the use of activecell.