Excel VBA If Then Copy Paste Format

SandyBeach

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Thank you. I probably won't get to it until tomorrow.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

SandyBeach

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I entered your code but it stopped with at the "set r" line

1619304233667.png


1619304266850.png


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

Active Member
Joined
Jan 14, 2021
Messages
301
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Oops, operator error - I had a number in G34 not G33. Fixed that and got the following...

1619350669861.png
 

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
301
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

On which line did that error happen?
 

SandyBeach

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Sorry about the delay - I think I am on the opposite side of the world to you....

1619395731262.png


1619395674802.png
 

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
301
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Did you change the spacing?
That line should be:
Rich (BB code):
c.Copy .Range(c.Address(0, 0))
      ^
  space here

Rich (BB code):
c.Copy.Range (c.Address(0, 0))
            ^
         not here
 

SandyBeach

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
1619403820478.png


After
1619403782993.png
 

Forum statistics

Threads
1,143,620
Messages
5,719,789
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top