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? The code I am using is below:
if there was a way to disable mcros but I think now I should have pursued the option of saving data to new workbook without transfering the existing formulas or macros. I really think I should attach the sheet I have thus far so I can convey what I am trying to accomplish. Anyone interested, please let me know and I will forward the sheet. I am unsure how to do this any other way. I found some code that would claim to do this but I have so many things going on already in this sheet I can't figure out how to incorporate the new code to work properly. Here is the original thread for reference [url]
Is it possible to use VBA code to save a copy of your workbook while removing all formulas and replaceing them with values and formats only? I know how to save and rename, but can;t find the command for a "Static Copy".
I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.
I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.
I created a financial model in sheet with a macro. The model works as designed. And the workbook can be saved with smaller steps. But with big steps that contains about 250,000 formulas, it seemed to take forever to have the work book saved, I have to canceled it after about 45 minutes. I tried it on different machines and all have the same problems.
I have a update/Save button on a spreadsheet. So when clicked it updates another sheet and saves the workbook. how can i have it run the macro when they don't click the button and do File -> Save.
I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 16-02-2009 by ceng '
I have some formulas in my workbook that use the weeknum function from the analysis toolpak. I have the analysis toolpak installed, yet every time I save the file and reopen it, the formulas with weeknum are gone, with #N/A in its place as the formula. Sometimes the results of the formula from the last time remain (the cells contain the number of the week, but without the underlying formula), sometimes not. All the other formulas are fine. The formulas are simple, like =WEEKNUM(C2)
I have been trying to save as a 97-2003 .xls file. Today I'm trying to save as .xlsx, but I have to rebuild the formulas first, so I don't know how that's going to turn out.
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code: Sub SaveSheetAsNewBook() Dim wb As Workbook Dim InitFileName As String Dim fileSaveName As String Dim wshape As Shape InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
I borrowed the macro below from this forum. My formulas in the sheets I'm copying refer to other sheets that I'm not saving. can someone tell me how to change it so that it copies values only to the new workbook as I'm only saving it for records purposes and some cells are saved with #REF errors.
I'm guessing there's a spot where I should type .Value ? Copy.Value doesn't work.
I have a workbook "Weekly Field Report-Master Copy" and another workbook "Weekly Field Report $$$-Master" The "Weekly Field Report $$$-Master" workbook has all the formulas that pull data from the "Weekly Field Reports-Master Copy" workbook and populates a financial report of that week's field unit production.
When I save the "Weekly Field Report-Master Copy" workbook to a new name "Weekly Field Report Job #1" and then save the "Weekly Field Report $$$-Master Copy" workbook to a new name "Weekly Field Report $$$-Job #1" is there a way to automatically change the formulas in the "Weekly Field Report $$$-Job #1" workbook to now pull data from the "Weekly Field Report Job #1" workbook instead of the "Weekly Field Report-Master Copy" workbook???
Currently I am manually changing the formulas in the new workbook "Weekly Field Report $$$-Job #1" to reference the data in the new workbook "Weekly Field Report Job #1". Otherwise the formulas pull the data from the "Weekly Field Report-Master Copy".
I am using the following code to save a workbook as a macro enable workbook on the selected path as today's date. But it is being saved as a macro free workbook.
Below is the code in module:
Sub save_file() Dim tDate As String Dim FileSaveName As String Dim fPath As String tDate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd-mm-yyyy") 'FileSaveName = Application.GetSaveAsFilename(InitialFileName:=tDate, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
I have a spreadsheet that data gets exported to from a software program. There is information such as customer name, part number, inquiry number, etc that I'd like to use to develop a log of all my estimates I create for parts. I'd like excel to take the cells I select and insert them into certain cells in the other workbook and automatically save that workbook. This will create a log of all my estimates where I can then link the full estimate to the log. The information will have to go in the next available row. Is there any way I can accomplish this without having to open the other workbook and save the work book each time?
Need VBA code to save column J of open workbook called LmbcAcctsPayable.xls, Sheet1, to same workbook & sheet called LmbcAcctsPayable.xls, Sheet1, when save is not selected.
Reason: The workbook is usually not saved because it is more of a templet with names in column A. Column J is updated with a date when a row is used so I need the column J to copy automatically to the blank templet when exiting without saving (using ThisWorkbook.Save = True in Auto_Close). That way, rows not being used for a long period of time can be periodically eleminated.
I snagged this code out from another project I wrote, and it works fine in that one, but doesn't in the new project.
What I want, is when the person presses the exit button, it will save whatever was done, its running as a form printer and database. The code below is the code for my exit button.
Private Sub CommandButton4_Click() Dim sAnswer As String Dim i As Integer
When it gets to "If ActiveWorkbook.Saved = False Then" it does put the question out "Do you want to save changes" I click Yes and it doesn't save it.. I have tried both activeworkbook and thisworkbook ... I copied the code and verified all is good to go..
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
I use excel 2003 SP3. I've created invoices that have several (very basic), but different formulas in the last column of the worksheet. I have the same customers every month so I use the same monthly workbook for all invoices. Since each invoice has the same formulas, in the same cells, I used the SHIFT key while selecting the entire range of worksheets (invoices). After they have been selected, I entered the SUM formula in a cell so it would automatically be entered in all of the worksheets (invoices).
When I select the same cell in all the worksheets (invoices) I can see that the formula is there. However, when I enter a value that the formula should be adding, it doesn't. But if I select the cell with the formula in it, the formula shows in the formula bar. When I put my cursor at the end of the formula and hit ENTER, the formula then works. So I have to select each formula, in each cell, on every worksheet, hit ENTER, and then, if I need to change a value, I have to repeat the process. I am definitely missing something, and I'm sure it's quite obvious and simple.
I have a macro that I have been trying. I want to take a workbook and do a saveas, remove one of the tabs called UPDATE closing the original and renaming and dating the new workbook.
I have a Sub SaveFile() in a standard module and a Sub in ThisWorkbook module that will call that Sub; however, I have another Sub CopySheet() that I would like skip the Sub SaveFile(). The reason I do not want the Sub SaveFile() to run is because I am making 19 copies of the first sheet and I do not want the user to have to click "Yes" 19 times. How can I have Sub Savefile() ignored when I run Sub CopySheet().
Sub SaveFile() Dim Ans As Integer Dim Filt As String Ans = MsgBox("Confirm you want to save this File." & (Chr(13) & Chr(10)) & _ "File will save as: Backup - (SheetNumber).xls in current directory", vbYesNo) If Ans = vbNo Then Exit Sub Application.DisplayAlerts = False ThisWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Path & "Backup - " & ThisWorkbook.Name ............. End Sub
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call SaveFile End Sub
Sub CopyBook() ActiveWorkbook.Save Dim i As Integer For i = 2 To 3 ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & CStr(i) & ".xls" Next i ActiveWorkbook.Close End Sub
I have a list of .xls filenames contained in a sheet that I would like to utilize in separate formulas in the same workbook.
I can't seem to write a formula to utilize a filename in a separate cell to gather data from said filename.
In other words, I would like to do something like the following:
Cell A1: '[filename.xls]'
Cell A2: =A1Sheet1!A1
to gather the data from Sheet1 cell A1 in filename.xls, but it doesn't work no matter how I have tried. It seems the only way is to actually manually transcribe the filename into cell A2, but this doesn't seem right. There must be a way to do this.
am trying to create a workbook template which collects data from users with user forms and some macros. Users assign a project name when workbook opens and then they are able to save as the workbook with name of project name. But i don't want them to save on my template mistakenly. How do i prevent this? I tried workbook_before close sub but i can manage that it recommends to save workbook with project name and if you don't want, it still asks you if you want to save workbook before close
I have made some changes to a workbook and now every time I want to close it I'm asked if I want to save the changes, even when I have made no changes. If I click 'Yes' to save changes the problem still occurs the next time it's opened & closed.
I have checked external connections and there are none. I have done a 'find' to check that there are no formulas looking at another sheet, which I cannot find.
This document has to have a live calculation function - I have read some suggestions around turning off auto calculations but this isn't a possibility and I'm not sure that this is where the problem lies.
The previous version does not have this problem, so it is something I have done between v2.2 and v2.3. I have attached both for reference. Note that they were originally xltm's not xlsx's - I could not upload macro-enabled templates, but the same problem occurs in v2.3 in this format.
