Save Workbook

Dec 26, 2008

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

View 9 Replies


ADVERTISEMENT

Excel 2003 :: Unable To Allow Users To Make Changes To Workbook But Only Be Able To Do Save As Not Save

Dec 3, 2012

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.

View 4 Replies View Related

Save Workbook Using Variable For Save As And For A New Folder Name

Jul 31, 2009

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.

View 6 Replies View Related

Save Button Save The Workbook

Apr 5, 2007

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.

View 9 Replies View Related

SaveAs - Copy Sheet Into New Workbook And Save It In Same Folder As Original Workbook

Jun 17, 2014

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
'

Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

[Code] ........

View 2 Replies View Related

Save Single Sheet To New Workbook And Delete VBA Code From New Workbook

Jun 25, 2014

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")

[code]....

View 2 Replies View Related

Macro To Save As Workbook 2 From Data In A Cell From Workbook 1

Sep 28, 2012

I have two workbooks open. I need to "Save As" and close workbook 2 with a file name from a cell in Workbook 1. The macro is running from workbook 1.

I'm guessing a change in the last line. I don't know what Dim means either.

Dim FName As String
Dim FPath As String

FPath = "G:"
FName = Sheets("sheet 1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName

View 1 Replies View Related

Open Workbook Then Save As Different Workbook Dependant On A1

Feb 14, 2009

is it possible to work on a workbook lets call it 123

once macro button is pressed it opens workbook abc

then saves it as what ever is in cell a1 to a directory?

View 14 Replies View Related

Save Workbook As Macro Enabled Workbook By VBA

Jan 23, 2012

the following scenario.

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")

[Code]...

View 4 Replies View Related

Save Data From One Workbook To Another Without Opening Workbook

Mar 1, 2013

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?

View 1 Replies View Related

Save Column J Of WorkBook When WorkBook Is Not Saved.

May 27, 2006

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.

View 3 Replies View Related

VBA Save Workbook As Shared Workbook

Aug 30, 2012

HTML Code:

Private Sub SaveAsAndShare()
ActiveWorkbook.SaveAs Filename:= _
"S:LdnPath1Path2Path32012Folder1Folder 2Aug12ReportName " & Format(Date, "MM-DD") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

HTML Code:

ActiveWorkbook.SaveAs FileName, , , , , , xlShared

My code is the first bit, the 2nd code I found from another post, I want to add xlshared to my code above so it is shared when saved down.

View 4 Replies View Related

Save Workbook On Workbook Close

Aug 26, 2006

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

' ****************
' ** Exit Button**
' ****************

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..

View 9 Replies View Related

Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

Mar 21, 2013

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...

View 5 Replies View Related

Save Workbook With New Name?

Aug 19, 2014

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.

View 2 Replies View Related

Save Workbook By Name

Jun 23, 2009

If i have 2 workbooks open and want to save only one of them and it is not the active one and i cant make it active as it is hidden.

View 2 Replies View Related

Prevent To Only Save On Specified Workbook With VBA

Jan 28, 2013

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

View 7 Replies View Related

Save And Close Workbook Using VBA

Dec 11, 2013

How can i save and close one workbook in excel with vba code.

View 2 Replies View Related

Workbook Asking To Save When No Changes Made

Mar 1, 2013

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.

View 3 Replies View Related

Save As Workbook Without Formulas?

Apr 3, 2014

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:

[Code] ......

View 14 Replies View Related

Save Worksheet As New Workbook

May 8, 2014

I have a sheet full of data.All this long I had written the code to save the selected cells as a PDF.But now I want it to be saved as a separate file. In this case, I believe, first the VBA will have to open a new workbook. Then copy paste this sheet onto one of the sheets in the workbook . then save the .XLM file.

[Code] ..

View 7 Replies View Related

Save A Workbook In Read Only

Apr 23, 2009

i need a way to alter the state of a workbook to "editable" then save and then switch back to "read only"

reason being is i need to hide tabs evertime the workbook is closed

View 8 Replies View Related

Save Range In A New Workbook As .gif

Sep 27, 2009

Save range in a new workbook as .gif
This is the macro :

View 5 Replies View Related

Userform Will Not Save Workbook?

Jan 4, 2013

I have a userform where data is entered and upon pressing submit it saves the workbook. this has been working fine, but all of a sudden today it has stopped saving.

I have tried using ThisWorkbook.Save and Activeworkbook.save and both appear to save (i see the "saving" dialogue box), the data is submitted into the spreadsheet behind the userform. But as soon as i close and re-open it, the data isnt there.

I assumed it may be some sort of permissions issue where the file is located. But if i manually enter the data into the spreadsheet and press save, it works and retains the data! So it appears to be something wrong with my userform, which has magically happened over night.

View 2 Replies View Related

Save Workbook As 'Values Only'

Jan 11, 2007

I have 5 workbooks, loaded with macros and VBA, each of which has 12 tabs representing months of the fiscal year. Values for the previous day's financials are updated daily.

The files are then posted to the server, so that certain bigshots can view the results and subsequently complain to us about our miserable lack of committment.

Is there a way to save or export these files without the macros and code, (i.e. as 'Values Only')? Apparently, some of them get frightened by the message box that appears warning them about the potential dangers of roque macros and code, and would prefer me to deal with the problem, rather than adjusting their security settings or allowing me to install a Trust Cert.

View 9 Replies View Related

Macro To Save A Workbook

Mar 19, 2008

I want to record a macro (so i can asign it to a button) that saves the workbook to a specific folder. I want the workbook to be saved under a name from a cell in the workbook.

the code below saves it to the correct folder but does not save it under the name typed in cell "I5" (it saves it under "false" now)

ActiveWorkbook.SaveAs Filename = "Z:ESTIMATES 2.xls" & Range("I5").Value & ".XLS"
End Sub

View 9 Replies View Related

VB Code To Save Workbook

Feb 24, 2009

I have a code that saves my worksheet once it closed. At the moment it saves the Active worksheet which is Sheet1

The problem i have is i have a few vlookup formulas that reference hidden worksheets Sheet2 & Sheet3

Would it be possible to modify this code so it saves the entire workbook?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Dim bk1 As Workbook
Dim bk As Workbook
Dim myfilename As String
myfilename = "C:files" & Range("B10") & " - " & Range("J10") & ".xls"
Set bk = ActiveWorkbook
Set bk1 = Workbooks.Add(Template:=xlWBATWorksheet)
bk1.Worksheets(1).Name = "Sheet1"
bk.Worksheets("Sheet1").Copy _
bk1.Worksheets("Sheet1")
On Error GoTo 0
bk1.SaveAs Filename:=myfilename, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close

End Sub

View 9 Replies View Related

Save Workbook As Cell Value

Sep 17, 2008

I am trying to save a workbook created by another macro using the value or text from cell A6. I get a runtime error of 1004: Method 'SaveAs' of object'_workbook' failed. Here is the code that I have.

Sub SaveAs()
Dim FName As String
Dim FPath As String
FName = ActiveWorkbook. Sheets("Sheet2").Range("A6").Text
FPath = "c:ISSignup"
ActiveWorkbook.SaveAs Filename:=FPath & "" & FName & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False

View 3 Replies View Related

Using VBA Code To Save A Workbook

May 17, 2006

is it possible to use code to save a workbook? I know this is pretty basic, but for the life of me, I cannot answer this question searching the entire website and forums.

I'm currently creating what could be called a "yearly timesheet" for a small company and trying to automate it as much as possible. I've created several buttons along with input boxes to create the data, now I would like to make two more, one to save the worksheet (preferrably using data from one cell as the workbook name) and a second to send the workbook to the local, default printer. I've come across the topics on stopping Excel from automatically asking the user to save it, etc etc but none along the lines of "this is how you create a button that if clicked, saves the workbook."

View 9 Replies View Related

Save Workbook & Close

Jul 8, 2006

One of my computers pulls info from my cash register once everyday (sales info). Then I have a excel monthly sales sheet that pulls the info from the register info and puts it in the right places. So the computer pulls the register info at 9:20pm everyday, then I have my task manager open excel at 9:25 everyday and it has a workbook open macro to automatically pull the info into the excel spreadsheet. Here's my problem-- I need to close it.

I'm sure I could use a workbook close macro, but when it tries to close it won't it come up with a save, don't save, cancel prompt? Nobody will be there to hit SAVE so it won't close? Is there just a macro that could save it then close it?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved