Macro copying Column Range instead of individual Column

[GuardianEnzo]

I've written a basic macro with extracts records based on two dates:

Sub extractDataBasedOnDate()


Dim lastrow As Long, erow As Long, i As Long
Dim mydate As Date
Dim StartDate As Date
Dim EndDate As Date


StartDate = Worksheets("Expense Report").Cells(3, 1).Value
EndDate = Worksheets("Expense Report").Cells(3, 2).Value
lastrow = Worksheets("Expenses").Cells(Rows.Count, 1).End(xlUp).Row


Worksheets("Expense Report").Rows(7 & ":" & Sheet2.Rows.Count).Delete


Worksheets("Expenses").Activate
Worksheets("Expenses").Range("A1").Select


For i = 2 To lastrow


    mydate = Cells(i, 6)
    If mydate >= StartDate And mydate <= EndDate Then
    erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Range(Cells(i, 2), Cells(i, 6)).Copy Destination:=Sheets("Expense Report").Cells(erow, 1)
    End If
Next i


End Sub

The problem I am having is that it is returning every column between Cells(i, 2) to Cells(i , 6).
I had intended to only copy the two columns (Column B and F), how can I achieve this?
Do I need to do a separate line for each column?


Thanks!

 

[My Aswer Is This]

Try this:

Sub extractDataBasedOnDate()

Dim lastrow As Long, erow As Long, i As Long
Dim mydate As Date
Dim StartDate As Date
Dim EndDate As Date

StartDate = Worksheets("Expense Report").Cells(3, 1).Value
EndDate = Worksheets("Expense Report").Cells(3, 2).Value
lastrow = Worksheets("Expenses").Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("Expense Report").Rows(7 & ":" & Sheet2.Rows.Count).Delete

Worksheets("Expenses").Activate
Worksheets("Expenses").Range("A1").Select

For i = 2 To lastrow

    mydate = Cells(i, 6)
    If mydate >= StartDate And mydate <= EndDate Then
    erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    'New
    
    Application.Union(Cells(i, 2), Cells(i, 6)).Copy Destination:=Sheets("Expense Report").Cells(erow, 1)
    End If
Next i

End Sub

 

[GuardianEnzo]

Wow that worked!

Thanks a lot, I'll have to keep a note of the Application.Union function.

Cheers

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Wow that worked!

Thanks a lot, I'll have to keep a note of the Application.Union function.

Cheers