Copy-Paste crashes VBA

Question

I have the following code:

Private Sub Update_To_Search_Click()
'add the user id and date in the lock and date columns
Dim r As Range
Dim wb As Workbook
 Set wb = Workbooks("GOOD")
 Set r = ActiveCell
 For i = 1 To Rows.count
     Set r = r.Offset(1, 0)
     If r.EntireRow.Hidden = False Then
         r.Select
         GoTo Continue
     End If
 Next

Continue:
ActiveCell.Offset(0, 67).Select
If ActiveCell.Value = "" Then
    ActiveCell.Value = UCase(Environ("UserName"))
    ActiveCell.Offset(0, 1).Value = Now
    ActiveCell.EntireRow.Select
    Selection.Copy
    wb.Activate
    Sheets("GoodDBData").Select
    Range("A2").Select
    ActiveSheet.Paste

Else
    ActiveCell.EntireRow.Select
    Selection.Copy
    wb.Activate
    Sheets("GoodDBData").Select
    Range("A2").Select
    ActiveSheet.Paste
End If
End sub

When run, it keeps spinning [not responding]. I have been using the Paste coding many times and it never happened before.

Any idea why? Thank you

"Crashes" and "doesn't work" are poor problem descriptions that don't really help make a good, clear question. Feel free to [edit] your post to include the specific error you're getting, and what specific statement is causing it. — Mathieu Guindon, Aug 08 '18 at 18:49

score 2 · Answer 1 · answered Aug 08 '18 at 18:34

Avoid using .Select/.Activate. Also, using GoTo is generally frowned upon.

Also, you most likely don't want to actually loop through every single row in Excel. This can cause it to hang up/error out.

This code should work, I think I kept it as you were intending:

Private Sub Update_To_Search_Click()
'add the user id and date in the lock and date columns
Dim r       As Range
Dim wb      As Workbook
Set wb = Workbooks("GOOD")
Set r = wb.Worksheets("Sheet1").Range("A1")       ' CHANGE THIS WORKSHEET to the correct name, and update the starting cell!!!!!!!
For i = 1 To wb.Worksheets("Sheet1").Cells(rows.count,1).End(xlUp).Row ' Change this to the column with the most data
    Set r = r.Offset(1, 0)
    If r.EntireRow.Hidden = False Then
        If r.Offset(0, 67).Value = "" Then
            r.Offset(0, 67).Value = UCase(Environ("UserName"))
            r.Offset(0, 67).Offset(0, 1).Value = Now
            r.Offset(0, 67).EntireRow.Copy wb.Sheets("GoodDBData").Range("A2").Paste
        Else
            r.EntireRow.Copy
            wb.Sheets("GoodDBData").Range("A2").Paste
        End If
    End If
Next
Application.CutCopyMode = False
End Sub

I was looping to find the unhidden row. I'm initially doing a filter based on some criteria and then I want to take the row that is filtered (hence the unhidden row) and copy-paste it in the wb workbook. — Val S, Aug 08 '18 at 18:49
I get a Run Time error 92 For loop not initialized? I still very new to VBA, would you have any idea what this error is about? — Val S, Aug 08 '18 at 19:11
@ValS - Hmm - what line throws the error? You may need to add `Dim i as Long` if you have `Option Explicit` on (which you should). I tried on my comp and I can begin the loop. Check that the worksheet names and workbook references are correct. — BruceWayne, Aug 08 '18 at 19:22
this is actually part of a userform button command. Should I add Option Explicit at the beginning of the Userform? — Val S, Aug 08 '18 at 19:25

Copy-Paste crashes VBA

1 Answers1