Copy and paste selected columns between Excel workbooks

[Orimslala]

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

 

[Ivyleaf]

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

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.

 

[Ivyleaf]

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

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.

 

[Orimslala]

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

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

 

[Ivyleaf]

Hi Ivan,

Thanks for responding...

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

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- Hide quoted text -

- Show quoted text -

Hmmm... not sure why the second one is failing for you... I did test
it before posting and it worked fine for me. It might be the
'ActiveSheet' part in the copy line. You could try substituting for
something like the following (change for your sheet name):

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

Other than that, the only thing I can suggest is triple checking all
your book names etc. Oh, and I haven't tested this code with either of
the books closed, so I am assuming they are both open when you are
running it.

Cheers,
Ivan.