Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Moving data between worksheets ...
Hi,
Wonder if someone can help me please? I have a sheet in a workbook called "Sheet1" (please see www.iedmont.blogspot.com/) and what I am trying to do is move all rows that contain a date into a separate sheet within the workbook called "JanArchive". "JanArchive" already contains archived entries and I would like to combine the data from "Sheet1" with the data from "JanArchive". Can anyone offer any suggestions how to do this please? Many thanks for your time. Ian Edmont. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving data between worksheets ...
Select cells in sheet which you wish to move. From Menus pick Edit then the cut option (you can also use your right mouse button for the shortcut menus which will bring up these options) Go to sheet where you want the information to appear. Select (or right click) in first availble cell and choose paste. If you are a bit worried that you might loose your information from sheet 1 while doing this, then you could always choose the copy option under edit instead, then go back and delete either the information or the sheet if no longer needed. -- jujuwillis ------------------------------------------------------------------------ jujuwillis's Profile: http://www.excelforum.com/member.php...fo&userid=2123 View this thread: http://www.excelforum.com/showthread...hreadid=502809 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving data between worksheets ...
Try this:
Option Explicit Const csz_dst_sheet As String = "JanArchive" Const csz_src_sheet As String = "Sheet1" Sub movedata() Dim wsd As Worksheet 'dst worksheet Dim wss As Worksheet 'src worksheet Dim rd As Long ' dst row Dim rs As Long ' src row Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet) Set wss = ActiveWorkbook.Worksheets(csz_src_sheet) 'find last row on dst rd = 2 While wsd.Cells(rd, 1) < "" rd = rd + 1 Wend rs = 2 While wss.Cells(rs, 1) < "" If wss.Cells(rs, 2) < "" Then wss.Rows(rs).Copy Destination:=wsd.Rows(rd) rd = rd + 1 End If rs = rs + 1 Wend Set wss = Nothing Set wsd = Nothing End Sub '------------ -- HTHs Martin "Ian Edmont" wrote: Hi, Wonder if someone can help me please? I have a sheet in a workbook called "Sheet1" (please see www.iedmont.blogspot.com/) and what I am trying to do is move all rows that contain a date into a separate sheet within the workbook called "JanArchive". "JanArchive" already contains archived entries and I would like to combine the data from "Sheet1" with the data from "JanArchive". Can anyone offer any suggestions how to do this please? Many thanks for your time. Ian Edmont. |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving data between worksheets ...
Thanks for that Martin, it worked fine however it leaves the rows on
Sheet1 in place. Is there a way to delete the rows from Sheet1 after they have been transferred to JanArchive? Many thanks. Ian Edmont. Martin Fishlock wrote: Try this: Option Explicit Const csz_dst_sheet As String = "JanArchive" Const csz_src_sheet As String = "Sheet1" Sub movedata() Dim wsd As Worksheet 'dst worksheet Dim wss As Worksheet 'src worksheet Dim rd As Long ' dst row Dim rs As Long ' src row Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet) Set wss = ActiveWorkbook.Worksheets(csz_src_sheet) 'find last row on dst rd = 2 While wsd.Cells(rd, 1) < "" rd = rd + 1 Wend rs = 2 While wss.Cells(rs, 1) < "" If wss.Cells(rs, 2) < "" Then wss.Rows(rs).Copy Destination:=wsd.Rows(rd) rd = rd + 1 End If rs = rs + 1 Wend Set wss = Nothing Set wsd = Nothing End Sub '------------ -- HTHs Martin "Ian Edmont" wrote: Hi, Wonder if someone can help me please? I have a sheet in a workbook called "Sheet1" (please see www.iedmont.blogspot.com/) and what I am trying to do is move all rows that contain a date into a separate sheet within the workbook called "JanArchive". "JanArchive" already contains archived entries and I would like to combine the data from "Sheet1" with the data from "JanArchive". Can anyone offer any suggestions how to do this please? Many thanks for your time. Ian Edmont. |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving data between worksheets ...
Ian on the second loop modify it as thus:
rs = 2 While wss.Cells(rs, 1) < "" If wss.Cells(rs, 2) < "" Then wss.Rows(rs).Copy Destination:=wsd.Rows(rd) wss.rows(rs).Delete rd = rd + 1 else rs = rs + 1 End If Wend -- HTHs Martin "Ian Edmont" wrote: Thanks for that Martin, it worked fine however it leaves the rows on Sheet1 in place. Is there a way to delete the rows from Sheet1 after they have been transferred to JanArchive? Many thanks. Ian Edmont. Martin Fishlock wrote: Try this: Option Explicit Const csz_dst_sheet As String = "JanArchive" Const csz_src_sheet As String = "Sheet1" Sub movedata() Dim wsd As Worksheet 'dst worksheet Dim wss As Worksheet 'src worksheet Dim rd As Long ' dst row Dim rs As Long ' src row Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet) Set wss = ActiveWorkbook.Worksheets(csz_src_sheet) 'find last row on dst rd = 2 While wsd.Cells(rd, 1) < "" rd = rd + 1 Wend rs = 2 While wss.Cells(rs, 1) < "" If wss.Cells(rs, 2) < "" Then wss.Rows(rs).Copy Destination:=wsd.Rows(rd) rd = rd + 1 End If rs = rs + 1 Wend Set wss = Nothing Set wsd = Nothing End Sub '------------ -- HTHs Martin "Ian Edmont" wrote: Hi, Wonder if someone can help me please? I have a sheet in a workbook called "Sheet1" (please see www.iedmont.blogspot.com/) and what I am trying to do is move all rows that contain a date into a separate sheet within the workbook called "JanArchive". "JanArchive" already contains archived entries and I would like to combine the data from "Sheet1" with the data from "JanArchive". Can anyone offer any suggestions how to do this please? Many thanks for your time. Ian Edmont. |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving data between worksheets ...
Thanks very much Martin. Exactly what I needed.
Ian Edmont. Martin Fishlock wrote: Ian on the second loop modify it as thus: rs = 2 While wss.Cells(rs, 1) < "" If wss.Cells(rs, 2) < "" Then wss.Rows(rs).Copy Destination:=wsd.Rows(rd) wss.rows(rs).Delete rd = rd + 1 else rs = rs + 1 End If Wend -- HTHs Martin "Ian Edmont" wrote: Thanks for that Martin, it worked fine however it leaves the rows on Sheet1 in place. Is there a way to delete the rows from Sheet1 after they have been transferred to JanArchive? Many thanks. Ian Edmont. Martin Fishlock wrote: Try this: Option Explicit Const csz_dst_sheet As String = "JanArchive" Const csz_src_sheet As String = "Sheet1" Sub movedata() Dim wsd As Worksheet 'dst worksheet Dim wss As Worksheet 'src worksheet Dim rd As Long ' dst row Dim rs As Long ' src row Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet) Set wss = ActiveWorkbook.Worksheets(csz_src_sheet) 'find last row on dst rd = 2 While wsd.Cells(rd, 1) < "" rd = rd + 1 Wend rs = 2 While wss.Cells(rs, 1) < "" If wss.Cells(rs, 2) < "" Then wss.Rows(rs).Copy Destination:=wsd.Rows(rd) rd = rd + 1 End If rs = rs + 1 Wend Set wss = Nothing Set wsd = Nothing End Sub '------------ -- HTHs Martin "Ian Edmont" wrote: Hi, Wonder if someone can help me please? I have a sheet in a workbook called "Sheet1" (please see www.iedmont.blogspot.com/) and what I am trying to do is move all rows that contain a date into a separate sheet within the workbook called "JanArchive". "JanArchive" already contains archived entries and I would like to combine the data from "Sheet1" with the data from "JanArchive". Can anyone offer any suggestions how to do this please? Many thanks for your time. Ian Edmont. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Associating data across worksheets from an inventory that's sorted | Excel Discussion (Misc queries) | |||
working with data between worksheets | Excel Discussion (Misc queries) | |||
Unique and duplicate data between 2 Excel worksheets | Excel Worksheet Functions | |||
Moving data between worksheets | Excel Worksheet Functions | |||
data entry on multiple worksheets | Excel Discussion (Misc queries) |