I don't see why PasteSpecial Values shouldn't work (unless you have merged
cells)

--
Regards,
Tom Ogilvy


"Ken McLennan" wrote in message
...
[This followup was posted to microsoft.public.excel.programming and a copy
was sent to the cited author.]

G'day there One & All,

I'm currently experimenting with a couple of different methods to
import data from various workbooks into a single workbook for various
operations to be conducted upon it.

I'm currently just copying the source worksheets with this code:

srcPg.copy after:=tgtPg

...and it's working fine. At least it was until some of my clients
started to implement their own techniques in their own workbooks (how
DARE they!!!).

I'm finding now when I cycle through my array of workbook titles,
obtained from a GetOpenFileName dialogue, open them in turn, and import
Sheet1 from each; that I get dialogues asking me about updating links,
and/or conflicting names from where different clients have built
formulae and have used the rather imaginative "Range1" in their sheets.
Hence, the first imports OK, but from there on in I get Name Conflict
errors (at least, I think that's what they were called).

I've also tried copying the UsedRange to the clipboard and then
using PasteSpecial to paste only the values and formatting, but that
wasn't quite as successful as I'd hoped. Trouble is that I can't now
remember why not. I'll have to give it another go to find out what went
wrong there.

In the meantime I thought I'd investigate the technique of leaving
the workbooks closed and forming links which I found on a developer's
site somewhere.

The problem is that I don't know the extent of the data in each
sheet that I need to copy. The data is consistently on Sheet1, and also
a consistent number of columns. However the number of rows vary.

Is there a way to determine the UsedRange from a closed workbook?
My experiments to date have all met with failure, however that's just as
likely to be my poor programming skills rather than an inability of XL
to perform the calculation.

Hope to hear back soonish
Thanks to you all
Ken McLennan
Qld, Australia