I developed a timesheet interface using Excel. I am creating an setup-like phase for when the time sheet is first opened which determines file path names and what not. I basically I have 2 questions which basically tie into each other:
1. How can I ensure that the code only runs the first time the workbook is opened?
2. Is there anyway to store the data (for example the pathname to a certain file) without storing the actual data on a spreadsheet for it to be pulled from? I guess I could use a seperate file but I'd like to avoid that if possible.
I would like to link two cells which are in different workbooks / Files. Now this is something I already know how to do, however there is complication and I am not sure if the following is possible. I would like to link these cells so that they stay linked even if the file name of the source cell changes. I am not sure if thats possible or how to go about it if it is. This sort of thing is possible (although a different concept) in the Solid Modeling program Solidworks. Often times assemblies are created using a number of files. When the filename of one subcomponent is changed, or the file is relocated the final assembly will no longer work. So a method has been created to automatically update the link if the file is moved or renamed.
I would like to be able to do this in excel. ( I should note that I was only using Solidworks as a reference, it really has nothing to do with what I want out of excel, the data will not have anything to do with solidworks or its files, it will be simple numbers letters and formulas that I wish to link between workbooks)
Working on a simple worksheet_change issue for someone, the thought came, "after this code runs" it would be good if the TargetRange changed to no longer include the rows it had run on already. This way, if you later accidentally deleted/edited a cell that already had data, it wouldn't cause the macro to run again and do the paste job again?
Is there a way to permanently hide the toolbars and protect the workbook that when others open the document, they will not be able to view or have access to this.
I am attempting to run a macro that will make conditional formatting I have applied permanent, and then remove the conditional formatting from the cell. My application for this is a master fantasy football cheatsheet, with multiple columns of the same player names ranked in different orders. When a player is drafted I type their name in cell A1, and conditional formatting is setup on so that name (presumably once in each column) will be highlighted/bolded/etc. For example, if I typed "LaDainian Tomlinson" in cell A1, I would be able to find his name in each list/column (from the conditional formatting that will make it stand out), and then run the macro to make this formatting permanent (show he has been drafted), and remove the conditional formatting code, leaving the effects. I found a macro online to do this at the following link, but am having some issues:
When I run the macro on any cells not containing conditional formatting, I get the message: "The formatting based on the conditions in the range (whatever is highlighted) has been made standard for those cells and the conditional formatting has been removed."
This messsage is precisely what I am trying to do, however, when I run the macro on a cell with conditional formatting (when it would actually be beneficial), I get "Run-time error '13' Type Mismatch"
When I go to debug, the error appears to be on the line
I am tracking baseball batting statistics. The first column in each row is the player's name and the next column is the date. Several columns of statistics follow. I enter the batting stats game by game. I know I can use the SUBTOTAL function combined with AutoFilter to view each player's statistics individually totalled, but what I'd like is to see that subtotal line permanently displayed for each player.
There is a macro i wrote that assigns buttons to different tasks, and it changes the name of each button to whatever task the user utilizes it for. I would like for the changes to be permanent, and i can do it if i can just keep the information in the userform. So, the most logical solution is to permanently change the names of the buttons in the userform, but this has to be accomplished by the users choice, which means i don't do it through the vbe. How do you change a button's caption permanently through macro?
I have sent a data sheet template to my client in which they paste their data, based on this data sheet analysis will be done automatically in next sheet "Summary".
Whenever they paste product code, vlookup is not working since the product code contains few are numeric (shows: "Number Stored as Text"). I advised my client to go with "Text to Column" and "Delimited" to change it as numeric. But my client feels this method is tedious one. Is there any alternate way to make that column as numeric permanently?
I am trying to create a macro to copy a set of datas in a row , paste into another workbook (specific sheet),then save it as one of the datas that was copied over.
Workbook1
Ticket# Description Name
12345 Test 1 David
32145 Test 2 Steve
Workbook2 - sheet (ABC)
Copy row 2 from Workbook1, paste to row 1 in Workbook2 sheet(ABC)12345 Test 1 David
Saveas Workbook2 - ABC - "12345" - Ticket #
Copy row 3 from Workbook1, paste to row 1 in Workbook2 sheet(ABC)
And so on. I will have a set of data to trasfer to Workbook2, each row different workbook.
Does anyone have a workaround for the following error "Excel Could Not Save All the Data and Formatting" Error Message (http://support.microsoft.com/kb/215783). I am applying validation to many, many cells
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
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.
Using excel 2000 on windows XP (home). When saving data as a web page the grids do not show despite having the page setup "show grids" box ticked. It makes reading the file on line hard to follow.
There are two Combo boxes in the form. ComboBox1 is being populated from a named range - "ParticipantName". (Located in Sheet1!C2:C500) ComboBox2 has been populated with "No" and "Yes" with additem in Form1 Initialize.
There are 3 (Three) Command Buttons - "Save", "Cancel" and "Close".
"Cancel" shall clear data in both Combo Boxes. It is done. "Close" shall unload the Form. It is also done.
My problem is with the "Save" button. The user will select a name in the ComboBox1 and Select "Yes" or "No" in ComboBox2. Then if the user press "Save"; only the selected data of ComboBox2 will be written in the 26th Column (Column "Z") of Sheet1 in respective Row of the name selected in the ComboBox1.
1. User will input the file in the "INPUT SHEET", typing the Text in Root, Uric, Lot size, Lot Unit, Display name and Series name.
2. User will select the from the drop down for Category and Type.
3. After Inputting the data according to the category and type selected. it should select the data, and Replace the Root with the inputed data to "IDX MAIN SERIES" the 2nd sheet "DATA" Sheet. and it should save it in CSV format
4. Same thing goes with the "IDX CHAIN SERIES" in the second sheet. and it should also save it in CSV. format
5. so for each and every root there would be 2 CSV file one for Main Series and one for chain.
6. The Root type can be repeatable, so if there are multiple root the all Roots should be in main series csv file and all the chain should be in Chain series csv file.
7. Same thing for the modifer sheet it will replace the data highlighted in Yellow (Data updated in "INPUT SHEET" and should save in ".TXT" format
Company Name | Model # | Comments | ABC | 1234 | Good | DFG | 3245 | Bad | IUR | 8950 | Moderate | ABC | 2435 | Bad | IUR | 2432 | Moderate | . (could be >100 entries under 20 companies)
We need to group the above data by company, copy it into a new file, and send it to respective company...
Any macro can serve this purpose? Save .xls to a location named by company name will be perfect as we'll send the files by mail merge to email with attachment.
In addition, the headers need to be included in the new files too...
I have a problem when i need to save an excel workbook. I have 7 sheets, and in one of these (sheet3) i make 3 data validating list, based an if formula.
in cell C9 (sheet3) i make a data validation list where i tiped =IF($D$8="Turism",Norma_poluare,Norma_poluare2) in cell C10 (sheet3) i make a data validation list where i tiped =IF($D$8="Turism",Cilindree,$A$1) in cell C11 (sheet3) i make a data validation list where i tiped =IF($D$8="Turism",Emisii_CO2,$A$1)
The "Norma_poluare","Norma_poluare2" tabels are in sheet 4 and the "Cilindree","Emisii_CO2" tabels are in sheet 5.
When i need to save the excel document, i must have open sheet 3, othewise excel returns me an error "One or more cells in this workbook contains data validation rules which refer to values on other worksheets.These data validation will not be saved". So I thought that before saving date, automatically to shift focul to sheet3, and after these excel to save the data. VB code for these problem.
The current macro I have allows me to save 'sheet 2' as the name in cell sheet1 B2, which is a 6 digit number. I now want it to save as data from 2 cells, sheet1 B2 followed by sheet1 B1, which is job name (eg. 112233 microsoft excel).
This is the current formula I have:
Public Sub saveworkbook() Dim filename1 As String Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _ IgnorePrintAreas:=False Sheet1.Select filename1 = Range("b2") ActiveWorkbook.SaveAs filename:= _ "C:UsersAndrew.SDocumentsInterloc DocumentsHardware Sheets" & filename1 End Sub
I have a code in which we can split the data using column values into new and same sheet also. I want to modify this accordingly to my need but i am new in VBA so can't modify this. i want 2 changes in this macro:
1. I want to set a value (Like:"Resolved"," In Process" ) in the macro to extract the data related to the value only. Means, when i run this macro it will extract the data only for "Resolved" status. I have to create two macros for each defined status.
2. After splitting, it will ask me to save the new workbook location.
3. Save it.
The codes i m having are :
First code to make directories:
Code: Public Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long
I've set up a series of linked spreadsheets that feed data to an overall sheet. I work in a school, and each day the staff fill in student data, which then updates back into my overview spreadsheet.
Now the term has finished, I want to be able to store the data, then wipe the spreadsheets so they can be used for the new term (entering student data each day).
(There are about 20 different files I want to save, and don't want to have to copy/paste values for each sheet!)
I am pretty much a novice at this whole excel / vba stuff, but I have been trying! unfortunately I have now got well and truely stumped! I require the macro to perform a loop and once saved the first row of data, select the next row of data (in exactly the same way) until it reaches the last row. The macro I have created does the first part fine, but I have reached the end of my knowledge base!
Below is the macro I have created: Sub TEST_1() ' ' TEST_1 Macro ' Macro recorded 20/08/2009 by ..........' ' Sheets("Input").Select Range("A1:CI1").Select Selection.Copy Sheets("Results").Select Range("G3").Select ActiveWindow.LargeScroll ToRight:=7 Range("G3:CO3").Select..............
What I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
I would like to add some icons on the left side of excel open file pane to faciliate my work. Because i need to load some files under the same folder many times a day. Does anybody know how to do that? I've seen people has more icons on the pane before. The defaut setting has only 'History', 'My Documents', 'Favorites', 'Desktop' and ' My nutwork places' on it.
