macro to save to new workbook and clear sheet

How would I go about adding a button and macro to this attached file that will allow me to save all the data on the Readings sheet to a new workbook/ save it as date done and close and then clear the data in the Readings sheet. Is it possible to do this without interupting the active macro that populates the Readings Sheet?

Thanks,

Martin

Hi add a button to your worksheet and paste in the following code in to its code module:

Sheets("Readings").Copy
ActiveSheet.SaveAs ActiveSheet.Name & Format(Date, "dd mmm yyyy")
ActiveWorkbook.Close

This will save the readings worksheet as "Readings" and todays date in My Documents!

Regards,
Simon

Thank you Simon.

I'll give it a try..

Martin

Works great, Thanks again Simon. One questions, is there a way to save the readings sheet and not have the Macro that is on the sheet saved with it. ie the readings sheets has this:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range

   For Each Rng In Target
      If Not Application.Intersect(Columns(Rng.Column), Columns(6)) Is Nothing Then
         Application.EnableEvents = False
         Cells(Rng.Row, "A").Value = Now()
         Application.EnableEvents = True
      End If
   Next Rng
   Application.EnableEvents = True
End Sub

It freaks the operators out when they go to open the saved sheet and they are not sure what to-do. Can I add the code to a module so its not attached to the workbook when saved?

Thanks,

Martin

Martin the code below is not mine, you need to make a backup copy of your work first just in case put it in a standard module!
The macro below can be used to delete Sheet Code based on the Sheet CodeNames supplied to the Select Case Statement within the Sheets Collection Loop . It's ideal to use when you need to copy Sheets from a Workbook but do not want the code to copy with the Sheets.

You will have to go to Tools>Macro>Security - Trusted Publishers and check Trust access to Visual Basic Editor before running the code

Sub DeleteSheetEventCode()
''Needs Reference Set To _
    "Microsoft Visual Basic For Applications Extensibility"
'Tools>References.
Dim sSheet As Object, strName As String

        For Each sSheet In Sheets
        
            Select Case UCase(sSheet.Name)
                Case "SHEET1", "SHEET2", "SHEET3"
                    strName = sSheet.CodeName
                    
                    With ThisWorkbook.VBProject.VBComponents(strName).CodeModule
                            .DeleteLines 1, .CountOfLines
                    End With
                    
                Case Else
                   'Whatever
            End Select
            
        Next sSheet
End Sub

of cousre substitute your sheet(s) for the ones shown here!

Regards,
Simon

P.S you might want to look here http://www.cpearson.com/excel/vbe.htm Chip has great worked examples!