There are some posts dealing with archiving worksheets to an archive workbook but most assume all sheets or all workbooks get archived. I need a more selective macro that I am hoping someone can assist me with.
I have the following code:
it copies the race sheet datra across to RaceArchive Ok but doesnt name the tab with the RDate variable as I want it to.
Also, how do I get it to create a new sheet, each time I run the archive macro - not just overwrite Sheet1?
Hope some one can help
I have the following code:
Code:
Sub ArchiveResults()
Dim MyPath As Variant
Dim RDate As Date
Dim ws As Worksheet
On Error GoTo error_handler
Application.ScreenUpdating = False
'Gets the race date - this is a drop down event date in cell C2
RDate = Range("c2").Value
'sets the archive workbook being "RaceArchive.xlsm"
MyPath = ActiveWorkbook.Path & "\" & "RaceArchive.xlsm"
Workbooks.Open Filename:=MyPath
With Workbooks("Race_Sheet.xlsm").Sheets("Race sheet")
.UsedRange.Copy Workbooks("RaceArchive.xlsm").Sheets("Sheet1").Range("a1")
End With
Worksheet.Name = RDate
Application.ScreenUpdating = True
Exit Sub
error_handler:
End Sub
it copies the race sheet datra across to RaceArchive Ok but doesnt name the tab with the RDate variable as I want it to.
Also, how do I get it to create a new sheet, each time I run the archive macro - not just overwrite Sheet1?
Hope some one can help