Copy data between 2 dates

[tigerdel]

Hi Guys
What I am trying to do here is to copy data from a sheet [called Full Bank Statement] that is between 2 dates held in A2 & B2 of the sheet and where the data meets the criteria, copy from columns 1 - 6 into the first empty cells of the sheet [January]
I only want to copy the cells in those columns as I have formulas in the cells from column 7 onwards.
The code I have seems to work until the last row beginning "Sheets" where I get the error:

Run-time error 1004
Application-defined or object-defined error

The Code I created was:

Sub JanuaryData()
Dim lastrow As Long
Dim erow As Long
Dim i As Long
Dim mydate As Date
lastrow = Sheets("Full Bank Statement").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Full Bank Statement").Range("A3").Select
For i = 4 To lastrow
mydate = Cells(i, 1)
If mydate >= Sheets("Full Bank Statement").Range("A2") And mydate <= Sheets("Full Bank Statement").Range("B2") Then erow = *_ Sheets("January").cellls(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheets("Full Bank Statement").Range(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 5), Cells(i, 6)).Copy Destination:=Sheets("January").Cells(erow, 1)
Next i
End Sub

Any help would be gratefully received
Derek

 

[DanteAmor]

Try this:

Sub JanuaryData()
    Dim lastrow As Long, erow As Long, i As Long
    Dim mydate As Date, dat1 As Date, dat2 As Date
    Dim ws As Worksheet
    
    Set ws = Sheets("Full Bank Statement")
    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    dat1 = ws.Range("A2")
    dat2 = ws.Range("B2")
    
    For i = 4 To lastrow
        mydate = ws.Cells(i, 1)
        If mydate >= dat1 And mydate <= dat2 Then
            erow = Sheets("January").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            ws.Range([COLOR=#0000ff]ws.Cells(i, 1), ws.Cells(i, 6)[/COLOR]).Copy Destination:=Sheets("January").Cells(erow, 1)
        End If
    Next i
    MsgBox "End"
End Sub

 

[tigerdel]

[SOLVED} Re: Copy data between 2 dates

Dante Amor

Thank you

Works perfectly

Derek

 

[DanteAmor]

Re: [SOLVED} Re: Copy data between 2 dates

You're welcome, thanks for the feedback.