Save As and Continue Working

[mharper90]

I have a macro that clears a database for the quarter, to start fresh with the next quarter. Each quarter adds a few things, so it's not feasible to just start with a blank from the original file. I'm trying to add something to the macro that will save the current file (the outgoing quarter), and then prompt for a Save As file name and save the active workbook to the new filename, and allow the user to continue working on the new quarter's file without having to close/reopen anything. Right after the Save As is complete, the macro would continue to reset the values that need to be reset for the new quarter. This way the previous quarter's data is maintained.

I've tried copying a few different codes in here to achieve this, and haven't had much luck. I've cleared my attempts out, and here's the code I'm working with... Thanks for the help!

Sub TLDChangeOut()
Dim ActiveWorkbook As Workbook

msgValue = MsgBox("Are you sure?   All TLD numbers and issue/collection dates will be permanently deleted!", vbYesNo + vbCritical, "TLD Changout?")[INDENT]
If msgValue = vbYes Then
[/INDENT]
[INDENT=3]'Insert "Save" and "Save As" here[/INDENT]
[INDENT=2]Sheets("Main Data").Range("C7:D250").Value = ""   ' clears the data for new quarter
Sheets("Main Data").Range("G7:G250").Value = ""   ' clears the data for new quarter[/INDENT]
[INDENT]ElseIf msgValue = vbNo Then
End If[/INDENT]

End Sub

 

[JackDanIce]

Edit the new file name in blue and try:

Sub TLDChangeOut()

    Dim msg As String
    
    msg = "Are you sure?@@All TLD numbers and issue/collectiondates will be permanently deleted!"
    
    Select Case MsgBox(Replace(msg, "@", vbCrLf), vbYesNoCancel + vbCritical, "TLD Changeout Confirm")
        Case Is = vbYes
            ActiveWorkbook.Save
            ActiveWorkbook.SaveCopyAs "New Quarter Data.xlsm"
            sheets("Main Data").Range("C7:D250,G7:G250").ClearContents
        Case Is = vbNo, vbCancel
    End Select

End Sub

I'm not sure in your code

Dim Activeworkbook as Workbook

is needed as ActiveWorkbook is an object in VBA that doesn't need declaring (because it already exists)

 

[mharper90]

I used your code, and it works great! But I'm trying to do something a little different, in that I want the Save As prompt window to pop up to get a file name/ensure it is going to the right folder on the LAN. (I'd also like a default file name to appear in the window first, using data from Me.TextBox1 (year) and Me.TextBox2 (period/quarter #), such as "TLD 2018 P4"...if that's possible?)

In attempting this, I found a very similar example in the VBA Help Menu, but I can't get it to work. I keep getting some type of a mismatch error (I can't remember the exact error message that pops up) that highlights the fName= row when debugging. Any thoughts?

Sub TLDChangeOut()[INDENT]Dim fName As Long
    Dim msg As String
Dim Lr As Long[/INDENT]
[INDENT]Dim ws1 As Worksheet:  Set ws1 = ThisWorkbook.Sheets("Main Data")

Lr = ws1.Range("A" & Rows.Count).End(xlUp).Row


[/INDENT]

    
    [INDENT]msg = "Are you sure?@@All TLD numbers and issue/collectiondates will be permanently deleted!"[/INDENT]
    
    [INDENT]Select Case MsgBox(Replace(msg, "@", vbCrLf), vbYesNoCancel + vbCritical, "TLD Changeout Confirm")[/INDENT]
        [INDENT=2]
Case Is = vbYes[/INDENT]
            [INDENT=3]ActiveWorkbook.Save
Do[/INDENT]
[INDENT=4]fName = Application.GetSaveAsFilename[/INDENT]
[INDENT=3]Loop Until fName <> False[/INDENT]
            [INDENT=3]ActiveWorkbook.SaveAs Filename:=fName[/INDENT]
            [INDENT=3]Sheets("Main Data").Range("C7:D" & Lr, "G7:G" & Lr).ClearContents

[/INDENT]
        [INDENT=2]Case Is = vbNo, vbCancel
[/INDENT]
    [INDENT]End Select[/INDENT]

End Sub

 

[JackDanIce]

You're welcome. You may want to start a new thread as you're new ask doesn't relate to the original posting