Macros to auto save 'values' only

[Dubseller]

Hey all, I am completely clueless when it comes to macros so would REALLY appreciate some help here. I work in a firm where one computer has an excel workbook with numerous sheets and vlookup values. I would like to do the following:

1. Automatically save a copy of 2 of the sheets from the original workbook into a separate shared folder (so all the members of my firm can access the new workbook).

2. Save only the 'values' on the new workbooks so the vlookups and equations aren't visible (paste special > values)

3. Have the original workbook save the updated values every hour or so.

I would be glad to offer more info... thanks in advance!

 

[VoG]

This should work to save as values

Sub SaveValues()
Dim ws As Worksheet
Sheets(Array("Sheet1", "Sheet2")).Copy
For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
Next ws
With ActiveWorkbook
    .SaveAs Filename:="values.xls"
    .Close
End With
End Sub

To run this at intervals you could use the OnTime method http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[Sektor]

Adjust sheets' names.

[COLOR="Blue"]Sub[/COLOR] DoBackUp()
    [COLOR="Blue"]Dim[/COLOR] this [COLOR="Blue"]As[/COLOR] Workbook
    [COLOR="Blue"]Dim[/COLOR] wkbBackUp [COLOR="Blue"]As[/COLOR] Workbook
    [COLOR="Blue"]Set[/COLOR] this = ActiveWorkbook
    [COLOR="Blue"]Set[/COLOR] wkbBackUp = Workbooks.Add
    [COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]Resume[/COLOR] [COLOR="Blue"]Next[/COLOR]
    this.Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeConstants).Copy wkbBackUp.Sheets(1).Cells(1, 1)
    this.Worksheets("Sheet2").Cells.SpecialCells(xlCellTypeConstants).Copy wkbBackUp.Sheets(2).Cells(1, 1)
        wkbBackUp.SaveAs FileName:="FILE_PATH"
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Dubseller]

This should work to save as values

Sub SaveValues()
Dim ws As Worksheet
Sheets(Array("Sheet1", "Sheet2")).Copy
For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
Next ws
With ActiveWorkbook
    .SaveAs Filename:="values.xls"
    .Close
End With
End Sub

To run this at intervals you could use the OnTime method http://www.ozgrid.com/Excel/run-macro-on-time.htm

I'll try this... how do I add the timing rules so it updates every day? I mean I'm not sure how to put them together.

 

[VoG]

In a regular module

Public dTime As Date

Sub SaveValues()
Dim ws As Worksheet
Sheets(Array("Sheet1", "Sheet2")).Copy
For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
Next ws
With ActiveWorkbook
    Application.DisplayAlerts = False
    .SaveAs Filename:="values.xls"
    .Close
    Application.DisplayAlerts = True
End With
dTime = Now + TimeValue("00:15:00")
Application.OnTime dTime, "SaveValues"
End Sub

In the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)

     Application.OnTime dTime, "SaveValues", , False

End Sub

Private Sub Workbook_Open()

  Application.OnTime Now + TimeValue("00:15:00"), "SaveValues"

End Sub

 

[Bruce_R]

I am using the macro that VoG posted above (2nd post in this topic). It works, for the most part, but I have one column that uses a UDF, and the new file does not contain the values where this UDF is called. I get a #NAME error. The UDF does call the WORKDAY function in the Analysis Tookpak Add-in, which seems like it may be the culprit, based on a Google search. I also pass a named range to the UDF. Any help would be appreciated. I can post more info if required.

 

[greenculture]

it works....

In a regular module

Public dTime As Date

Sub SaveValues()
Dim ws As Worksheet
Sheets(Array("Sheet1", "Sheet2")).Copy
For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
Next ws
With ActiveWorkbook
    Application.DisplayAlerts = False
    .SaveAs Filename:="values.xls"
    .Close
    Application.DisplayAlerts = True
End With
dTime = Now + TimeValue("00:15:00")
Application.OnTime dTime, "SaveValues"
End Sub

In the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)

     Application.OnTime dTime, "SaveValues", , False

End Sub

Private Sub Workbook_Open()

  Application.OnTime Now + TimeValue("00:15:00"), "SaveValues"

End Sub

 

[Bruce_R]

Sub SaveValues()
    Dim ws As Worksheet
    currentpath = ActiveWorkbook.FullName
    newname = (Left(currentpath, Len(currentpath) - 5)) & "_published.xlsx"
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
    For Each ws In ActiveWorkbook.Worksheets
        With ws.UsedRange
            .Value = .Value
        End With
    Next ws
    With ActiveWorkbook
        .SaveAs Filename:=newname
        .Close
    End With
End Sub

So the above is what I am using to save a macro-free, UDF-free, Optional Add-in independent sheet for distribution. I just tried passing the range without using the name of the range, and the UDF still functions as I view results in my macro-enabled workbook, but still does not export the UDF result values using the macro above. All other values are exported fine.

So I think that what is not working is the Analysis Toolpak add-in when I use the macro above. Suggestions?

 

[Bruce_R]

I created a sample workbook with the UDF and the SaveValues macro. I uploaded it to my google drive. You can see the value in red in the sheet is returned by the UDF, but is not exported. Please note that you do need to enable the Microsoft Add-In called Analysis Toolpak in order for the UDF to work, as it uses the WORKDAY function.

https://drive.google.com/open?id=0B2lTA0YkLlDbOVdCS2J6UkpMc1pNWGw5VDJBcDV5R3pHdnJF&authuser=0