I have a need to simply rename a workbook without saving. I have a master template named "IR QT" but, within the workbook, I run a macro that saves the file with content from a cell as the filename. After the 'Save' macro is executed, the sheet stays open, as planned, but obviously the file has been renamed. What I would like to happen at the end of the 'Save' macro is for the sheet to be renamed (not re-saved) to "IR QT". I just need the VBA code to put at the end of the 'Save' macro to do the rename back to "IR QT".
I have a condition setup (under "This Workbook") upon opening a spreadsheet to verify a computer's predetermined motherboard serial number that will shut the Excel workbook down if the numbers do not match. This works fine. What I'd like to do is to add a second condition that will allow a password to be entered in a textbox if the numbers do not match so that the serial number condition could be manually overridden, if necessary. If the serial number and the password are incorrect the workbook automatically closes.
Here is the code that works:
Private Sub Workbook_Open() If CreateObject("Scripting.FileSystemObject").GetDrive("C:").SerialNumber "-XXXXXXX" Then ActiveWorkbook.Close False End Sub
Here is what I am attempting to do that is not functioning properly - need to add a textbox to enter a password to override an incorrect serial number
Private Sub Workbook_Open() If CreateObject("Scripting.FileSystemObject").GetDrive("C:").SerialNumber "-XXXXXXX" Then Dim Rng Rng = InputBox("aaaaaa") If Rng "aaaaaa" Then ActiveWorkbook.Close False End Sub
Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
I want to open an existing workbook, make a copy, rename the copy only and have it remain open and retain the original workbook unchanged and not open.
I have a workbook that pulls in data from other sources and contains 15 linked sheets. The data changes when different criteria is selected in the first sheet. The first 2 sheets are summary sheets and the tabs are named correctly.
I would like to be able to rename the remaining 13 tabs using labels found in the cells in the first sheet. These tabs should update or change when the first sheet is manually recalculated (F9).
I have used ActiveSheet.Name=Range("A1") but it doesn't apply in this case. Is there a macro that could run with something like Sheet1.Name=Range("A1"), Sheet2.Name=Range("A2"), Sheet3.Name=Range("A3"), etc.... or maybe something in each sheet that would change the name of the tab to a specific name off sheet 1 when the whole workbook is recalculated?
had a look and the closest I could find was Renaming Or Removing Workbook which doesn't quite do what I am after. I am writing an add-in that creates a new workbook, grabs data from sybase to populate the workbook and then formats it as required. What I want to do is rename this workbook something meaningful, but without saving it.
The reason is that many people will end up using the add-in and will want to solve their cut of the results in various different location. The link above is along the lines, but is looking for a previously saved workbook, where as the workbook in question has yet to be saved.
So I guess the two option are to 1. Rename after the workbook has been created 2. Add the workbook with a specified name but I can't find either methods out there.
I have an electronic meter (Fluke AirCheck) that provides me with reports in the form of excel documents. I need to combine those documents and have a page/worksheet that contains a legend and some notes etc. for reporting to management.
Given: I have a folder with nearly identical workbooks. All contain 4 worksheets: Document map Sheet2 Sheet3 Sheet4
Goal: 1. Copy and combine, either specific sheet(s) or all the sheets from multiple workbooks into a new workbook (with prompting) 2. Rename the worksheets as the workbook file names and existing worksheet names combined.
Example: Workbook name is "101B.xls" Worksheet 1 would be = "101B - Document Map" Worksheet 2 would be = "101B - Sheet2" Worksheet 3 would be= "101B - Sheet3" Worksheet 4 would be= "101B - Sheet4"
Results thus far: Okay so I managed to find a script that does most of what I need:
[Code] .....
For renaming the worksheets I have tinkered with:
[Code] .....
But alas none of that works. I can also only copy 1 sheet at a time currently.
Another issue which seems to have reared its ugly head in the copy and paste function as the text on my new worksheets is white on white when the originals are black text on white. I can select the new worksheet and select all cells and hit "Automatic" on text and it fixes it but that's a pain on 100+ worksheets. So I need some sort of copy /paste special command, I think to make that work too.
I have groups of folders that I need to extract "Sheet2" from each workbook and assemble them into one workbook. Along the way I want to rename the sheets to the file name (-xls). I have assembled this code so far but it is broken
Code:
Sub CombineSheets() Dim sPath As String Dim sFname As String Dim wBk As Workbook Dim wSht As Variant
I will shortly be running a project over a twenty four hour period, where a group of people will be inputting data into a workbook (one worksheet per hour), and what I would like to know is if it is possible, to input some coding into a thisWorkbook module to action a save on the workbook every time an entry is placed in column A (of each worksheet) which is divisable by 10 (a10, a20, a30 etc).
I have an Excel doc with ~20 tabs. Each type represents 1 type of activities (soccer, tennis, etc). The size of the file (with the 20 tabs) is pretty significant (>1mb), which jams our email traffic.
Is it possible to: - keep this workbook with the 20 tabs when open by the user; - let the user select and fill in whatever tab he needs (just one at a time); - have a button for the user to click that will save only this tab with a different filename (therefore, when sent by email, the file should be pretty small).
As I've learned to develop Excel apps with vba over the years I've noticed that, if the Excel app starts to get too large (over 1MB) saving it will often cause the application to crash for no apparent reason - the result being that I lose all the vba code I entered before the last save - with the worst case being that, on occasion I have lost all of the vba code in an applications!!!
So, I've learned to back up often using a third-party utility that I use to save my applications, which works wonders in allowing me to save my application without it crashing. The application is called Code Cleaner 97.exe. It was designed around Excel 97 (which I still use) but if possible I'd like to get an updated version for Excel 2003 and Excel 2007, which I also use.
Does anyone know of a more recent version of this utility, or another similar utility that allows large Excel applications to be properly saved, etc?
Code Cleaner 97.exe was a freebie that was available through one of the old Compuserve Excel forums. It was a very popular utility and was suggested as a means to save one's Excel apps without the app getting trashed, etc.
I'm not expecting a freebie solution so I'll gladly pay for something that allows me to save my large Excel apps without them crashing, etc.
Ok, I'm trying to learn how to create and save a new workbook using VBA...having some issues with this error:
Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed.
The line is highlighted in red.
Code: Sub Copy_ActiveSheet_1() Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String
I Want A Code That Will Save My Workbook To A Specific File (different Form The One It Is Currently In) Using The Name That I Have Typed In Cell D13 In The Workbook
I am using this code in my ThisWorkbook, but it keeps coming back as an error for
ActiveWorkbook.SaveAs Filename:=ThisFile
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Saves file as what is in cell "C7" to specified network location - variant below Dim Location, ThisFile As String Location = "\Office-pcpublicCustomers" ThisFile = Range("C7") & Format(Now(), "dd/mm/yy") ThisFile = Location + ThisFile ActiveWorkbook.SaveAs Filename:=ThisFile End Sub
When the button is pressed, It opens a Mesage Box giving an alert to the User about what is He going to do...if he clik no the macro stops, if the user click yes then it opens an InputBox ....and the name the user writes there will be the name of the new book...
If working good if the user press OK ...my problem comes If the User press CANCEL in the InputBox...The excel try to Save the file as FALSE...and I don´t know how to avoid it.
Here is the code.
Sub Botón1_AlHacerClic()
X = MsgBox("Warning Bla bla ".Are you sure ?", vbYesNo, Title:="IMPORTANTE")
Naa = Application.InputBox("Write the file name Here", "New File Name")
i have a workbook which is mainly use in construction building design... i want users to input ther respective data in that workbook to calculate and design only, but i dont want the user to save the changes they made...
How do I get a shared workbook to automatically save upon every change by each user. The only way I can get this to work (at this point) is to run a macro that updates every 45 seconds IN EACH WORKBOOK! It's counterintuitive because the workbook is shared (on a network), but each user must start the "time" macro in their "individual" SHARED workbook to allow the automatic saving.
What am I missing? There has to be a more efficient way to do this, right?
Code below HTML Sub Time()
Call Save Application.OnTime Now + TimeValue("00:00:45"), "Time"
I'm wondering if it is possible to prevent a user saving a workbook. If opened as a read-only, excel throws up an option to save with an alternative name. I'm wondering if I can put some code in the Workbook_BeforeSave event that prevents saving of the document unless the application.username is myself.
All I want to do is save a copy of one sheet called "Patient Stock Form", change the name to "Patient Order", and put it into an email. But my code is putting the entire workbook into the email. Here's the code:
VB: Sub EMAILFORM() Dim oApp As Object ' Outlook.Application Dim oEmail As Object ' MailItem
Is it possible to have a userform open in a workbook and when the data from the form is saved, save it to a completely different workbook. If so, will that other workbook need to be open for the userform data to be saved?
I have my Excel workbook open & have made changes. Now I close it and Excel prompts me if I want to save the changes. Is there a way to just close the workbook and automatically accept any changes without have the "Do you want to save changes" message appear?
What about if it is opened in read-only mode? Typically the workbook will not save any changes unless you save it off as another file name. If there is a solution to my question above, can it be enabled only when the workbook is opened in edit mode, or will it not auto-save changes over the file because Excel knows it was opened in read-only mode?
I read two pages on "Workbook_BeforeClose" trying to find a relative answer. I want to able to close a workbook with out saving and not rely on the user pressing the correct button, can this be done?
I am trying to write code which copies the activesheet (called "Data"), opens a new workbook, pastes the data into it and closes the new sheet as "sales 2008" followed by the date. What I have at the moment is:
Need the VBA code to close a workbook, and not save it? I need it to open a workbook, run a macro, and close without saving. The code I have thus far is:
i get an excel file with dynamic data everyday. is there away i can use a macro on this file to store this dynamic data to a specfic closed workbook and add the data onto the old data in that workbook?
I would like to set up my workbook so that I can save it by the combination of two cell values. The cells are A2 (which displays a country name) and E10 (which displays a string of numbers) and would like to specify the file to where to save the workbook which is:(G:BusUnitsShipping_RecievingInternationalInt'l Order FormsSpecific Orders). The following is a code I have tried that is close but not quite right:
