The code below transfers data from one workbook book to another.The source workbook has seven sheets "TUES ,WED,THURS,FRI,SAT,SUN,MON"
The destination workbook Called "history File at Present has only one sheet called "Sheet 1" I would like to change the history file so it also has seven sheets i named the same as the soruce workbook ie: TUES,WED etc Where I need the help is to alter the code below so if the macro is run when the active sheet in the source file is say WED it will paste the data for that day in the corresponding sheet in the history filei e WED
Thanks for any help
[VBA]
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
[/VBA]