software

Hi,

Wonder if someone can help me please?

I have a sheet in a workbook called quot;Sheet1quot; (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
quot;JanArchivequot;.

quot;JanArchivequot; already contains archived entries and I would like to
combine the data from quot;Sheet1quot; with the data from quot;JanArchivequot;.

Can anyone offer any suggestions how to do this please?

Many thanks for your time.

Ian Edmont.
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: www.excelforum.com/member.php...foamp;userid=2123
View this thread: www.excelforum.com/showthread...hreadid=502809Try this:

Option Explicit

Const csz_dst_sheet As String = quot;JanArchivequot;
Const csz_src_sheet As String = quot;Sheet1quot;

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) lt;gt; quot;quot;
rd = rd 1
Wend

rs = 2
While wss.Cells(rs, 1) lt;gt; quot;quot;
If wss.Cells(rs, 2) lt;gt; quot;quot; 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 Martinquot;Ian Edmontquot; wrote:

gt; Hi,
gt;
gt; Wonder if someone can help me please?
gt;
gt; I have a sheet in a workbook called quot;Sheet1quot; (please see
gt; www.iedmont.blogspot.com/) and what I am trying to do is move all rows
gt; that contain a date into a separate sheet within the workbook called
gt; quot;JanArchivequot;.
gt;
gt; quot;JanArchivequot; already contains archived entries and I would like to
gt; combine the data from quot;Sheet1quot; with the data from quot;JanArchivequot;.
gt;
gt; Can anyone offer any suggestions how to do this please?
gt;
gt; Many thanks for your time.
gt;
gt; Ian Edmont.
gt;
gt;

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:

gt; Try this:
gt;
gt; Option Explicit
gt;
gt; Const csz_dst_sheet As String = quot;JanArchivequot;
gt; Const csz_src_sheet As String = quot;Sheet1quot;
gt;
gt; Sub movedata()
gt; Dim wsd As Worksheet 'dst worksheet
gt; Dim wss As Worksheet 'src worksheet
gt; Dim rd As Long ' dst row
gt; Dim rs As Long ' src row
gt;
gt; Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet)
gt; Set wss = ActiveWorkbook.Worksheets(csz_src_sheet)
gt;
gt; 'find last row on dst
gt; rd = 2
gt; While wsd.Cells(rd, 1) lt;gt; quot;quot;
gt; rd = rd 1
gt; Wend
gt;
gt; rs = 2
gt; While wss.Cells(rs, 1) lt;gt; quot;quot;
gt; If wss.Cells(rs, 2) lt;gt; quot;quot; Then
gt; wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
gt; rd = rd 1
gt; End If
gt; rs = rs 1
gt; Wend
gt; Set wss = Nothing
gt; Set wsd = Nothing
gt; End Sub
gt; '------------
gt; --
gt; HTHs Martin
gt;
gt;
gt; quot;Ian Edmontquot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; Wonder if someone can help me please?
gt; gt;
gt; gt; I have a sheet in a workbook called quot;Sheet1quot; (please see
gt; gt; www.iedmont.blogspot.com/) and what I am trying to do is move all rows
gt; gt; that contain a date into a separate sheet within the workbook called
gt; gt; quot;JanArchivequot;.
gt; gt;
gt; gt; quot;JanArchivequot; already contains archived entries and I would like to
gt; gt; combine the data from quot;Sheet1quot; with the data from quot;JanArchivequot;.
gt; gt;
gt; gt; Can anyone offer any suggestions how to do this please?
gt; gt;
gt; gt; Many thanks for your time.
gt; gt;
gt; gt; Ian Edmont.
gt; gt;
gt; gt;Ian on the second loop modify it as thus:

rs = 2
While wss.Cells(rs, 1) lt;gt; quot;quot;
If wss.Cells(rs, 2) lt;gt; quot;quot; Then
wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
wss.rows(rs).Delete
rd = rd 1
else
rs = rs 1
End If
Wend

--
HTHs Martinquot;Ian Edmontquot; wrote:

