Excel VBA PasteSpecial not working for specific cells

Question

I have some VBA code in Excel to copy information from one sheet of a workbook to another sheet in the same workbook. All of the copy/paste code works except for 3 cells on the destination sheet and I can't figure out why. Here is a code snippet that includes a working section and a broken section.

'Works to copy A11 from "Job Entry" to A2 on "Work List"
Sheets("Job Entry").Select    
Range("A11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work List").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

'Does not work to copy B11 from "Job Entry" to M2 on "Work List"
Sheets("Job Entry").Select
Range("B11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Work List").Select
Range("M2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

The code that copies cell A11 from "Job Entry" to "Work List" works fine. However, the code that copies cell B11 from "Job Entry" to "Work List" does not. All of the cells involved use date values and they're all formatted to use the date format MM/DD/YY.

I've spent a decent amount of time trying to find a solution for this, but can't find anything to help with this specific predicament.

What does "does not work" mean? Note - you can just value transfer: `Sheets("Work List").Range("A2").Value = Sheets("Job Entry").Range("A11").Value`. — BigBen, Dec 20 '19 at 15:04
`Sheets("Work List").Range("A2").Value = Sheets("Job Entry").Range("A11").Value` and `Sheets("Work List").Range("M2").Value = Sheets("Job Entry").Range("B11").Value` — Scott Craner, Dec 20 '19 at 15:06
Updated the code with comments on which one works and which does not. The first block works to copy from Job Entry to Work List, but the second block does not copy from Job Entry and paste to Work List. I tried the code provided in the comments and the M2 cell on Work List still remains blank after running it. — Dermaeher88, Dec 20 '19 at 15:15
Also [Interesting Read](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Dec 20 '19 at 15:15
It leaves the cell blank? is `Sheets("Work List").Range("M2")` a merged cell? — Scott Craner, Dec 20 '19 at 15:29
is the format of the target cell causing the value to not be seen? — Scott Craner, Dec 20 '19 at 15:31
None of the cells being referenced are merged and the only special formatting they have is to be in a date format.https://www.dropbox.com/s/m4asde30ges1qvp/Maintenance%20Scheduling.xlsm?dl=0 here's a data stripped copy of the whole workbook. Maybe one of the other macros is causing the issue? — Dermaeher88, Dec 20 '19 at 16:28
@Dermaeher88 no one is going to download and open a macro workbook. You are the one that will need to step through the code and find where the issue is. — Scott Craner, Dec 20 '19 at 17:51

score 0 · Answer 1 · 2019-12-20T16:06:29.260

Just replace all Range("...").Select with a "fully qualified" notation: Activesheet.Range("...").Select.

Other than that, it looks like you've presented a somewhat modified output of the "macro recorder". A "real" VBA code does each block of your sample with one simple line of code (copying both content and formatting), like (for the second block):

Sheets("Job Entry").Range("B11").Copy Sheets("Work List").Range("M2")

Also, if you what just values then the comment by @Scott Craner will serve the purpose.

And, of course, look out for merged cells.

score 0 · Accepted Answer · answered Dec 20 '19 at 18:34

Sorry for the confusion. I figured out what was going on. Although the cells I was copy/pasting weren't merged, some cells that were being copied later in the subroutine were merged and it was wiping out the previously pasted data.

Thanks for all of the suggestions and info. Apologies for the noobism :).

Excel VBA PasteSpecial not working for specific cells

2 Answers2