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

Programming advice please
 

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" wrote in message
...
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

Programming advice please
 

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" wrote in message
...
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