Moving data between worksheets ...

[Ian Edmont]

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.

[Martin Fishlock]

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.
>
>

[Ian Edmont]

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.
> >
> >

[Martin Fishlock]

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.
> > >
> > >
>
>

[Ian Edmont]

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.
> > > >
> > > >
> >
> >