Copy Paste row from one worksheet to another based on DATE

Hey All,

I am new to VBA and would really appreciate some help with a macro to carry out the following activity for me.

Please refer to the attached sample data for clarity.

1. I need the macro to copy the data in the worksheet" Test1" to another worksheet based on date.(column A)

2. If the data is above the year 2010, then copy that entire row to new worksheet and if its below or equal to the year 2010, then copy it to another worksheet.

The header remains same for all worksheets. If possible I would like the new worksheet with 2011 data to be named "2011" and worksheet with 2010 and below data , named as "2010"

3. If i want to create a PIVOT out of each of the two new sheets taking only the 1,2 and 4th row.. is that possible?

Thanks a lot for your help.

Hey,

I have got an initial breakthru with the code:

Sub Copy()

Dim rngStart As Range
Dim rngEnd As Range
Dim i As Integer


Set rngStart = Range("A2")

Set rngEnd = Range("A" & CStr(Application.Rows.Count)).End(xlUp)

For i = rngEnd.Row To rngStart.Row Step -1

If DateValue((Cells(i, "A").Value)) > DateValue("31/12/2010") Then

Cells(i, "A").EntireRow.Copy Worksheets("2011").Range("A65536").End(xlUp).Offset(1, 0)
Else

Cells(i, "A").EntireRow.Copy Worksheets("2010").Range("A65536").End(xlUp).Offset(1, 0)

End If
Next i
End Sub


the above code sorts the data into new worksheets according to date criteria. Only thing left is that it does not automatically creates new worksheet with the tab names. We have to manually create the worksheets before running the macro. So I wanna know what needs to be done to create the worksheets automatically.

Thanks

try this at the top of your code and then always refert to newSH object

Please Login or Register  to view this content.

Best Regards\
MaczaQ