I have a workbook with 4 worksheets the first is called "input" which I use to enter information which goes to the other worksheets which arecalled engine, gearbox, doors.
I am stuck on the following; after inputting the information. I want to save as pdfs in various locations.
Is it possible to have 4 buttons on the input sheet which automatically :
Button 1 - Save "engine" worksheet as a pdf to a specified folder
Button 2 - Save "gearbox" worksheet as a pdf to a specified folder
Button 1 - Save "doors" worksheet as a pdf to a specified folder
Button 1 - Save the sheets as multiple excel files to a specified folder
I have a spreadsheet that from a button I want to run a macro that will input todays date, the value in cell A1 as the filename into a default dialog box that is at a default file path. I have been trying to do this for several hours and can not completely get it done.
I have a workbook that automatically creates a new sheet based on the current day. There is a pivot table in the workbook that gets values from multiple consolidation ranges.
Right now I have to manually add the ranges from the new sheet every time. I need that pivot table to update with the same ranges from the new sheet every time a new sheet is created.
There is also another sheet (named "Data Results") that needs to update too, but that's not as urgent.
I've searched everywhere on the forums and can't find anything like that. When the file opens, I've written script to have a box pop up asking whether you want to "View Data" or "Begin New Audit." If you click "View Data" nothing happens and you can just view the file. If you click "Begin New Audit" it creates a new sheet based on the current date.
I wanted to attach the file, but it's an Excel 2007 macro-enabled file (*.xlsm).
I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.
I found the following formula which, when used as a named range, automatically extends a list for data validation:
I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?
I have a macro which performs the auto save function perfectly. I'm looking at possible enhancements.
1. How to set autorecover save location in VBA? I know how to do this on the front-end i.e goto tools/options/save tab and set it there As i have disabled save autorecovery feature, autorecover save location is greyed out
2. How to create a backup of my workbook? So that my workbook will perform autosave and when i close this workbook at the end of the day all the latest changes are added to the backup and saved
User selects 'go to page B' on page A. They look at an object then click the object to go back to page A. Once back on page A they need to check data on page B, but in the same general location as where they just looked. This is over simplified, but pretty much sums up what I need to do. The code I have now is below, but keeps sending me to the last selection on my current page.
Public Sub SaveLocation(ReturnToLoc As Boolean) Static WB As Workbook Static WS As Worksheet Static R As Range If ReturnToLoc = False Then Set WB = ActiveWorkbook Set WS = ActiveSheet Set R = Selection Else WB.Activate WS.Activate R.Select End If End Sub
Public Sub SetSaveLoc() SaveLocation (False) End Sub
Public Sub GetSaveLoc() SaveLocation (True) End Sub
I've been searching for ages trying to work out how to do this but have so far only managed to confuse myself. i have office 2007 and I have found some code to convert an excel sheet to PDF, however I need it to saveas the contents of cell (e6) and save to a location on our network drives (C:TEMP).
I often need to save the user's current position on a worksheet, do some stuff then get them back to where they started. In the past I've saved the current location sometimes as a string and sometimes as a range.
Code: Sub BackToRange() Dim BackToHere As Range Set BackToHere = Selection ' do stuff BackToHere.Select End Sub
Code: Sub BackToString() Dim BackToHere As String BackToHere = Selection.Address ' do stuff Range(BackToHere).Select End Sub
managed to get a piece of code working to create unique sequential numbers for purchase orders, but only by "enabling all macros" which is apparently not recommended and could conceivably, as I understand it, leave a PC vunerable to viriuses contained in other imported files.
Please bear with me as I'm very new to anything other than basic Excel functions; macros were, until last week, something I didn't even know existed, let alone how to use them. Anyway, having got my macro working, I understand that the best thing to do is put it in a "trusted location" from where it will work automatically without requiring operator input (whilst still maintaining high overall security), rather than "enabling all macros", but I have a couple of issues with this.
Firstly, having created a folder in "my documents" as per the instructions, how do I save the macro there? What format should it be in? Surely not a word document? (as you can gather I am still really in the dark about all this). If not, what?
Secondly, how do I direct Excel to access and run the macro from this "trusted location" folder, rather than the already existing "module1"?
I am running a macro in Excel which automatically generates a PDF of my worksheet. Currently it saves in the default location but i want to modify it to a specific location - P:Emergency Services|Procative ContactForms PDF.
I've been searching all morning through various posts on this subject, but I can't find anything that I can adapt to my needs. Here is the code I have:
ChDir "C:Documents and SettingsmynameDesktop" ActiveWorkbook. SaveAs Filename:= _ "C:Documents and SettingsmynameDesktopBook1.txt", FileFormat:=xlText, _ CreateBackup:=False
This file will be used accross multiple computers and therefore the directory will always change. What I need to hapen, is for this code to save the workbook in the same directory as the origional file. I also want the file name to refer to a cells value, as I will be having the user define the name through a userform.
I get an xl file sent to me every day which I upload into a database. I then save the file keeping its name and save it into a selected folder. Is it possible to add a macro at the beginning of my current macro that saves the file using its name to a selected folder?
I've created a spreadsheet of clients and info; I want each client name to link to their specific folder on my computer for easy referencing. I entered in all the links and they work just fine; however, when i tried to save the file to another location on my computer so it could be used by another employee (same terminal, etc, just in another folder) the hyperlinks stopped working.
For a sheet that many non-expert users will use on different systems I need a macro that let's them save, print and send the results of their work. So I made a macro that makes a copy of only 1 sheet of the workbook and saves it with a given name to a given location. The problem is that I want a location prompt to ask the user where they want the file saved, while giving/suggesting them a fixed filename. A lot of different users will make and use their sheets so I need a certain naming policy to manage all the files. (date, location, etc)
I just got a new computer and upgraded to Excel 2010 and Windows 7. When I try to open a workbook in Excel 2010, my saved file paths on the left side of the screen are gone. I want to put them back in there.
In Excel 2007 running Windows XP, I would just right click in the left side menu and click "add" and now that's no longer an option.
I am attempting to write an Excel macro that will be stored in a file called MacroFile. The purpose of the macro is to
1. Follow a hyperlink to an Excel file saved in a SharePoint type enviroment 2. Save the file to my laptop directory My Documents.
Below is the code I have written. The code is following the hyperlink and saving a file but is the focus file is incorrect.
Here is what happens:
1. Open up MacroFile and run macro 2. Hyperlinked file LinkedFile_1.xls is opened 3. File NewFile_1 is saved but contains the info from MacroFile 4. Hyperlinked file LinkedFile_2.xls is opened 5. File NewFile_2 is saved but contains the info from LinkedFile_1 6. Hyperlinked file LinkedFile_3.xls is opened 7. File NewFile_3 is saved but contains the info from LinkedFile_2
The files created are named correctly but have the wrong data in them. I need to know how to control which file is considered ActiveWorkbook.
I have the below code that saves selected sheets of my workbook as pdfs in the current file location. What I would like this code to be able to do is to create a new folder (named with todays date), and then save each of the pdfs into this folder.
Code: Sub SaveWorksheetsAsPDFs() Dim sFile As String Dim sPath As String Dim fPath As String Dim wks As Worksheet
I have a workbook with approximately 30 worksheets. This workbook already has a massive macro that I've written. I'd like to write a code that will take a designated worksheet, check to see if there is data in cell A2, if so, save it as a new workbook.
The name of the workbook should be predetermined, for example "SIA April(Previous Month) 2008(Current Year) P-Card Import Template.xls" The months and years will need to change.
The file to save it in will also change monthly- for example- G:PCard DirectoryCloses2008 ClosesApril(previous month) 2008(current year)
I have 40 files in one folder which I named it as "CA" + month's name that I am working on. I need to do analyse these files monthly and save it under new folder. how do I automatically save them in new folder and name them for that particular month. Also, each file has worksheet which has one cell as "Aug-07" and the cell next to it has number of that month that is "08". How do I automatically change this also based on the name of the file, because file name month and month in the cell are the same.
I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
I am starting within a workbook with a standard invoice/form. During the course of a month this invoice will be copied 20 to 30 times within the workbook and the amounts changed each time. Is there a way that I can:
a) Have amounts from 2 of the cells (say E30 and E31) within each new invoice worksheet automatically included on a summary page? These cells are calculated by formula.
b) (This one would be nice but is not essential) Have each new invoice worksheet that is added (by copying) automatically numbered sequentially in a cell (say A2) and also re-named with this number. The first existing invoice would be manually numbered and each additional invoice would follow from there.
Is it possible to have the worksheet save itself automatically every 5 minutes or so? I don't want the user to have to click on a macro or do anything. Basically they will not even know that it is saving.