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

[chuckchuckit]

Here is "With" code that transfers data:

[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></o>
<o></o>

 

[AlphaFrog]

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.

 

[chuckchuckit]

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:

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.

 

[AlphaFrog]

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.

 

[chuckchuckit]

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?

 

[chuckchuckit]

"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.

 

[AlphaFrog]

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.

 

[chuckchuckit]

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...

 

[Datsmart]

The following code will "Assign" Values and Formats without affecting the clipboard.

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.

 

[chuckchuckit]

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