Trying to update PlayerDB worksheet from the Scores worksheet and get this runtime error

Question

Here is my Macro

Sub Update()

'

' Update Macro

'

'

Dim DBRow As Integer

Sheets("Scores").Select

dateplay = Range("X1").Value

Numplaying = Cells(3, 31).Value

Application.ScreenUpdating = False


    

For i = 1 To Numplaying

Sheets("Scores").Select

DBRow = Cells(i + 2, 32).Value


      

' update scores in db

Sheets("Player DB").Select

Range(Cells(DBRow, 8), Cells(DBRow, 26)).Select

Selection.Copy

Range(Cells(DBRow, 9), Cells(DBRow, 27)).Select

ActiveSheet.Paste

Sheets("Scores").Select

Range("Z" & i + 2).Select

Selection.Copy

Sheets("Player DB").Select

Cells(DBRow, 8).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

    :=False, Transpose:=False


    

' update course/tee in db

Range(Cells(DBRow, 30), Cells(DBRow, 48)).Select

Application.CutCopyMode = False

Selection.Copy

Range(Cells(DBRow, 31), Cells(DBRow, 49)).Select

ActiveSheet.Paste

Sheets("Scores").Select

Range("AD" & i + 2).Select

Selection.Copy

Sheets("Player DB").Select

Cells(DBRow, 30).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

    :=False, Transpose:=False



'enter date of score db

Range(Cells(DBRow, 51), Cells(DBRow, 69)).Select

Application.CutCopyMode = False

Selection.Copy

Range(Cells(DBRow, 52), Cells(DBRow, 70)).Select

ActiveSheet.Paste

Cells(DBRow, 51).Value = dateplay



Next i

Sheets("Scores").Select

Cells(1, 21).Value = Date



End Sub

Answer 1

Hi, Jack Ledom

The error occurs at ActiveSheet.Paste because the macro repeatedly selects worksheets and relies on Excel’s clipboard. The screenshot confirms that the worksheet Paste method is failing, and this method depends on a valid current selection unless a destination is supplied.

Here are some suggestions you can try:

Replace the loop with direct, fully qualified range operations, which avoids Select, ActiveSheet, and clipboard-based pasting:

Option Explicit
Sub Update()
    Dim wsS As Worksheet, wsDB As Worksheet
    Dim i As Long, NumPlaying As Long, DBRow As Long
    Dim datePlay As Variant
    Set wsS = ThisWorkbook.Worksheets("Scores")
    Set wsDB = ThisWorkbook.Worksheets("Player DB")
    datePlay = wsS.Range("X1").Value
    NumPlaying = wsS.Cells(3, 31).Value
    Application.ScreenUpdating = False
    For i = 1 To NumPlaying
        DBRow = CLng(wsS.Cells(i + 2, 32).Value)
        If DBRow > 0 Then
            wsDB.Range(wsDB.Cells(DBRow, 8), wsDB.Cells(DBRow, 26)).Copy _
                Destination:=wsDB.Cells(DBRow, 9)
            wsDB.Cells(DBRow, 8).Value = wsS.Range("Z" & i + 2).Value
            wsDB.Range(wsDB.Cells(DBRow, 30), wsDB.Cells(DBRow, 48)).Copy _
                Destination:=wsDB.Cells(DBRow, 31)
            wsDB.Cells(DBRow, 30).Value = wsS.Range("AD" & i + 2).Value
            wsDB.Range(wsDB.Cells(DBRow, 51), wsDB.Cells(DBRow, 69)).Copy _
                Destination:=wsDB.Cells(DBRow, 52)
            wsDB.Cells(DBRow, 51).Value = datePlay
        End If
    Next i
    wsS.Cells(1, 21).Value = Date
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Also confirm that every value in column AF is a valid Player DB row number.

Thank you for your patience in reading, I hope this information has been helpful to you. 

---

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.