Excel VBA If Then Copy Paste Format

[SandyBeach]

I have a range which contains individual cell formatting which makes up specific designs. The range is quite large - 500 columns x 300 rows - and is trimmed as required by a VBA routine in order for the design to fit specific dimensions. Trimming is not just by cutting off the surplus right hand columns or bottom rows as the trimming modifies both the size and elements of the design. So, trimming may be effected by cutting off both left and right hand columns, or just the left hand columns, or just the top rows, etc. That is stage 1 and it works well.

Stage 2 requires the trimmed design/range - for which I know the exact number of columns and rows (which could be 500 x 300 but is generally not) - to be copied to another range of exactly the same size and which contains other design elements. Given the design is all formats I want to be able to look at each destination cell to determine if it is occupied (probably by an integer) before copying and pasting from the original range. So, the routine needs to look at, say, the top left hand cell of the destination range and identify if it it is vacant or filled before copying the format in the top left cell of the source range. If the destination cell is vacant, the contents are copied and pasted. If the destination cell is occupied, the routine moves on without copying and pasting.

I have fiddled about with some code but it is so bad i am not going to present it here!

All suggestions gratefully received.

 

[SandyBeach]

Thank you. I probably won't get to it until tomorrow.

 

[SandyBeach]

I entered your code but it stopped with at the "set r" line

As I didn't rally understand what the code was trying to do (this is getting embarrassing), I had no idea of what to modify.

 

[aRandomHelper]

When you run it, are there values in G33 / H31, or are they being cleared off ? they are supposed to tell the code how many rows/columns your range should be.

 

[SandyBeach]

Oops, operator error - I had a number in G34 not G33. Fixed that and got the following...

 

[aRandomHelper]

On which line did that error happen?

 

[SandyBeach]

Sorry about the delay - I think I am on the opposite side of the world to you....

 

[aRandomHelper]

Did you change the spacing?
That line should be:

c.Copy .Range(c.Address(0, 0))
      ^
  space here

c.Copy.Range (c.Address(0, 0))
            ^
         not here

 

[SandyBeach]

That fixed it. My mistake. The format from sheet 1 is now copied to sheet 2, and it doesn't paste over what is already there.. See before and after below. Thank you very much. You have been a great help. This exercise may even make me take a VB course!

Before

After

 

[aRandomHelper]

Glad it worked, and thanks for the feedback!

This exercise may even make me take a VB course!

If you enjoy programming, you would definitely enjoy it! Good luck!

 

[SandyBeach]

I loved working with Fortran so.....

Thanks again.