Why does my VBA loop paste incorrect values?

Question

I have created a loop where data is copied from a worksheet and pasted into another, however I am having problems with the paste function – sometimes the wrong data gets pasted, seemingly randomly. My current code is:

    Sub ACCPR_LOOP()

Dim wsACC_PR As Worksheet
Set wsACC_PR = ThisWorkbook.Sheets("ACC PR")
Dim wsPR_CALC As Worksheet
Set wsPR_CALC = ThisWorkbook.Sheets("PR - CALC")
Dim MyRange As Range
Dim MyCell As Range
Set MyRange = Range("A2:A145")

Application.ScreenUpdating = False    
Columns("B:C").ClearContents

For Each MyCell In MyRange
    MyCell.Copy
    wsPR_CALC.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    wsPR_CALC.Range("B226,B228").Copy
    MyCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True

Next MyCell

End Sub

What the code is doing is in Col A are a bunch of dates, it copies the date in A, then pastes it into another worksheet to update a drop-down date selector and change the data. Two of the cells are then copied and pasted back into the original worksheet with an offset of 1 column. For some reason sometimes, the data from the previous date in A is pasted. For example, the date in A17 is copied and pasted into the date selector, the correct data is then pasted into B17, but on the next step, the data relating to A17 is pasted into the next row down at B18.

If a repeat the line:

MyCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True

the code works but this seems rather inefficient. Any ideas what’s going on in my code and how I can fix it?

Vityata · Accepted Answer · 2018-02-15 14:18:26Z

2

In the Set MyRange = Range("A2:A145") you should declare the corresponding worksheet as well. E.g.:

Set MyRange = Worksheets("MyNameIsWhat").Range("A2:A145")

Otherwise, it would take the ActiveWorksheet and the MyRange would be assigned to it.

The same goes to Columns("B:C").ClearContents.

It should be Worksheets("TsakaTsakaSlimShaddy").Columns("B:C").ClearConents

answered Feb 15, 2018 at 14:18

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

2 Comments

Xabier Over a year ago

Loving the Eminem reference, +1 just for that! :)

Wookies-Will-Code Over a year ago

"SlimShady" the ranges are not specified to a sheet as discussed above. Excel is not going to know what you want when you are bringing focus to more than one sheet with a range that is not clearly "pathed".

matt2103 · Accepted Answer · 2018-02-15 14:58:36Z

I always try to avoid the copy/paste function in VBA. It's processor intensive and functions ... arcanely.

Try this instead:

For Each MyCell In MyRange
    wsPR_CALC.Range("A1").Value = MyCell.Value
    Application.Calculate
    MyCell.Offset(0, 1).Value = wsPR_CALC.Range("B226,B228").Value
Next MyCell

You'll lose the number formatting, but there are other ways of doing that. I also added an Application.Calculate line, because it looks like you're copying from a formula in the second step, and it's good to make sure that value gets updated. You can also try Application.CalculateFull if plain .Calculate isn't cutting it.

Also, to echo Vit, if you're working with multiple sheets, declaring your sheet as often as possible will help as well.

Collectives™ on Stack Overflow

Why does my VBA loop paste incorrect values?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related