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
-This saves it to my computers D drive and works fine.
Then I also have the following code whice is suppose to save it to the public drive which is U: drive, but this computer does not have direct access that that drive so i save it to
-The wcrsan1 is the path i use to save it to th U:drive.
My issue is the code runs fine, but it does not save it to wcrsan1 owley public1 picking logs. If i save it manually it works but not when the macro runs it.
VBA is password protected.. The password is Kayley98 the sheet password is go
way to construct my save file so that it saves to the correct location.
I have a a FileSearch that lookings in a specific Folder and the sub folders for a file type and then saves them to a different location. Here is the location of the save in regards to the original location...
Filesearch folder= C:path Savefolder=C:path he folder its found in & " excel converted"
If that makes any sense, basically I need to call the path of the file I opened, but not including the file name.
I save a workbook, the location where I were last (ex cell Z42) is also saved and upon re-opening it again, it opens the sheet to the last location (Z42). So how could I make the workbook and all its worksheets to open at a defined location. ie. A1 for instance.
I am currently having a slightly annoying issue that only happens 50% of the time, that when trying to re-save a excel sheet on a network location. The network location is a folder on another computer. The same happens when I try and save it as a pdf, but only happens half of the time.
The message which pops up says something along the line of 'Saving as: 'File-ServerDocumentsexcelsheetexample.xls' ' and a sort of green loading bar appears. It does this for about 15 seconds then goes off.
I've checked our anti virus (which is eset) and I've set it to not real time scan network locations.
I have attached a example of the saving message: ExcelWarningExample.jpg
I have an issue with saving the file at the desired location below is the code i have currently used . I have given the destination as "D:New folder". The new file does get saved at location "D:" but not inside the "D:New folder", instead names the file as "New folder".
Is it possible to use a macro like the one below, and have the second location save with the date / time stamp in the name? This is what I need to accomplish: I have a few folks that need to make changes to a master spreadsheet daily. The spreadsheet is in a Network share that when modified and saved, will save to that network share, but also locally. Can the second location have the date/time stamp added to the name of the file so that I know when the changes were made and have an audit trail ? This will also keep a copy of the last modified file in case the spreadsheet is corrupted. Here is what I have for saving to 2 locations:
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?
Is there a way to display the location of an active workbook on my local drive and save it into a varible. I'm new to Excel and VBA but I'm trying to create a template to use for multiple vehicles. When the macro is run...
1) the workbook will have to identify what Workbook and worksheet it is in and save it as a varible so that it can access it later. Each workbook is specific to a vehicle. *(The reason is because I will need to refer to the address of the original workbook when pasting the copied data from the "OilChart" Workbook.)
2) Copy the vehicle identification # and save as a varible. (Dim vehicleNumber As Variant)
3) Open another workbook on the local drive (OilChart.xls)
4) Run the macro in the new workbook (OilChart.xls) to update the data
5) Search Column B in (OilChart.xls) for matches of the vehicleNumber, and for all rows that match, select and copy all rows and paste it back into the orig workbook (Vehicle Specific Workbook).
This is the code I have so far.
Private Sub btnFind_Click()
Dim vehicleNumber As Variant Dim sourceLocation As String Dim sourceFileName As String Dim fileName As String Application. ScreenUpdating = False
I have'nt figured out how to do step 5 yet so if anybody has any ideas I'd be open to them. I tried looking at Aaron Bloods Kickbutt VBA Find Function but I got a little lost on how his code would relate to my code. It also looks like it doesn't copy or paste anything to another workbook.
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).
Now this works perfectly fine, but I am wondering if it is possible to replace 2013-004 so that it reads the text from a cell on that sheet to complete the file name.
I want to replace the 2013-004 in B1 with text from A1. I have tried multiple methods but it just always assumes it is part of the file name or breaks the formula.
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).
Im struggling to get a workbook to a specific location. I have copied 3 worksheets to a new workbook, which im trying to save to an archive folder. Here is my code.
Code: Sub atest() Dim strFileName As String Dim Archivepath As String
[Code]....
The problem is i keep getting an error (runtime 1004) saying the file could not be found?? well, im trying to create it !..
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
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.
I have a folder which contains multiple 'Customer' workbooks (example attachment 'Customer_001'). Each workbook has a filename unique to the customer (Customer_001, Customer_002, Customer_117 etc). The workbooks contain a single sheet with customer information and answers to questions. These 'Customer' workbooks are automatically saved into a folder once the customer completes a Userform and clicks 'save'. Potentially, there could be 100's of customers' workbooks saved in the folder, each with their own unique filename.
I also have a 'Master' sheet saved in a different folder (example attachment 'Master'). The 'Master' workbook has multiple sheets named 'Department 1' and 'Department 2'. The purpose of the master sheet is to consolidate all information from the individual customers' workbooks.
Specifically, I would like a command button on the 'Master' workbook to execute the following tasks...
1. Copy the data from range A3:F3 from each of the 'Customer' workbooks held in the folder. 2. Paste the data into the next blank row on the 'Department 1' sheet in the 'Master' workbook.
3. Copy the data from range A7:F7 from each of the 'Customer' workbooks held in the folder. 4. Paste the data into the next blank row on the 'Department 2' sheet in the 'Master' workbook.
5. Save the 'Master' workbook. 6. Delete all 'Customer' workbooks in the folder.
2. In addition to the original workbook being saved, a copy of the workbook with the current date is also saved to the specified location of my choice.
For example
"C:UsersUSER1DesktopBackup Test as of 02-10-14"
3. If a copy of the workbook is already saved with today's date, then overwrite it automatically without prompting the user.
4. All the above happens without any user interaction.
I'm trying take a reference from some workbooks (e.g. WB1.xls,WB2.xls,WB3.xls,...etc) They are all placed in one folder but there filename is subject to change,
I need to get the result of Cells A1 from WB1.xls+A1 from WB2.xls + A1 from WB3...etc to shown on AA1 in another workbook(e.g. Collected.XLS)
Is this possible knowing that workbooks filenames are changeable? Moreover Can the value of A1 from any new workbook added to the same folder be added automatically?...
Can the formula typed in AA1 from Collected.xls be draged down?(So as to show the sum of Cells B1 and C1.....etc) from each workbook?
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?
