Copy and paste to specific Worksheets

[BENSON]

The code below copies data and pastes the values into a workbook called "Gardens History" it works fine.What I would like to do now is to create a code that would then take the pasted data and copy it to specfic worksheets named "TUES,WED,THURS "etc thru to "SUN" in the same workbook.The criteria being the the date ie: if date is TUE 04 DEC 07 the pasted data would appear in the spreadsheet named TUES as well as the exsisting sheet .The ranges in the named week day sheets would have to be dynamic so as to not paste over previously copied data.

Thanks for any guidence


Private Sub Workbook_BeforeClose(Cancel As Boolean)
a = MsgBox("ARE YOU READY TO UPDATE THE HISTORY FILE?", _
vbYesNo)
If a = vbYes Then
Cancel = True
Dim WsTgt As Excel.Worksheet
Dim rngCopy As Excel.Range
Application.ScreenUpdating = False
Set WsTgt = Workbooks("Gardens History.xls").Sheets(1)
With WsTgt.Range("A" & NextEmptyRow(WsTgt))
.Value = Date
.NumberFormat = "ddd dd mmm yy"
' Add C285 and C286
ActiveSheet.Range("C284").Copy
.Offset(, 1).PasteSpecial xlPasteValues
ActiveSheet.Range("C286").Copy
.Offset(, 2).PasteSpecial xlPasteValues
ActiveSheet.Range("C288").Copy
.Offset(, 3).PasteSpecial xlPasteValues

Set rngCopy = ActiveSheet.Range("G260:AZ260")
rngCopy.Copy
.Offset(, 4).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
End With
Else
Rem Cancel = True:Rem If you don't want No=Close
End If
End Sub


Function NextEmptyRow(Wks As Worksheet) As Long
Dim Rng As Range
Set Rng = Wks.Range("A" & Wks.Rows.Count).End(xlUp)
If Rng <> "" Then Set Rng = Rng.Offset(1)
NextEmptyRow = Rng.Row
End Function

Private Sub Workbook_Open()
End Sub

[stanleydgrom]

BENSON,

Is this the range [ActiveSheet.Range("G260:AZ260")] that you want to copy to the "TUES" thru SUN" worksheets?

What beginning cell do you want to copy to in "TUES" thru SUN"?

Have a great day,
Stan

[figment]

this might help

Sub TESTING()
Select Case Weekday(Now, vbMonday)
Case 1
Range("A1") = "Monday"
Case 2
Range("A1") = "Tuesday"
Case 3
Range("A1") = "Wendsday"
Case 4
Range("A1") = "Thursday"
Case 5
Range("A1") = "Friday"
Case 6
Range("A1") = "Saturday"
Case 7
Range("A1") = "sunday"
End Select
End Sub

[BENSON]

the range would be in Gardens history workbook sheet 1 ("A:AX') being the range of the data that has been pasted by the exsisting code,the range is dynamic .From sheet one I would like to seperate the data based on the day which is shown in collum "A" into seven other adjacent worksheets in the same workbook (Gardens History) named "Tues,Wed ,Thurs etc thru to Mon

Thanks