I have two worksheets, "Signed" and "April". I want to copy Column "Y" from "Signed" based on certain criteria into column "A" starting from the next available/blank row. ( so right under the existing data).
My criteria for column Y is that if column L = month of cell "D2" from "April" AND the year of cell "D2" from "ApriL"...( so right now D2 is 4/30/2017).. then copy that cell in the next available row of Col A of "April" and keep adding on.
my code is as follows:
Dim sourceSht As Worksheet
Dim myrange As Range
Dim DestRow As Integer
Dim ws2 as Worksheet
Range("D3").Select
ActiveCell.FormulaR1C1 ="=MONTH(R[-1]C)"
Range("D3").Select
Selection.NumberFormat ="General"
MonthVal = ActiveCell.Value
Set sourceSht = ThisWorkbook.Worksheets("Signed")
Set myrange = sourceSht.Range("Y1", Range("Y" & Rows.Count).End(xlUp))
Set ws2 = Sheets(NewSheet)
DestRow = ws2.Cells(Rows.Count,"A").End(xlUp).Row + 1
For each rw in myrange.Rows
If rw.Cells(12).Value = Month(Range("D2")) Then
myrange.Value.Copy Destinations:=Sheets(ws2).Range("A" & DestRow)
End If
Bookmarks