Macro that copies and pastes according to range

[Brand_New2VBA]

Hi Guys,

I'm brand new to VBA and I need some help completeing a code I have.

I need certain information to be copied from one sheet and pasted in sheet 4 according to month. So--for instance-- on sheet one i want the information copied (persons name and date of birth) to be pasted in sheet four under the specific month of birth. sheet two has several columns of merged cells that are names by month. I want the macro to tell excel to copy the information from sheet one and paste it under the merged range according to the person's month of birth.

is this possible ? so far this is the code I have (i am using excel 2007):

Private Sub CommandButton1_Click()
Dim wksSource As Worksheet
Dim wksDestination As Worksheet
Dim vntCloneRange As Variant
Dim nextrow As Long

Set wksSource = Sheet1
Set wksDestination = Sheet4

If Range("B4,e4") = "published" Then
vntCloneRange = wksSource.Range("c5:c10")
nextrow = Sheet4.Range("May").AutoFill(Rows.Count, "C").End(xlUp).Row + 3
wksDestination.Range("C" & nextrow).Resize(UBound(vntCloneRange), 1) = vntCloneRange
Erase vntCloneRange
Range("b4:e11").Delete Shift:=xlUp
End sub