Forum

Run-time error '100...
 
Notifications
Clear all

Run-time error '1004'

4 Posts
2 Users
0 Reactions
94 Views
(@mymalone)
Posts: 103
Estimable Member
Topic starter
 

I have a macro and received error message, cannot change part of a merged cell.  Can someone tell me what I am doing wrong and what is the solution.

Here is my macro

Dim myArray (0 to 1000) As Variant

Workbooks("MGN-JULY 2017_Final Report").Worksheets(Sheet2").Range("J94:L94").Copy Destination:=Workbooks("MGN_SortMacro_JULY 2017").Worksheets ("TEST H2").Range("E5:P5")

End Sub

 

Columns E5:P5 are merged.

 

Thanks

 
Posted : 24/09/2017 9:06 am
Anders Sehlstedt
(@sehlsan)
Posts: 971
Prominent Member
 

Hello,

I am just a basic user of VBA, but I will try to give some help anyway.

What first strikes me is that your range where you copy the data from is three cells, J94, K94 and L94. But the range where you want to paste it in is scripted to be a range of 12 cells. That is probably the main reason to your issue, the merged cells.

In this forum I found that when the user used xlPasteValuesAndNumberFormats it worked fine to paste the values to merged cells. Do test to use the PasteSpecial method. I am also linking to a useful site where you can read more about using PasteSpecial.

Best of luck!

Br,

Anders

 
Posted : 25/09/2017 8:13 am
(@mymalone)
Posts: 103
Estimable Member
Topic starter
 

Mr. Anders

This is most helpful...was able to make adjustment to macro.

Thank you so very much!!

 
Posted : 28/09/2017 1:57 pm
Anders Sehlstedt
(@sehlsan)
Posts: 971
Prominent Member
 

Hello,

That's great you found a solution.

Br,

Anders

 
Posted : 28/09/2017 5:23 pm
Share: