Move data from on sheet to another based on month

[pauluk]

Hi All,

this what i need i have the main sheet, which all the data is placed o
to, then sheets for each month, what i want is when data is placed int
the main sheet and the date is entered it copies that row into th
coresponding sheet.

so ie colum f contains the date if it is say 20/7/04 then it copie
that row into the next avaible row of the july sheet.

Thank

 

[Ron de Bruin]

Hi pauluk

Try this

There is no error checking to see if there is a date in A1 or if the sheet exist

With Sheet Input as main sheet
And with 12 sheets with a name 1,2,3...................12

This example will look at the date in A1 and copy the whole row in the month sheet


Sub copy()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Dim SHname As String
SHname = Month(Sheets("Input").Range("A1").Value)
Lr = LastRow(Sheets(SHname)) + 1
Set sourceRange = Sheets("Input").Rows("1:1")
Set destrange = Sheets(SHname).Rows(Lr). _
Resize(sourceRange.Rows.Count)
destrange.Value = sourceRange.Value
End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function