Using Basic, I need to copy all of the VALUES from a sheet in one SS to another SS, while retaining all of the formatting, column widths, etc. I know that I can manually copy the source sheet contents to the clipboard and then use Paste Special to paste the values and formatting to a sheet in the other document. This works just fine and retains every last bit of formatting.

For use in a Basic macro, I have found the PasteCellRange method that should allow me to paste the clipboard to the other SS. My question is: what do I use to copy the source sheet to the clipboard so I can then paste it?

I know that oRange.DataArray = iRange.DataArray will copy the data, but loses all formatting. I know that using Transferable Content will copy the FORMULAS and that I can then use oRange.DataArray = iRange.DataArray to replace the formulas with the values. This gives me everything I want except retaining the column widths. I could add code to redo the column widths, but I would need special code for each different sheet that was copied. I need a general solution that retains the column widths.

I imagine that this question has been answered before, but I can't find it. Thanks for your help.

Last edited by Herb40 on Sun Apr 16, 2017 3:55 pm, edited 1 time in total.

Select the exchangable range of cells and call Tools>Scenarios... to create a new scenario.Give a name to the selected scenario range (just type a name into the name box left of the formula bar).Organize scenarios in the navigator (hit F5 for the navigator and then its last button)

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x

Villeroy wrote:It is a way easier to exchange data on the same formatted sheet by means of scenarios.

"...on the same formatted sheet..." But I need to exchange data to a different sheet in another SS.

"It is way easier... by means of scenarios." But I'm just looking for info about an API method that will copy a range to the clipboard. Why would anyone want to use a scenario for this? KISS is the approach I prefer.

You can use the Clipboard (by macro) for copying Cell styles, and other properties together with the cell contents.You must copy a whole column for copying the Column properties, and a whole row for copying the row properties.

Or you can get the desired property values in the source document by API functions and you can set them in the target document.

See Pitonyak's books, and the API descriptrions.

Tibor Kovacs, Hungary; LibreOffice4.4.7 on Win7x64Prof.And the portable versions: LO3.3.0-LO5.4.1 and AOO4.1.3 Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.

Thanks, guys. I followed the discussion of the dispatcher and data exchange in Andy Pitonyak's OOME and soon achieved a successful copy, except that the optimal column widths were not retained by the dispatcher's Copy action. I had noted that if I did a manual SelectAll, Copy, and PasteSpecial, everything was copied intact. So I used the macro recorder and found that the dispatcher issued a SelectAll before the Copy. This addition is included in the following code which should achieve everything required to produce an exact copy of the values and formatting. In this code, all of the "i..." objects (iDoc, iSheet, etc.) refer to the "input" SS whose iSheet is to be copied. Likewise, all of the "o..." objects refer to the "output" SS whose oSheet is to receive the copy.

Thanks to Andy Pitonyak for presenting the basis for this code. I did not experiment with the six Property Values supplied to the dispatcher, and assume that all six are required.