Re: Copy and paste selected columns between Excel workbooks

Tech-Archive recommends: Fix windows errors by optimizing your registry

On 4 Apr, 09:31, Ivyleaf <ica...@xxxxxxxxx> wrote:
On Apr 4, 7:18 pm, Ivyleaf <ica...@xxxxxxxxx> wrote:





On Apr 4, 4:13 pm, Orimslala <kol...@xxxxxxxxx> wrote:

Hi,

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

I keep on getting an error on Active***.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 *** name.
Alternatively, you could get away with using Active*** if you will
always have the source data *** 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.Active***.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
.

Quantcast