How to copy range in Excel without clipboard?

[Vuncs]

I have a macro that creates a report from several text files. I have a workbook (a template) with formatted cells (merged cells, borders, labels, etc.). I want to copy one formatted range from the template workbook to an other, but I don't want to use the clipboard (neither the Excel's nor the windows').
I have tried the ".Copy destination:=", it does not use the Excel's clipboard, but use the windows'. Because the text files are large, and other tasks are running on the computers while the processing is in progress, the users want to use the clipboard for other purposes.
How could be possible to copy one formatted range to an other without the clipboard?
I have tried copying all properties cell by cell (by "=" not with ".Copy"), but it was very slow. Is there a better (faster) way to do this task?

 

[MeGustaXL]

Have you tried Skip Vought's "How to make my code run faster" FAQ?

faq707-4105

The Copy bit "Skips" the clipboards! (Sorry)


Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[Loomah]

Just a blindstab in the dark but can you add the data to an array then apply that array to the new workbook?

Not sure what would happen to formatting and have a feeling that the merged cell will stuff things up - as they usually do - but it might be worth a try?

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

Loomah - using the destination part of the copy method is by far the easiest way (that is what is used in Skip's FAQ)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Of course, if you can avoid the Copy method altogether it would be even faster, now wouldn't it. ;-)

The best solution (IMHO) is (of course this is all HIGHLY insubstantial as I've not seen any code or anything) to use as little processes/objects/methods which require memory as possible. You can't avoid this, bottom line, you have to do something, but the question is what. As I mentioned about the Copy method (CutCopyMode), sometimes it's not needed if you only want to assign values to a location (namely an Excel file).

Now as you mentioned formats, borders, etc, it's kind of hard to get away from the Copy method unless you want to check these formats by hand and set them individually. As there is a bundle of them, I'd not recommend it, I think it would take longer than the Copy method. Bottom line, if you Copy, there's no way around using a Clipboard.

Also, btw, please take out all of your merged cells. 1) There are other ways to accomplish what you are trying to do with them, and 2) they wreak havoc on a spreadsheet, especially if you add any VBA code to it/them.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Loomah]

Just to drag this one up again!!

xlbo
I'm aware of that but the original problem (for whatever reason) is to avoid use of the clipboard.

I'm not sure if "Copy destination" uses the clipboard. If it does I'd guess it's only for a negligible period of time!

Anyway, just for the hell of it, if the destination cells are already formatted this will do the job. Even allows for merged cells if they are in the same place in each sheet. Having said that my earlier comment on merged cells and firefytr's comments are valid and should be heeded - loose the merged cells!!

Sub a()
Dim myArr As Variant
Dim col As Integer, row As Long
With Worksheets("Sheet1").Cells(1, 1).CurrentRegion
    myArr = .Value
    row = .Rows.Count
    col = .Columns.Count
End With
With Worksheets("Sheet3").Range(Cells(1, 1), Cells(row, col))
    .ClearContents
    .Value = myArr
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Vuncs]

Thanks everybody for the answers.

To Loomah:
Unfortunatelly, there are a lot of Copy action (in average cases more than 20-25.000 ) in the process. The ".Copy Destination" uses the windows clipboard, but not uses the excel's clipboard. The merged cells are in different places, according to the log file contents (so it can't be done the cells merging before the processing).
The real problem is not the copying of the cell values (it is done by using arrays), but the merging, formatting, borders, color etc.

I think, we (me and the users of this macro) must accept that using the clipboard is the most effective way to do this task.

Once again, thanks everybody!