Run-Time Error 1004 Cannot Change Part of a Merged cell.

[dosborn]

I am getting Run-time error 1004 "Cannot Change Part of a Merged Cell". I am trying to copy Sheet1 cell E11 (merged cells E11:G11) to Sheet2 cell E11 (merged cells E11:G11). Can someone please help me figur out where I am going wrong. Thanks.

Sub PartBalance()

With Sheets("PART REQUEST")

If Range("D11").Value - Range("K11").Value <> 0 Then

Range("B11").Copy Sheets("PURCHASE REQUISITION").Range("M11")
Range("E11").Copy Sheets("PURCHASE REQUISITION").Range("E11")

End If

End With

Sheets("PURCHASE REQUISITION").Range("C11").Value = Sheets("PART REQUEST").Range("T11").Value

If Sheets("PURCHASE REQUISITION").Range("C11").Value > 0 Then

MsgBox "Purchase Requisition Needed " & Sheets("PART REQUEST").Range("E11").Value

End If

 

[VoG]

Merged cells are best avoided (Horizontal Alignment=XLCenter achieves the same visual effect). However, try

Range("E11").Copy Sheets("PURCHASE REQUISITION").Range("E11").Resize(, 3)

 

[Jonmo1]

Well, the error pretty much says it all...
You can't change PART (E11) of a Merged Range (E11:G11).

You have to copy/paste the WHOLE merged range.
Range("E11:G11").Copy Sheets("PURCHASE REQUISITION").Range("E11:G11")


Or, avoid using merged cells to begin with.
Merged Cells and VBA go together about as well as Oil And Water.

A very similar effect can be achieved with "Center Accross Selection"
Found in format - Cells - Alignment - Text alighnemt - Horizontal Dropdown.

 

[dosborn]

Thank you very much. I tried that before but I must have not had the right cell range or something. It is difficult because I m trying to make a form for ordering parts that can work automated or if my employees need to fill it out by hand it is functional that way too. Anyhow. Thanks for the help.