Why is the VBA code for copy and paste not working?

Question

The entire code shown in "Case 5" is supposed to RESET Worksheets("Attendance") back to its original state and thus be ready to use for a brand new class that I will teach. When the checkbox(5) is chosen on the userform, this code executes. The part that is not working is a follows:

    If x <> 1 Then 
        cksum = cksum + 1 
        Worksheets("Attendance").Range("J12:BN14").Copy 
        Worksheets("Attendance").Range("J12:BN14").Offset(Step, 0).PasteSpecial  
        Paste:=xlPasteValuesAndNumberFormats  
    End If

You will note I put a way to check to see if it was going through this routine using the counter "cksum", which it does 19 times. However, it does not copy the range indicated. The target ranges are still empty.

Case 5     'ATTENDANCE Spreadsheet 
    Worksheets("Attendance").Range("J1:BG7").ClearContents
    Worksheets("Attendance").Range("J10:BG10").ClearContents 
    Worksheets("Attendance").Range("A11:C90").ClearContents   'Student names 
    Step = 0 
    ck = 0 
    cksum = 0 
    For x = 1 To 20 
        Worksheets("Attendance").Range("J11:BN11").Offset(Step, 0).ClearContents 
    **  If x <> 1 Then 
            cksum = cksum + 1 
            Worksheets("Attendance").Range("J12:BN14").Copy 
            Worksheets("Attendance").Range("J12:BN14").Offset(Step, 0).PasteSpecial  
    Paste:=xlPasteValuesAndNumberFormats  
        End If **
        Step = Step + 4 
    Next x 
    MsgBox "Copy routine executed " & cksum & " times." 
    Step = 0 
    For x = 1 To 20 
        Worksheets("Attendance").Range("D11").Offset(Step, 0).MergeArea.ClearContents 
        Step = Step + 3 
    Next x 
    'Worksheets("Attendance").Range("K8").Copy 
    'Worksheets("Attendance").Range("L8:BG8").PasteSpecial 
    xlPasteFormulasAndNumberFormats 
    Controls("Checkbox" & k) = False
Case 6

I did not try anything else. The code is question was obtained from another forum and my research indicates the syntax is correct.

Step is a reserved keyword in VBA, so using it as a variable name is not recommended. — taller
– taller, Commented Nov 13, 2024 at 3:45
This question is similar to: How to avoid using Select in Excel VBA. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — Dominique
– Dominique, Commented Nov 13, 2024 at 10:52

Michal · Accepted Answer · 2024-11-13 02:41:03Z

0

When the code below is executed your Step variable has the value of 0, so .Offset(Step, 0) is .Offset(0, 0) which means that you are over-writing the source cells instead of copying those values to a new location.

Case 5     'ATTENDANCE Spreadsheet 
    Worksheets("Attendance").Range("J1:BG7").ClearContents
    Worksheets("Attendance").Range("J10:BG10").ClearContents 
    Worksheets("Attendance").Range("A11:C90").ClearContents   'Student names 
    Step = 0 
    ck = 0 
    cksum = 0 
    For x = 1 To 20 
    Worksheets("Attendance").Range("J11:BN11").Offset(Step, 0).ClearContents 
**  If x <> 1 Then 
    cksum = cksum + 1 
    Worksheets("Attendance").Range("J12:BN14").Copy 
    Worksheets("Attendance").Range("J12:BN14").Offset(Step, 0).PasteSpecial  
    Paste:=xlPasteValuesAndNumberFormats  
    End If **

answered Nov 13, 2024 at 2:41

Michal

7,6894 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Les Murrell Over a year ago

Step=0 is correct. First pass executes the Range("J11:BN11") but will bypass "If x <> 1 Then." Step increases by 4 each time through the For/Next. Each subsequent pass will execute the code between the **. It copies the source range and steps down 4 to paste. Step will increase again by 4 and the next step will be 8 rows from the source range.

Collectives™ on Stack Overflow

Why is the VBA code for copy and paste not working?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related