Save As Workbook Without Formulas

[nva2k4]

Good afternoon,

I need to save my workbook to another drive every Friday for archive purposes. My workbook contains time sheets for each employee. Currently I use a command button to copy and save the workbook to the correct place and it works perfect. However, the formulas are copying with it and when I open the saved workbook in the future the dates have changed in the Mon-Fri cells. The reason for this is because I have a formula in the week starting cell that changes the date automatically every Sunday. Then, the dates in the Mon-Fri cells change with it.

What code can I insert, and where, to keep the formulas from copying over with the workbook? Thanks in advance for responding. The code I am using is below:

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Private Sub CommandButton3_Click()

 Dim mbResult As Integer
 mbResult = MsgBox("Are you sure you want to proceed?", _
   vbYesNo)

 Select Case mbResult
    Case vbYes

    ActiveWorkbook.SaveCopyAs ("My Directory is Here\" _
       & Format("Week Ending ") & Format(Now() + 1, "dd mmm, yyyy") & ".xlsm")
   
    Case vbNo
       'Do NOT allow the macro to run

End Select
End Sub

[6StringJazzer]

You have to be very careful here because you are using SaveCopyAs. You want to make sure that you remove formulas from the copy, not your original workbook. I suggest using SaveAs instead. Here is what I would do:

Private Sub CommandButton3_Click()

   Dim mbResult As Integer
   Dim w As Worksheet
   
   mbResult = MsgBox("Are you sure you want to proceed?", _
   vbYesNo)
   
   Select Case mbResult
      Case vbYes
         For Each w In ActiveWorkbook.Worksheets
            w.Calculate
            w.Cells.Copy
            w.Cells.PasteSpecial xlPasteValues
         Next w
         ActiveWorkbook.SaveAs ("My Directory is Here\" _
               & Format("Week Ending ") & Format(Now() + 1, "dd mmm, yyyy") & ".xlsm")
      
      Case vbNo
         'Do NOT allow the macro to run
   
   End Select
   
End Sub

However, when this code finishes, the active workbook will now be the copy, with its new name. Using SaveCopyAs leaves the original workbook as the active workbook and does not open the copy.

Edit: I assumed you have multiple worksheets but if not then you don't need the For loop. You can just copy and paste on Worksheets(1).

[nva2k4]

Thank you 6StringJazzer for the quick response. I tried the code you recommended and the formulas are still going with it. Any clue?

[6StringJazzer]

Do you have a version of your file you can post, without any private data?

To post a file:

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Click the Upload button to upload the file
5. Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

[nva2k4]

Thanks again for your response. Here is the template I'm using. There are multiple worksheets in this workbook, one for every employee. There are multiple formulas embedded into this workbook that automate it completely. I have four command buttons at the top that make my life easier. I would like to just "Copy" and "Save As" the whole workbook (Values Only)so that my actual workbook stays untouched. I have a command button that deletes all the time stamp values after I have archived it.

[6StringJazzer]

I'm not clear on what you have attached. There are no buttons, and you have not attached a macro-enabled file.

In any case, I have added a Save Values button and my code so you can see how this works. It works fine.

[nva2k4]

I appreciate all your help 6StringJazzer. However, that code makes my workbook freeze for some reason. I tried it multiple times with the same result. Is it possible to just copy and save the worksheet rather than the whole workbook?

[6StringJazzer]

Is it possible to just copy and save the worksheet rather than the whole workbook?

Do you mean the worksheet called "Weekly Time Sheet"? I have no idea why you would have trouble, I developed this under 2013. Here is a revision that saves only "Weekly Time Sheet".

[nva2k4]

Not sure why but the code didn't run. It kept getting stuck at:

ActiveWorkbook.SaveAs CopyName

I messed with it for at least an hour but could not get it to run. I decided to just save the worksheet as a .pdf instead. Anyways, thank you 6StringJazzer for the help, you got some excel skills.

[nva2k4]

Just seen the note about the code tags. I will make sure I include those next time.

[mikerickson]

Have you tried saving it as a .CSV file. That will turn all the formulas into values.

[6StringJazzer]

Savings as .csv loses all formatting. There is significant formatting in this file.

[6StringJazzer]

Did it display an error message on that line? What was the value of CopyName at that point?

I always test code that I post, unless I explicitly state that I didn't; I don't know why it wouldn't run for you. I tested it with 2013.

[nva2k4]

The program just sent me into debug and that line was highlighted yellow. I'm not sure why, CopyName seemed like it was declared correctly.

[6StringJazzer]

I still need the answers to the two questions I asked to be able to help.

The program never just drops you into debug mode. It gives you a dialog box with an error message, and an option to end or enter debug mode. What was the error message?

Once you are in debug mode, you can hover your cursor over a variable name and see the value at that time. What is the value of CopyName at that point?