VBA to Copy Sheet Based on Cell Drop Down to matching Sheet Name

[Hawkeye_28]

Hi,

I have created a new forecast model (Sheet Name: New FC), which i will need to update periodically. Once i have submitted this copy i want to copy and paste the sheet as values to the respective period sheet name i.e. 3+9, 4+8, 5+7, 6+6, 7+5,8+4, 9+3,10+2, 11+1, based on a drop down field located in cell B4 in "New FC" sheet. I will then be able to always have a copy of my submission - it would be good to have a time stamp on when it was copied and pasted to each respective sheet.

I have been trying to amend a code i found on the message boards which is applicable for searching rows and pasting them into their respective sheets, however i cannot figure out how to just simply paste the whole sheet into the correct existing sheet based on the drop down value;

[CODE=vba]
Sub Do_it()

Dim rs As Worksheet
Set rs = Worksheets("Sheet8")

For r = 1 To rs.Range("A" & Rows.Count).End(xlUp).Row
wsName = rs.Cells(r, "A")

If WorksheetFunction.IsErr(Evaluate("'" & wsName & "'!A1")) = "False" Then 'worksheet exists write data
    wr = Worksheets(wsName).Range("A" & Rows.Count).End(xlUp).Row + 1
    rs.Rows(r).Copy Destination:=Worksheets(wsName).Range("A" & wr)
End If

Next r

MsgBox "Done"

End Sub

[/CODE]

Any help much appreciated