help with code

[suresh]

i want to know any one can write a program in such a way
that , from one excel(a.xls)sheet i can import one row in
to another excel pro in sheet1 and othere row in to sheet2
etc
ex
a.xls contain

a 1 2 3 4 5
b d e r t g
c x m o n t

then i want to impot these rows into b.xls in these way
a 1 2 3 4 5
in sheet 1

b d e r t g
in sheet 2


c x m o n t
in sheet 3
thanks

 

[losmac]

Option Explicit

Sub CopyRowToNextSheet()

Dim i As Long
Dim wbk As Workbook, wbkDest As WorkBook
Dim wsh As Worksheet, wshDest As Worksheet

On Error GoTo Err_CopyRowToNextSheet

i = 1
'a.xls and b.xls must be open
Set wbk = Workbooks("a.xls")
Set wbkDest = Workbooks("b.xls")
Set wsh = wbk.Worksheets("Sheet1")

Do While Not IsEmpty(wsh.Range("A" & i))
'copy all row
wsh.Range(i & ":" & i).Copy
Set wshDest = wbkDest.Worksheets("Sheet" & i)
wshDest.Range("A1").PasteSpecial
i = i + 1
Loop



End_CopyRowToNextSheet:
On Error Resume Next
Set wsh = Nothing
Set wshDest = Nothing
Set wbk = Nothing
Exit Sub

Err_CopyRowToNextSheet:
'MsgBox Err.Description, , Err.Number
Select Case Err.Number
Case 9 'destanation sheet does'nt exists
Set wshDest = wbkDest.Worksheets.Add
(After:=wbkDest.Worksheets(wbkDest.Worksheets.Count))
wshDest.Name = "Sheet" & i
Case Else
End Select
Err.Clear
Resume Next

End Sub

 

[suresh]

thans or the help
but this part of the pro is giving error
(After:=wbkDest.Worksheets(wbkDest.Worksheets.Count))
i deleted this step and it worked
is that step important??
any way thans the help

 

[Chip Pearson]

Suresh,

What error are you getting? The line of code

(After:=wbkDest.Worksheets(wbkDest.Worksheets.Count))

should be on the same line as

Set wshDest = wbkDest.Worksheets.Add

or you can use a line continuation character. E.g.,

Set wshDest = wbkDest.Worksheets.Add _
(After:=wbkDest.Worksheets(wbkDest.Worksheets.Count))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[suresh]

as u have said i added _ next to the add and it worked
thans