On Apr 4, 7:41*pm, Orimslala <kol...@yahoo.com> wrote:
> On 4 Apr, 09:31, Ivyleaf <ica...@gmail.com> wrote:
>
>
>
>
>
> > On Apr 4, 7:18*pm, Ivyleaf <ica...@gmail.com> wrote:
>
> > > On Apr 4, 4:13*pm, Orimslala <kol...@yahoo.com> 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").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.- 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- 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.
|
Similar Threads
