Run-time error '1004': We Can't do that to a merged cell

[Sarche]

I wrote a VBA code that was working until a few days ago now I am getting an error message that is can't do that to a merged cell when trying to copy and paste. However, there are no merged cells in the section that is being copied and pasted. Below is the code that keeps throwing the error message. I cannot figure out why I keep getting this message. Here is a link to a sample worksheet for the entire code.

20-8010 - FAIRMOUNT CHATTSWORTH STATION - Sample.xlsm

Select Case Sheets("Job2Date").Range("A1") = ""
Case True
Sheets("Job2Date").Range("O7:R701").Copy Sheets("Job2Date").Range("A7")
Case False
Sheets("Job2Date").Range("O7:R701").Copy Sheets("Job2Date").Range("XFD7").End(xlToLeft).Offset(0, 1)
End Select

 

[Joe4]

Do you have any merged cells in your workbook?
If so, I highly suggest getting rid of them. They cause all sorts of problems for things like sorting, VBA, etc.
Most programmers avoid them like the plague.

If you are just using them to merge columns across a row (or rows), you can get the same visual effect without all the issues that merged cells cause, if you use the "Center Across Selection" formatting option instead.

 

[Fluff]

there are no merged cells in the section that is being copied and pasted.

Yes there are, you have merged cells in both the copy range & the paste range.

 

[Sarche]

I took out the merged cells and did the "center across section" formatting and I am still getting that error. The only merged cells I found in row 10.

 

[Fluff]

O7:R7
O10:P10
A8:E8
E9:F9
J9:M9
Are all merged cells not to mention even more of them from row 447 downwards.

 

[Sarche]

O7:R7
O10:P10
A8:E8
E9:F9
J9:M9
Are all merged cells not to mention even more of them from row 447 downwards.

Thank you. I am removing them now. How did you find those so fast? Is there a short cut beside visually looking? I did not make the sheet just wrote the code and I did not notice those merged cells.

 

[Fluff]

I just tabbed through some of the cells, to find the merged cells.
However I don't understand why you are copying the data from columns O:R to A:D as you are going to overwrite a lot of info.

 

[Sarche]

I just tabbed through some of the cells, to find the merged cells.
However I don't understand why you are copying the data from columns O:R to A:D as you are going to overwrite a lot of info.

I am still a beginner when it comes to VBA. The ultimate goal of this entire code is when a new tab is created it will ask you the starting date, fill in the appropriate dates at the top of each section, then add a new week summary to the Job2Date tab as well as name the new tab with the next numbered week. Columns O through R is full of formulas that pull from Week (1). When Week (2) is created I need those same formulas but I need them to pull from Week (2) not Week (1). If there is an easier way to code this I would love to learn it as I am sure I am over thinking most of it.

 

[Fluff]

If you are just trying to copy those cells to the next available columns try

With Sheets("Job2Date")
   .Range("O7:R447").Copy .Cells(8, Columns.Count).End(xlToLeft).Offset(-1, 1)
End With

 

[Sarche]

If you are just trying to copy those cells to the next available columns try

With Sheets("Job2Date")
   .Range("O7:R447").Copy .Cells(8, Columns.Count).End(xlToLeft).Offset(-1, 1)
End With

That worked, but it is not updating the formulas to pull from the new tab.