Macro Copy Sheet to a New Named File

[pong1092]

I am trying to copy a sheet automatically to a new file and name it a certain way. I want to repeat this function every 24 hours but for testing purposes I set it to 5 minutes. I have found most of this code from this site and haven't been able to get it to work in the way that I need.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
For information purpose sheet1 is a sheet that brings in data through an Add in (PI data link). I want to then paste the values from sheet 1 to sheet 2. This part is working fine. I want to paste to an additional sheet so I can keep my formatting that I want which I lose when I just paste values in a macro. The Macro then saves the entire workbook to a file. I want to save just sheet 2 which has the values. So far it saves the entire workbook. Because I want to use this for archival purposes I want to get rid of sheet 1 and just save sheet 2. What am I missing on how to save just the sheet?<o></o>
Thanks.
<o></o>
My code is

Option Explicit
Private Sub Workbook_Open()
    AutoSave 'Start Autosaving
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim NextSaveTime As String
    'Cancel next save-call.
    NextSaveTime = GetSetting("MyAppName", "AutoSave", "SaveNext")
    On Error Resume Next
    Application.OnTime CDate(NextSaveTime), "ThisWorkbook.AutoSave", schedule:=False
End Sub
Private Sub AutoSave()
    Dim strFileName As String
    Dim dtTime As Date
    Application.Calculate
    'Copy SHEET 1 to SHEET 2 As Values only
        Range("A1:ZZ100000").Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'END Copy
 
    'Create file name.
    strFileName = "C:\test\" & Format$(Now, "dd-mmm-yy hh-mm-ss ") & ThisWorkbook.Name
    ThisWorkbook.SaveCopyAs strFileName
 
    'Record current auto-saved filename in registry
    SaveSetting "MyAppName", "AutoSave", "PreviousFilename", strFileName
    'Set Time interval to 20 minutes:
    dtTime = DateAdd("n", 20, Now) 'Get next save-time
    'This will be used later to cancel:
    SaveSetting "MyAppName", "AutoSave", "SaveNext", dtTime
    'Schedule next save:
    Application.OnTime dtTime, "ThisWorkbook.AutoSave", schedule:=True
End Sub

 

[taigovinda]

This code would save a copy of the active worksheet, then after saving it will close the new book (without saving again) - maybe you can work it into your code?

Sub test()
Dim mySht As Worksheet, myBook As Workbook
ActiveSheet.Copy
Set mySht = ActiveSheet
Set myBook = mySht.Parent
myBook.SaveAs "c:\....."
myBook.Close False
End Sub

 

[pong1092]

That may work but I am not sure it will be cause I believe the addon when pulling in data and brings it to the top. I'll have to double check that tomorrow when I have access to the addon. Also I know I am copying a lot of lines 100,000. I could cut it down to 86401(How many seconds in a day plus title row) but figured that wouldn't make a huge different for final product I will probably cut the amount copied in half by going to Z100000.