CODEWITHSUNDEEP
vbaexcelmerge
bdkong
bdkong

Reputation: 191

Can't paste into merged cell using VBA

I'm writing a macro that copies data to a template form, saves the form under a different name, and then copies using the same blank form. Some of the data is copied into merged cells. Weirdly enough, the code that I am currently using works for the first, second, and third iteration, but not the fourth.

I've tried everything I can think of but it still won't work. It gives me the error message "We can't do that to a merged cell".

Here is the code that won't paste into the cell.

'     
' Transfers Component 1 Data
'
    Range("B27").Select
    Selection.Copy
    Windows("Protected_Form.xls").Activate
    Range("B61:D61").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

And here is the code that DOES paste into the merged cell earlier on in the program:

'
' Transfers Component 1 Data
'
    Range("AV194").Select
    Selection.Copy
    Windows("Protected_Form.xls").Activate
    Range("B61:D61").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

As you can see, the only thing that changes is what cell the data is being copied from.

Edit: Just realized that I used the same code(the part that isn't working) early on in the program under certain conditions. I just tried running it under those conditions and it worked.

Now I'm extremely confused. Feel free to ask me to clarify things, I know this is confusing.

Also: I know .Select. is slow, I don't care.

Upvotes: 2

Views: 8921

Answers (1)

sous2817
sous2817

Reputation: 3960

Have you tried fully qualifying your range references? Perhaps earlier in the program Range("B27").Select is selecting on one sheet and Range("B27").Select is selecting from a different sheet later on.

Also, and not to harp on it but... .Select is not only slow, it also tends to lead to unexpected results, which I suspect is the issue you're having now. I know you said in a comment that you don't have time (luxury) to get away from it, but the time spent cleaning up your code now will save you TONS of time if you ever have to debug, change, modify, update, etc. I wouldn't look at it as a chore, but a necessity to writing good VBA code!

Upvotes: 2

Related Questions