Help getting code to carry merged cells as code continues down sheet

[bdouglas1011]

I have a sheet that I am copy and pasting into another sheet. The problem is that my range is (A9:R65536) so I get the whole sheet it gets longer at certain times. once I select my range and paste it certain cells in each row are merged i can go line by line merging the proper ones but there has got to be a better way. this is what I have so far.

Please Login or Register  to view this content.

You can each row has the same cells merged but how do get the code to merge as it goes down the sheet without doing each line myself.

Thanks for your help

[sintek]

Rule No 1 with excel ... Stay away from merged cells ... lol


Edit...Oops, sorry did not think this one through lol

[ptmuldoon]

Can you post a sample file?

And yes, like sintek mentioned. VBA and merged cells don't play nice.

And two questions for you.

1. Are you copying the entire worksheet in your copy? Possibly easier to simply copy the worksheet vs copying the range.
2. Instead of using Merged Cells, what about using Center Across? It achieves the same visual look and avoids the merged cell issue with looping.

[bdouglas1011]

I have a workbook attached.....(SHEET 1) is actually a separate file I choose when running the macro but the file builds going down so when finished i might have 200 rows or more going down the sheet. When i run the macro I want to choose the file and copy it to the sheet (Noralis email gen (2)). If for some reason you cant get the header info in rows 1-9 then the copy can just start from 10 going down rows 1-200

Please Login or Register  to view this content.

[sintek]

If you really want to merge then make use of a loop like such to do the job...

Please Login or Register  to view this content.