I have a estimating workbook with a number of worksheets to provide a estimating process. this includes the final worksheet with the estimate on.
This is fine if i am printing it to send to the customer or sending as a PDf.
However i need to save as a stand alone excel workbook. But all pre-formated and only with the values in it and not the formulas. It would be easiest for the users if it could be done with a button on the quote sheet (ie done with as macro as some users are not very IT literate and do mess up).
I have a code (which I have posted below) which I slightly modified and was kindly provided for me by 'antoka05'
It saves all my worksheets to seperate files which works great. :D
What I wanted to know is can I modify this code so that when it saves the worksheets to seperate workbooks that it just saves the values and not the formulas that are in the cells.
Also I need to remove the password from the sheets before they are saved, could this also be incorporated into the code?
'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
i have an excel table with embedded listboxes (Control Toolbar element). The user selects from listbox and has to send this file for approval. but when she saves the spreadsheet, the listbox-selection is reset to the first item. I know, that this is common for Excel, that the Listbox-selections are not saved. So I think the solution is:
1) to save the position of the shown listbox-item like
Cell(1,1).Value = Listbox1.TopIndex.Value
2) at reopening the saved file just to put this value into the Listbox like
Listbox1.TopIndex.Value = Cell (1,1).Value
But I don't know how to access this Control Toolbox objects from VBA. I've read some previous posts and then tried with
but it doesn't work. The simplest solution would be to use the LinkedCell property, but the users do not always click on the ListBox to select the Item, they just scroll with the mouse and then save the file, so that ListIndex and TopIndex are not the same.
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:
Below is the syntex I am using in the array... and the source file.. when opened will calculate the values. The problem arises when I close the source file and values return to this " #Value". My entire frontend file is nothing but arrays and links compiling data from multiple sources and everything works except cells that have the below array in them.
why my cell values will not save upon saving and closing both my frontend file and source file?
I have created a userform which is initiated off a button in her spreadsheet, where if she enters an order number, it will display the values from the row containing that order number. I have made it so she can load from the excel sheet the values into textboxes, where she can change values then save them back into the excel sheet.
I have also include the ability to insert a new row at the end for a new order.
Everything works fine except the date values and I'm a bit stumped, I have searched but can't find anything that helps (I thought DateSerial helped but it didn't).
I've had two problems. the first was that the day and month swapped, I could load 1st Mar 2007 from the excel sheet and when stored back it changed to 3rd Jan 2007. This I presume to be due to US and UK date formats, I am in the UK.
I am working with an Address Worksheet where the house#, street name and street type are 3 seperate columns. I need to combine that data into one "Address1" column. I can use a Concatenate() formula to combine the data, but I need to be able to have that data output to a new column, independent of the formula.
The problem that I am having is if I try to "copy" the concatenated data to either a new column or new worksheet it only wants to copy the formulas. I don't need the formulas anymore, I need the data.
Every so often I get a spreadsheet form some one else, and when I start to edit it formulas turn immediately into values.
For example, I enter a =10/2 ,when pressing enter, the cell displays correctly the result, 5. But when I click into the cell, instead of having the formula
=10/2, the only value inside is 5.
How do I keep this happening ??? I want the cells to keep the formulas.
How pasting values/formulas only works. I have a spreadsheet I've been using to record product sales, commission, shipping etc. It currently has three pages of records. But now I have a new spreadsheet I want to use with basically the same 'look' as the old one (same columns, entries per sheet, etc) but using better formulas. I don't want to manually input all the data from the old one and I thought I could copy all the cells from the old one and then paste values only into the new one. But when I do that, it pastes the values but also deletes the formulas in the new one. So then I thought I could copy all the cells from the new and paste only formulas into the old one but then it deletes all the values in the old one. Is this what is supposed to happen? And if so, is there a way to achieve what I want?
I need to ignore all data in a row (it won't be averaged, summed or anything else) if a particular value in that row is "0". I need to be able to see it but just not have it counted. Change the 0 to something else and all the data in that row is included in formulas as usual.
I have a column of values (Sold Price) for which I need to figure extra fees related to that cost, but they vary depending on the cost. The different fees associated with the Sold Price are: A. If SP is $.01 - $25, fee is 10% of SP B. If SP is $25.01 - $100, fee is 10% of 1st $25, plus 7% of remaining balance C. If SP is $100.01 - $1,000, fee is 10% of 1st $25, plus 7% of next $25.01 - 100.00, plus 5% of remaining balance over 100
So I need one formula to go into the fee column which will work with all values of the SP column.
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
I am trying to concatenate the values of many cells, not the formulas in the cells, to avoid having to repetatively paste special, value. Perhaps something like this: =CONCATENATE(VALUE(AA2):VALUE(AQ2))
I work for a bank and we use an excel spreadsheet that has an in depth payment calculator used for creating monthly payment arrangements or calculating settlements on bank loans. I would like to add the following feature and I'm not quite sure exactly how to do it.
For example, imagine cell A1 will contain the full payoff of a loan. I want to be able to put a settlement offer in B1 and have C1 populate the percentage of the payoff that is. At the same time, I want to be able to delete the settlement offer in B1 and put a percentage in C1 and have it calculate that settlement offer in B1. The calculations/formulas to do these problems is simple, but what I need is to not lose the formulas in B1 and C1 if I were to go back and forth entering settlement offers vs settlement percentages.
I am trying to copy data from an employee worksheet into a database that tracks the history of that data when they click submit. Everything is working well, except I can't figure out how to change the code to copy and paste values instead of the formulas.
Im looking for code to add new rows and copy the formulas only (not values) into the cells A, B, C, D and F. I have found code that does this in various places online but in every case I have found they duplicate the row then remove the values (so the formulas are left behind). The issue I have however is that it triggers some change event code I have on the sheet which interrupts the process.
Is it not possible to simply insert a new blank row, then copy down the formulas only without values?
I am copying the value in a cell to another sheet's cell. I want to convert the formulas on the second sheet to reflect their value. I do this all the time within cells by using Past Special and Values however it doesn't give me that option within the Text Box
I have the following macro that copies data from a calendar-style setup on one sheet and pastes it in a contiguous list on another sheet:
Code: Option ExplicitSub move_daily_data_to_ordersvstips() Dim OutSH As Worksheet Dim findit As Range
[Code]....
I would like only the values from the "Data by Month" sheet to paste to the "Orders vs Tips" sheet. However, all my attempts are returning various errors/inconsistent results.
I've developed a spreadsheet that shows what errors people made in their spreadsheets. I compare cell A1 in spreadsheet 1 (original) against cell A1 in spreadsheet 2 (final), in spreadsheet 3. Spreadsheet 3 shows a "-" if the cell they wrote was OK, and shows the new, correct value if they made an error, as follows:
=IF('Final'!A5='Original'!A5, "-",'Final'!A5)
The problem is, this only works if I take the original spreadsheets, which contain formulae in each cell, and paste them into a new spreadsheet using paste values.
Is it possible for the IF function to search the formula in a cell, and not the value?
i have about 100 worksheets in my workbook ..i need to combine them into one worksheet ...all my 100 worksheet has many formulas in some of the cells.
i want to combine all the worksheets one below the other with a gap of 5 blank rows after each worksheet contents....i just want the values in each worksheet to be pasted and no formulas...
I am trying to find a way to search for specific cell formulas (not the values they produce). For example, how could I search an Excel tab for a cell containing "= sum()" ? I want to ignore all other formulas and values. I then want to replace this formula only with its value.
I have what I thought was a very simple task; Change all date formulas in the workbook to values. I've accomplished this, but I was wondering if someone had a better way of doing without so many loops (I used two). NOTE - Dates are on different sheets within the workbook. I've tried setting each date on each sheet to one named range. I then tried doing this
this obviously didn't work. Anyhow, this is what I used that works, but I'm convinced that there is a better way.
Sub FinishOU() Dim sh As Worksheet, wb1 As Workbook, r As Range, TestRange As Range With Application . ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False Set wb1 = ActiveWorkbook With wb1 .Save For Each sh In Worksheets Set TestRange = sh.Range("A1:S4") For Each r In TestRange.SpecialCells(xlCellTypeFormulas).........................
I need to list only the unique values in a range found in multiple columns. Some cells are blank. I need to list all the unique values in numeric and/or alphanumeric order in another column within the same worksheet. I would prefer non-array formulas, if possible. See the attached file.
In the attached table, I have 5 columns and 5 rows of values in the range B2:F6. The unique values, in order, are 0, 1, 2, 3, 4, 5. This looks simple and can be done manually if the file is small but I have 150 columns and 150 rows, thus the need for automation.
