Posted to microsoft.public.excel.programming
|
|
help with code
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
.
.
.
|