Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with code
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with code
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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with code
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 . . |
#5
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 . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |