+ Reply to Thread
Results 1 to 6 of 6

Macro to Auto save a backup copy in a seperate location OR save file with a pop up

  1. #1
    Registered User
    Join Date
    06-12-2014
    Posts
    3

    Macro to Auto save a backup copy in a seperate location OR save file with a pop up

    I am not GREAT at Macros, so I will try and explain what I need the best I can.

    We have a workbook that has a macro already with a Live Clock that calculates the job times etc. It is on a Server for remote access and used by many people 1 at a time. I look in the file at the data to send intervention emails regarding the times etc. The problem, is that the user does not SAVE the file but every couple of hours so I am not seeing live data. We cannot force a save without a pop up of sometimes, which would be the best bet, but I am unsure how to do that.

    Another option is to Autosave a backup file every so many minutes that I could view remotely and use to send the intervention emails without disturbing their copy. Any ideas?

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,479

    Re: Macro to Auto save a backup copy in a seperate location OR save file with a pop up

    This code will autosave a backup copy of your workbook every 10 minutes while it is open.

    In your workbook, copy this code into the codemodule of the Thisworkbook object:

    Please Login or Register  to view this content.
    And then in a standard codemodule, use thiscode, but change Z:\FolderName\Backup of workbook X to the path and name that you want for the backup file. Take out the Fromat(Now part if you just want one backup file instead of many date-stamped files..
    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    06-12-2014
    Posts
    3

    Re: Macro to Auto save a backup copy in a seperate location OR save file with a pop up

    Thank you for your help! I got this to work but it took away the live clock macro. I guess I need to learn how to do it without taking that one away. I appreciate your reply! thanks again!

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,479

    Re: Macro to Auto save a backup copy in a seperate location OR save file with a pop up

    You can combine the two - use the live clock macro to do the saving when the minutes are and even multiple of ten - set a flag so that the macro only saves once, then reset the flag when the minutes are not even multiples of ten.... post your code if you need help with the logic.

  5. #5
    Registered User
    Join Date
    04-30-2013
    Location
    texas
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro to Auto save a backup copy in a seperate location OR save file with a pop up

    the book itself has a mess of macros.. it is 3270 KB so it will not let me attach it to show you.

    in Workbook I have :
    Private Sub Workbook_Open()



    Recalc



    End Sub


    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Disable

    End Sub

    Module 1 is:
    Dim SchedRecalc As Date

    Sub Recalc()

    With Sheet36.Range("B1")


    .Value = Format(Time, "hh:mm:ss AM/PM")

    End With

    Call SetTime

    End Sub

    Sub SetTime()

    SchedRecalc = Now + TimeValue("00:00:30")

    Application.OnTime SchedRecalc, "Recalc"

    End Sub

    Sub Disable()

    On Error Resume Next

    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False

    End Sub

    Then each sheet has its own: (there is 31 tabs, 1 for each day of the month)

    '==========================
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Set cboTemp = ws.OLEObjects("TempCombo")
    On Error Resume Next
    With cboTemp
    'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    End With
    On Error GoTo errHandler
    If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    Cancel = True
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
    'show the combobox with the list
    .Visible = True
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width + 5
    .Height = Target.Height + 5
    .ListFillRange = str
    .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown

    End If

    errHandler:
    Application.EnableEvents = True
    Exit Sub

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,479

    Re: Macro to Auto save a backup copy in a seperate location OR save file with a pop up

    Add this to the top of your module:

    Public boolSaved As Boolean

    And then change your Recalc sub to this:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-30-2013
    Location
    texas
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro to Auto save a backup copy in a seperate location OR save file with a pop up

    Thank you So much for your help Bernie! That worked perfect.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy, edit and save workbook to same location as source data file, not macro file.
    By Jasonhouse in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2014, 09:01 AM
  2. [SOLVED] Auto save backup macro?
    By alfgrey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-15-2013, 08:42 AM
  3. Creating macro to save a backup copy of the same file in a shared drive
    By Kiran9 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-29-2011, 07:15 AM
  4. Click a button, auto-save a copy of the .XLS file to a specified location?
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-03-2011, 12:45 PM
  5. Using Macro to Save Copy of File to New Location
    By Chris Z in forum Excel General
    Replies: 1
    Last Post: 07-05-2006, 12:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1