On 4 Apr, 09:31, Ivyleaf wrote:
On Apr 4, 7:18*pm, Ivyleaf wrote:





On Apr 4, 4:13*pm, Orimslala wrote:

Hi,

I am trying to copy a selected number of columns from one workbook
and
paste it into another worksheet in a second workbook.

I keep on getting an error on ActiveSheet.Paste.(ie. TargetWS.Paste
below)

How can i get round this or better still could a vb champion show me
the correct way to do this.

Thanks.

kolu

* * Set SrcWkb = Workbooks("source.xls")

* * Application.Windows("source.xls").Activate
* * Columns("G:I").Select
* * Application.CutCopyMode = False
* * Selection.Copy
* * Application.Windows("Target.xls").Activate
* * Columns("G:I").Select
* * With TargetWkb.Worksheets("sheet")
* * * * TargetWS.Paste
* * End With

The error message i get is : object invoked has disconnected from its
cilents when i execute *TargetWS.Paste

Hi Orimslala,

If both workbooks are open, then the following should be all that you
need:

* * Workbooks("Target").Sheets("sheet").Columns("G:I") = _
* * * Workbooks("source").Sheets("...").Columns("G:I").V alue

Make sure to replace the "..." in the second bit with your sheet name.
Alternatively, you could get away with using ActiveSheet if you will
always have the source data sheet active when you run it.

If you need something that will work with the books closed, let us
know.

Cheersa,
Ivan.- Hide quoted text -

- Show quoted text -

Just in reference to your original posted code, if you really want to
use the paste method (I usually try to avoid running data throught the
Clipboard), then an optimised version of your code would look
something like this:

Sub CopyCols()
* * Dim SrcWkb As Workbook, TargetWkb As Workbook

* * Set SrcWkb = Workbooks("source.xls")
* * Set TargetWkb = Workbooks("Target.xls")

* * SrcWkb.ActiveSheet.Columns("G:I").Copy _
* * * * Destination:=TargetWkb.Sheets("sheet").Columns("G: I")

End Sub

The only advantage in using the clipboard is the it will also copy
more formatting than any other single method I know.

Cheers,
Ivan.- Hide quoted text -

- Show quoted text -

Hi Ivan,

Thanks for responding...

Workbooks("Target").Sheets("sheet").Columns("G:I") = _
Workbooks("source").Sheets("...").Columns("G:I").V alue

The above solves 80% of my problem, the remaining 20% is that it does
not paste the colour of cells from the source workbook.

Your other alternative CopyCols() gives me the same error as my
original post

Thanks for your feedback

Orimslala