New to VBA...want to save as with values

[jen_maser]

Hello - First time poster here and relatively new to VBA/macros! I have an excel worksheet that I have set up for users to update at various times throughout the day. This file is linked to another and auto-refreshes data every time it is opened. After the user updates this template file, I want them to be able to click a button in the sheet so that the file is saved as a new copy with a file name based on fields in the worksheet. I also want the data connection to break on this new saved as file and display the values. I've searched high and low, think this can be done...but having no luck. Any help/direction would be greatly appreciated! Thanks! Jen

 

[tonykslee]

For the save as thing:

Dim cell1 As String
Dim cell2 As String
Set cell1 = ActiveSheet.Cells(1, 1)
Set cell2 = ActiveSheet.Cells(2, 1)
ActiveWorkbook.SaveCopyAs "C:\TEMP\" & cell1 & cell2 & ".XLS"

For the connection breaker, you could have your workbook check the workbook name in the Workbook_Open sub. If it isn't "xxxxx" then end program, else continue running program.

or a simple prompt at the beginning of the code to ask you to continue or not. i use this a lot

    Dim ans As Variant
    ans = MsgBox("Continue running code?", vbOKCancel)
    If ans <> vbOK Then
        End
    End If