Copy-Paste crashes VBA
Clash Royale CLAN TAG#URR8PPP
Copy-Paste crashes VBA
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
1 Answer
1
Avoid using .Select/.Activate. Also, using GoTo is generally frowned upon.
.Select
.Activate
GoTo
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 8 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 8 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 8 at 19:22
Dim i as Long
Option Explicit
this is actually part of a userform button command. Should I add Option Explicit at the beginning of the Userform?
– Val S
Aug 8 at 19:25
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
"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 8 at 18:49