Coping data from one workbook to another based on specific criteria

[AlbinoRyno88]

Hello all,

This is my first post and I have looked all over the internet to find an answer. I am new to VBA and coding so my terminology is not so great, so please bear with me. What I am trying to do is create a macro that will copy some data from one workbook and create a new workbook and paste that data in the new workbook then save the workbook. I want the macro to copy specific data. Below I have my data pasted so you can see what I am trying to do and I have also attached my spreadsheet if you need to look at that as well.

What I am specifically trying to do is have the macro evaluate a certain date in column A (in this case 6/19) and a specific word in column B(Sales). If it meets both criteria ( 6/19 and Sales) I then need it to copy the entire column and paste into a new worksheet and save it. Right now when I execute the macro it just copies the first line (line 2) and then pastes it correctly.


Sub mysales()
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

If Cells(i, 1) = "6/19" And Cells(i, 2) = "Sales" Then

Range(Cells(i, 1), Cells(i, 50)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\ryanb\Downloads\Nightbook.xlsm"
Worksheets("Sheet2").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If
Next i
End Sub

Thank you in advance for you help!
Ryno

[mancubus]

Sub vbax_59958_copy_rows_based_on_condition()

With ThisWorkbook.Worksheets("Sheet1")
.Cells(1).AutoFilter 1, "6/19"
.Cells(1).AutoFilter 2, "Farmland"
If .AutoFilter.Range.Rows.Count > 1 Then
.UsedRange.Offset(1).SpecialCells(12).EntireRow.Copy
Workbooks.Open "C:\Users\ryanb\Downloads\Nightbook.xlsm"
With ActiveWorkbook
.Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial
.Save
End With
End If
.AutoFilterMode = False
End With

End Sub