Programming advice please

I have a fair bit of information to swap between two workbooks.
From a utility workbook "Bk-a.xls", I compiled a macro as follow.
Line numbers are added for reference here.
I would appreciate some advice on

why line 70+80 failed (application or obj defined error)
but line 60 is OK

line 90 must be in the form as line 100;

Is there ways to avoid switching windows repeatedly please?

10 Sub t()
20 Dim frmBK As Workbook
30 Dim toBK As Workbook
40 Set frmBK = Workbooks.Open("Bk-b.xls")
50 Set toBK = Workbooks.Open("Bk-c.xls")

60 frmBK.Sheets(1).Range("B1").Copy toBK.Sheets(1).Range("C2")
70 'frmBK.Sheets(1).Range(Range("B1"), Range("B1").End(xlToRight)).Copy _
80 'toBK.Sheets(1).Range("C2")

90 'Windows(frmBK).Activate
100 Windows("bk-b.xls").Activate
110 Range(Range("B1"), Range("B1").End(xlToRight)).Copy
120 'Windows(toBK).Activate
130 Windows("bk-c.xls").Activate
140 Range("C2").PasteSpecial
150 End Sub

Regards
Have a happy New Year
KC

One thought.

If row 1 only has an entry in B1, and nothing else, the
Range("B1").End(xlToRight) will select all the way up to column IV. When you
try to copy 255 cells to C2 (where there are only 254 left in the row), it
would fail.

Try this alternative

frmBK.Sheets(1).Range(Range("B1"), Range(Range("B1"),
cells(1,columns.Count).End(xlToLeft)) _
..Copy toBK.Sheets(1).Range("C2")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"KC Cheung" <[email protected]> wrote in message
news:[email protected]...
> I have a fair bit of information to swap between two workbooks.
> From a utility workbook "Bk-a.xls", I compiled a macro as follow.
> Line numbers are added for reference here.
> I would appreciate some advice on
>
> why line 70+80 failed (application or obj defined error)
> but line 60 is OK
>
> line 90 must be in the form as line 100;
>
> Is there ways to avoid switching windows repeatedly please?
>
> 10 Sub t()
> 20 Dim frmBK As Workbook
> 30 Dim toBK As Workbook
> 40 Set frmBK = Workbooks.Open("Bk-b.xls")
> 50 Set toBK = Workbooks.Open("Bk-c.xls")
>
> 60 frmBK.Sheets(1).Range("B1").Copy toBK.Sheets(1).Range("C2")
> 70 'frmBK.Sheets(1).Range(Range("B1"), Range("B1").End(xlToRight)).Copy
_
> 80 'toBK.Sheets(1).Range("C2")
>
> 90 'Windows(frmBK).Activate
> 100 Windows("bk-b.xls").Activate
> 110 Range(Range("B1"), Range("B1").End(xlToRight)).Copy
> 120 'Windows(toBK).Activate
> 130 Windows("bk-c.xls").Activate
> 140 Range("C2").PasteSpecial
> 150 End Sub
>
> Regards
> Have a happy New Year
> KC
>
>

Bob

Thanks for your response.
No, I have deliberately made a row of 10 cells only so that I can keep watch
on the sheets.

Also I cannot see your response in the news group, only from DeveloperDEX
please

Regards
KC

From: Bob Phillips
Date Posted: 12/31/2005 6:39:00 AM


One thought.

If row 1 only has an entry in B1, and nothing else, the
Range("B1").End(xlToRight) will select all the way up to column IV. When you
try to copy 255 cells to C2 (where there are only 254 left in the row), it
would fail.

Try this alternative

frmBK.Sheets(1).Range(Range("B1"), Range(Range("B1"),
cells(1,columns.Count).End(xlToLeft)) _
...Copy toBK.Sheets(1).Range("C2")


--

HTH

RP


--
Regards
KC Cheung
The Inglewood
"KC Cheung" <[email protected]> wrote in message
news:[email protected]...
> I have a fair bit of information to swap between two workbooks.
> From a utility workbook "Bk-a.xls", I compiled a macro as follow.
> Line numbers are added for reference here.
> I would appreciate some advice on
>
> why line 70+80 failed (application or obj defined error)
> but line 60 is OK
>
> line 90 must be in the form as line 100;
>
> Is there ways to avoid switching windows repeatedly please?
>
> 10 Sub t()
> 20 Dim frmBK As Workbook
> 30 Dim toBK As Workbook
> 40 Set frmBK = Workbooks.Open("Bk-b.xls")
> 50 Set toBK = Workbooks.Open("Bk-c.xls")
>
> 60 frmBK.Sheets(1).Range("B1").Copy toBK.Sheets(1).Range("C2")
> 70 'frmBK.Sheets(1).Range(Range("B1"), Range("B1").End(xlToRight)).Copy
_
> 80 'toBK.Sheets(1).Range("C2")
>
> 90 'Windows(frmBK).Activate
> 100 Windows("bk-b.xls").Activate
> 110 Range(Range("B1"), Range("B1").End(xlToRight)).Copy
> 120 'Windows(toBK).Activate
> 130 Windows("bk-c.xls").Activate
> 140 Range("C2").PasteSpecial
> 150 End Sub
>
> Regards
> Have a happy New Year
> KC
>
>