gt; Thanks for that Martin, it worked fine however it leaves the rows on
gt; Sheet1 in place.
gt;
gt; Is there a way to delete the rows from Sheet1 after they have been
gt; transferred to JanArchive?
gt;
gt; Many thanks.
gt;
gt; Ian Edmont.
gt;
gt;
gt; Martin Fishlock wrote:
gt;
gt; gt; Try this:
gt; gt;
gt; gt; Option Explicit
gt; gt;
gt; gt; Const csz_dst_sheet As String = quot;JanArchivequot;
gt; gt; Const csz_src_sheet As String = quot;Sheet1quot;
gt; gt;
gt; gt; Sub movedata()
gt; gt; Dim wsd As Worksheet 'dst worksheet
gt; gt; Dim wss As Worksheet 'src worksheet
gt; gt; Dim rd As Long ' dst row
gt; gt; Dim rs As Long ' src row
gt; gt;
gt; gt; Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet)
gt; gt; Set wss = ActiveWorkbook.Worksheets(csz_src_sheet)
gt; gt;
gt; gt; 'find last row on dst
gt; gt; rd = 2
gt; gt; While wsd.Cells(rd, 1) lt;gt; quot;quot;
gt; gt; rd = rd 1
gt; gt; Wend
gt; gt;
gt; gt; rs = 2
gt; gt; While wss.Cells(rs, 1) lt;gt; quot;quot;
gt; gt; If wss.Cells(rs, 2) lt;gt; quot;quot; Then
gt; gt; wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
gt; gt; rd = rd 1
gt; gt; End If
gt; gt; rs = rs 1
gt; gt; Wend
gt; gt; Set wss = Nothing
gt; gt; Set wsd = Nothing
gt; gt; End Sub
gt; gt; '------------
gt; gt; --
gt; gt; HTHs Martin
gt; gt;
gt; gt;
gt; gt; quot;Ian Edmontquot; wrote:
gt; gt;
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; Wonder if someone can help me please?
gt; gt; gt;
gt; gt; gt; I have a sheet in a workbook called quot;Sheet1quot; (please see
gt; gt; gt; www.iedmont.blogspot.com/) and what I am trying to do is move all rows
gt; gt; gt; that contain a date into a separate sheet within the workbook called
gt; gt; gt; quot;JanArchivequot;.
gt; gt; gt;
gt; gt; gt; quot;JanArchivequot; already contains archived entries and I would like to
gt; gt; gt; combine the data from quot;Sheet1quot; with the data from quot;JanArchivequot;.
gt; gt; gt;
gt; gt; gt; Can anyone offer any suggestions how to do this please?
gt; gt; gt;
gt; gt; gt; Many thanks for your time.
gt; gt; gt;
gt; gt; gt; Ian Edmont.
gt; gt; gt;
gt; gt; gt;
gt;
gt;

Thanks very much Martin. Exactly what I needed.

Ian Edmont.Martin Fishlock wrote:

gt; Ian on the second loop modify it as thus:
gt;
gt; rs = 2
gt; While wss.Cells(rs, 1) lt;gt; quot;quot;
gt; If wss.Cells(rs, 2) lt;gt; quot;quot; Then
gt; wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
gt; wss.rows(rs).Delete
gt; rd = rd 1
gt; else
gt; rs = rs 1
gt; End If
gt; Wend
gt;
gt; --
gt; HTHs Martin
gt;
gt;
gt; quot;Ian Edmontquot; wrote:
gt;
gt; gt; Thanks for that Martin, it worked fine however it leaves the rows on
gt; gt; Sheet1 in place.
gt; gt;
gt; gt; Is there a way to delete the rows from Sheet1 after they have been
gt; gt; transferred to JanArchive?
gt; gt;
gt; gt; Many thanks.
gt; gt;
gt; gt; Ian Edmont.
gt; gt;
gt; gt;
gt; gt; Martin Fishlock wrote:
gt; gt;
gt; gt; gt; Try this:
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt;
gt; gt; gt; Const csz_dst_sheet As String = quot;JanArchivequot;
gt; gt; gt; Const csz_src_sheet As String = quot;Sheet1quot;
gt; gt; gt;
gt; gt; gt; Sub movedata()
gt; gt; gt; Dim wsd As Worksheet 'dst worksheet
gt; gt; gt; Dim wss As Worksheet 'src worksheet
gt; gt; gt; Dim rd As Long ' dst row
gt; gt; gt; Dim rs As Long ' src row
gt; gt; gt;
gt; gt; gt; Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet)
gt; gt; gt; Set wss = ActiveWorkbook.Worksheets(csz_src_sheet)
gt; gt; gt;
gt; gt; gt; 'find last row on dst
gt; gt; gt; rd = 2
gt; gt; gt; While wsd.Cells(rd, 1) lt;gt; quot;quot;
gt; gt; gt; rd = rd 1
gt; gt; gt; Wend
gt; gt; gt;
gt; gt; gt; rs = 2
gt; gt; gt; While wss.Cells(rs, 1) lt;gt; quot;quot;
gt; gt; gt; If wss.Cells(rs, 2) lt;gt; quot;quot; Then
gt; gt; gt; wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
gt; gt; gt; rd = rd 1
gt; gt; gt; End If
gt; gt; gt; rs = rs 1
gt; gt; gt; Wend
gt; gt; gt; Set wss = Nothing
gt; gt; gt; Set wsd = Nothing
gt; gt; gt; End Sub
gt; gt; gt; '------------
gt; gt; gt; --
gt; gt; gt; HTHs Martin
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ian Edmontquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi,
gt; gt; gt; gt;
gt; gt; gt; gt; Wonder if someone can help me please?
gt; gt; gt; gt;
gt; gt; gt; gt; I have a sheet in a workbook called quot;Sheet1quot; (please see
gt; gt; gt; gt; www.iedmont.blogspot.com/) and what I am trying to do is move all rows
gt; gt; gt; gt; that contain a date into a separate sheet within the workbook called
gt; gt; gt; gt; quot;JanArchivequot;.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;JanArchivequot; already contains archived entries and I would like to
gt; gt; gt; gt; combine the data from quot;Sheet1quot; with the data from quot;JanArchivequot;.
gt; gt; gt; gt;
gt; gt; gt; gt; Can anyone offer any suggestions how to do this please?
gt; gt; gt; gt;
gt; gt; gt; gt; Many thanks for your time.
gt; gt; gt; gt;
gt; gt; gt; gt; Ian Edmont.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;