ExcelBanter - help with code

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - help with code (https://www.excelbanter.com/excel-programming/287343-help-code.html)

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

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.Coun t))
wshDest.Name = "Sheet" & i
Case Else
End Select
Err.Clear
Resume Next

End Sub

-----Original Message-----
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

.

thans or the help
but this part of the pro is giving error
(After:=wbkDest.Worksheets(wbkDest.Worksheets.Coun t))
i deleted this step and it worked
is that step important??
any way thans the help
-----Original Message-----
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.Cou nt))
wshDest.Name = "Sheet" & i
Case Else
End Select
Err.Clear
Resume Next

End Sub

-----Original Message-----
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

.

.

Suresh,

What error are you getting? The line of code

(After:=wbkDest.Worksheets(wbkDest.Worksheets.Coun t))

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.Coun t))

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

"suresh" wrote in message
...
thans or the help
but this part of the pro is giving error
(After:=wbkDest.Worksheets(wbkDest.Worksheets.Coun t))
i deleted this step and it worked
is that step important??
any way thans the help
-----Original Message-----
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.Cou nt))
wshDest.Name = "Sheet" & i
Case Else
End Select
Err.Clear
Resume Next

End Sub

-----Original Message-----
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

.

.

as u have said i added _ next to the add and it worked
thans
-----Original Message-----
Suresh,

What error are you getting? The line of code

(After:=wbkDest.Worksheets(wbkDest.Worksheets.Cou nt))

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.Coun t))

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

"suresh" wrote in
message
...
thans or the help
but this part of the pro is giving error
(After:=wbkDest.Worksheets(wbkDest.Worksheets.Coun t))
i deleted this step and it worked
is that step important??
any way thans the help
-----Original Message-----
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.Cou nt))
wshDest.Name = "Sheet" & i
Case Else
End Select
Err.Clear
Resume Next

End Sub

-----Original Message-----
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

.

.

.