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.

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.

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.

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.

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.