How to transfer cell colors and formats using "With" statements?

chuckchuckit

Well-known Member
Joined
Sep 18, 2010
Messages
541
Here is "With" code that transfers data:
Code:
[COLOR=black][FONT=Verdana]With Range("A1:B2")<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Range("D5").Resize(.Rows.Count, .Columns.Count) = .Value<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End With<o:p></o:p>[/FONT][/COLOR]
It does not transfer cell colors, fonts, row heights, or column widths etc. How would I modify (or add to) the above code to transfer such without using the "copy" command?

Am trying to avoid using the clipboard, which the "copy" command uses.

Thanks.

Chuck<o:p></o:p>
<o:p></o:p>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
This method of .Copy doesn't use the clipboard.

Range("A1:B2").Copy Destination:=Range("D5")

It's not the same as .Copy and then .Paste which does use the clipboard.
 
Upvote 0
I used to think that, but was told that even that version does use the clipboard. I think any code with the word "copy" in it does use the clipboard.

I just tried that code:
Code:
Range("A1:B2").Copy Destination:=Range("D5")

and it does erase anything that was in the clipboard prior.

What I tried: In an other instance of Excel I copied something Ctrl-C, then ran that code, then that other instance of Excel could not paste what it had copied, because nothing was there to paste.
 
Last edited:
Upvote 0
I can only go by what others have said and Microsoft.

Copy Method
"Destination Optional Variant. Specifies the new range to which the specified range will be copied. If this argument is omitted, Microsoft Excel copies the range to the Clipboard."

I know if I use the .Copy Destination:= method on a large range, it's much much faster than using the .Copy .Paste method. For me, performance is more of the issue rather than if the clipboard is used or not.

If you copy some cells (Ctrl+C), then type something in a new cell, then try to paste the copied cells to yet another cell, there is nothing to paste. When you copy a range, the clipboard isn't used until you do Both a Copy and an immediate Paste.
 
Last edited:
Upvote 0
Speed is not an issue for my code, but can't use clipboard at all.

Not sure I understand the Microsoft description there. It is sounding like if the destination variant is not specified, it does use the clipboard. If that is true then they would be saying picking a destination variant causes no clipboard use. If so, what variant I wonder would transfer the full formating?
 
Upvote 0
"If you copy some cells (Ctrl+C), then type something in a new cell, then try to paste the copied cells to yet another cell, there is nothing to paste. When you copy a range, the clipboard isn't used until you do Both a Copy and an immediate Paste."

I see the clipboard being emptied there too. But when using the "With" code in the 1st post, it does not empty the clipboard.

There has to be some way to use "With" and transfer formatting.

Even if it is some sort of structure type of code with all the attributes tagged along with it would do.
 
Upvote 0
Can I ask why you can't use the clipboard at all? If you need to preserve something from the clipboard, perhaps you could store it somewhere else until needed.
 
Upvote 0
The reason I can not let my macros use the clipboard to copy is: I have many instances of Excel running automatically in the background at the same time that do queries every minute, and then with their results do an immense amount of processing and copying etc. As long as they take turns doing query and processing when the other Excel instances are not doing the same, they do not crash when they try to use the clipboard at the same time.

So with other posters help here I wrote code that only allows one instance of Excel to use the clipboard at a time (there is only one clipboard per computer). That code was complex, but works great as each instance of Excel waits and takes its turn using the clipboard when many instances are trying to copy at the same time.

But the problem is: I can no longer copy and paste things manually using Ctrl-C, Ctrl-V in any other program if the Excel runnning programs are locking out the clipboard while they take turns copying. I no longer can tell if my manual copy is in the clipboard or not until I try to paste. So I am stuck with re-writing all my copy and paste routines so I can manually copy and paste while those other programs no longer use the clipboard to copy.

I am about 1/3 done and am using those "With" statements to do most of the copying, but they do not transfer all the cell attributes in as broad a sense as the copy and paste does.

But the "With" statements are a whole lot faster way to copy (Macros run a lot faster).

...Now if only I could learn how to use a "With" statement and transfer cell attributes like colors and col and row sizes too... there must be a way...
 
Last edited:
Upvote 0
The following code will "Assign" Values and Formats without affecting the clipboard.
Code:
Sub AssignFormats()
'Assign Values to range
    With Range("A1:B2")
        Range("D5").Resize(.Rows.Count, .Columns.Count) = .Value
    End With
'Assign Font
    With Range("D5").Resize(Range("A1:B2").Rows.Count, Range("A1:B2").Columns.Count).Font
        .Color = Range("A1:B2").Font.Color
    End With
'Assign interior color
    With Range("D5").Resize(Range("A1:B2").Rows.Count, Range("A1:B2").Columns.Count).Interior
        .Pattern = Range("A1:B2").Interior.Pattern
        .PatternColorIndex = Range("A1:B2").Interior.PatternColorIndex
        .Color = Range("A1:B2").Interior.Color
        .TintAndShade = Range("A1:B2").Interior.TintAndShade
        .PatternTintAndShade = Range("A1:B2").Interior.PatternTintAndShade
    End With
'Assign Column widths
        Range("D:D").ColumnWidth = Range("A:A").ColumnWidth
        Range("E:E").ColumnWidth = Range("B:B").ColumnWidth
End Sub
Be aware only the Font, Interior Color, and columnWidth will be Assigned. If more Formats are desired, they will be added to the code.
 
Upvote 0
Thanks much John,

That seems along the lines of what I needed. The squares are coming out black in destination, but I can play with the code to likely figure that out.

But for more cell attributes to try, where would the index of all those properties I can try be found?

Your code has me in the right direction now for sure.

Chuck
 
Upvote 0

Forum statistics

Threads
1,224,609
Messages
6,179,874
Members
452,949
Latest member
Dupuhini

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
Back
Top