copy and paste data with month criteria

Hi All,

I want to organize my data set with the criteria of data (which spans over different months of each year. The data which i am having is of the following structure.

Date Contract Months Price Near Middle Far
The input data is of the form:-
Bid Date : Contract Month :Price
Jan-Jan-Price
Jan-Feb-Price
Feb-Feb-Price
Jan-March-Price
Feb-March-Price
March-March-Price
Feb-April-Price
March-April-Price
April-April-Price.....

The date column has the all the dates for various years. I want the data to be filtered with the following criteria. and then paste to the column Near Middle Far columns respectively. The criteria for the Near Middle and Far follows:

Near Middle Far
January February March
February March April
March April May
April May June
May June July
June July Aug
July Aug Sept
Aug Sept October
Sept October Nov
October Nov Dec
November Dec Jan
Dec Jan Feb

The above has to be followed till the end of the Date in the Date column.

Any thoughts most graciously accepted.

Thanks.
Upananda

Hi All,

May i know whether the thread is confusing in posing the problem. I need your help to know where i am wrong.

With sincere regards,
Upananda

Hi All,

I got the solution from the following code. I was feeling guilty of not posting clearly so i am posting the solution.

Sub FormatIT()

Dim mytst As Range
Dim lastRow As Single
Set mytst = Sheets("Master Sheet").Range("A1").End(xlDown)
lastRow = mytst.Row

'Dim lastCol As Single
'Set mytst = Sheets("Master Sheet").Range("A1").End(xlToRight)
'lastCol = mytst.Column

Dim firstDate, lastDate As Date

firstDate = Sheets("Master Sheet").Cells(2, 3).Value
lastDate = Sheets("Master Sheet").Cells(lastRow, 3).Value

Dim i, m1, m2, k1, k2, k3, j As Integer
k1 = 2
k2 = 2
k3 = 2
Dim k4 As Integer
k4 = 2

Sheets("Formatted").Cells.Clear

Sheets("Formatted").Cells(1, 1).Value = "Near Contract Month"
Sheets("Formatted").Cells(1, 2).Value = "Date"
Sheets("Formatted").Cells(1, 3).Value = "Near Month Price"

Sheets("Formatted").Cells(1, 5).Value = "Middle Contract Month"
Sheets("Formatted").Cells(1, 6).Value = "Date"
Sheets("Formatted").Cells(1, 7).Value = "Middle Month Price"

Sheets("Formatted").Cells(1, 9).Value = "Far Contract Month"
Sheets("Formatted").Cells(1, 10).Value = "Date"
Sheets("Formatted").Cells(1, 11).Value = "Far Month Price"

Sheets("Formatted").Cells(1, 13).Value = "ERROR"
Sheets("Formatted").Cells(1, 14).Value = "ERROR"
Sheets("Formatted").Cells(1, 15).Value = "ERROR"



For i = 2 To lastRow
m1 = Sheets("Master Sheet").Cells(i, 1).Value
m2 = Sheets("Master Sheet").Cells(i, 3).Value

If Month(m2) Mod 12 = (Month(m1) + 2) Mod 12 Then
Sheets("Formatted").Cells(k1, 9).Value = m2
Sheets("Formatted").Cells(k1, 10).Value = m1
Sheets("Formatted").Cells(k1, 11).Value = Sheets("Master Sheet").Cells(i, 4).Value
k1 = k1 + 1
Else
If Month(m2) Mod 12 = (Month(m1) + 1) Mod 12 Then
Sheets("Formatted").Cells(k2, 5).Value = m2
Sheets("Formatted").Cells(k2, 6).Value = m1
Sheets("Formatted").Cells(k2, 7).Value = Sheets("Master Sheet").Cells(i, 4).Value
k2 = k2 + 1
Else
If Month(m2) = Month(m1) Then
Sheets("Formatted").Cells(k3, 1).Value = m2
Sheets("Formatted").Cells(k3, 2).Value = m1
Sheets("Formatted").Cells(k3, 3).Value = Sheets("Master Sheet").Cells(i, 4).Value
k3 = k3 + 1
Else
Sheets("Formatted").Cells(k4, 13).Value = m2
Sheets("Formatted").Cells(k4, 14).Value = m1
Sheets("Formatted").Cells(k4, 15).Value = Sheets("Master Sheet").Cells(i, 4).Value
k4 = k4 + 1
End If
End If

End If



Next i










End Sub