When i want delete all my data on my sheet 'Export Freshbooks' and replace it whit new data it says.Data lost.PNG
So i click on ok then... but when i do this all my Formulas Are gone....
My Formulas must stay no matter what.
Test Version - Copy.xlsm
On some worksheets and spreadsheets, when I save the formulas disappear, only to be replaced by the previously calculated values. It has become a real pain.
I assume this is something simple.
We have an excel template with numerous VBA and Macros embedded within it, saved on a central drive. I am able to access this file ok, however, when I save it when it is reopened by either myself, or a colleague, we get the "File error: data may have been lost" message. Everyone else within my organisation can open the template, edit, and save and subsequently reopen the spreadsheet without any problems at all.
One thing I have noticed, which to me seems strange is that the files I save that have the "error" message are slightly smaller in size than the ones my colleagues save (350kb vs 417kb)
My IT department have tried unintalling Excel and reinstalling, and also rebuilding my profile, all to no avail. A trainer has sat with me to check I am not using the spreadsheet incorrectly (or differently to anyone else, and this is not the case).
I know, I know... should've backed it up... there exists no other copy of the file in question newer than it's initial creation two weeks ago (been working on it solidly since).
The data does appear to be there but, rather mixed up... does anyone know of any methods/tools/software that might be able to salvage as much of the data (needs to be in it's original structure as it's a cross match excersice) as humanly possible?
I have created some Excel files using Excel 2000 on Windows Server 2003. The Excel version is given as 9.0.6926 SP-3. When I open them using Excel 2000 in Windows 2000 after a few sheet deletions I get the error message "File Error: Data May Have Been Lost" when opening the files. The Excel version is given as 9.0.8948 SP-3 in Windows 2000 (why is it different to the version number in Server 2003? Could this be part of the problem?) It crashes on this line
If Sheets(3). Name <> "template" Then
with the error "Run time error 32809 Application defined or object defined error".
The template sheet is very hidden. When I try and unhide it I get the 32809 error again.
I'm working with a list of different manufacturer part numbers, that must be represented in TEXT format only! The file with manufacturer part numbers exported from ERP system to TXT/CSV formats. When I open it in Excel , manually in "Text Import Wizard" I define Column Data Format as Text.
Examples:
1. part number 3214-4-5 turns to 05/04/3214
2. part number 0005487 turns to 5487
3. part number 223878615654 turns to 2.23879E+11
4. part number 4303.240600 turns to 4303.2406
Is anyway to do it automatically from VBA? I try to record macro , but it does not contain "text format".
I can still do a pivot report against the 'saved' data. So it must be out there in a file...? I read the function GETPIVOTDATA. Based on the description, It sounds like it would work to rebuild the source data. However the examples given don't see to match up with that. I have tried playing with the command, but I really don't know what I am doing and have had no luck
Does anyone know of a way to retrieve that stored data that the pivot reports are reporting against?
I have data in sheet 1 like below
Custid loc city
123 us newyork
124 uk wales
876 in mumbai
Sheet 2
custid newcustomerid
123 756
124 394
Outputsheet:
Custid loc city
756 us newyork
394 uk wales
876 in mumbai
So it will check sheet1 custid with sheet2 custid. If both matches it will replace custid with newcustid.if cust id not exists it will load sheet1 recprds as usual.
What it does is to activate the desired worksheet, and for 3 different choices replace what's there with what I want to be there. I put this together a while back, and it's working fine, but I'm sure it can be cleaned up a bit. Any help would be appreciated.
View 9 Replies View RelatedI have a monthly report with over 2,000 entries per column and it is dynamic (number changes monthly). I'm trying to replace individual department names with "department" but keep certain ones the same. For instance- A,B,C,D stay the same but E-Z need to be changed to "department". I first coded an If then macro but was unable to get it to loop and after researching looping realized that it may not be the best option especially with large datasets. Is an advanced filter macro the best option where I filter NOT A,B,C,D and then change the results to department?
View 9 Replies View RelatedWe have a report with 2 different sheets. On sheet one there are 13,000 rows of data and columns A-H are filled with different data. column A is our item id and column G has a date that states when the item was sold last. Well, the date in column G is wrong for about 7800 rows and needs changed.
The second sheet in the file is the 7800 rows with just the item id in column A and the correct date in column B. We are looking for an easy way to replace the rows in the first sheet with the correct information from the rows in the second sheet without manually replacing it line by line.
I almost forgot to add in, that in both sheets, column A with the item ids are the same item ids on both those sheets. So column A on sheet one has 13000 rows and included in those 13000 are the 7800 item ids on sheet 2.
We tried using the VLookup function that is incorporated into excel but had absolutly no luck, mostly because we have never used this function before. Sheet one already has all the lines of sheet 2, its just that sheet one has the wrong dates for the item ids and sheet 2 has the correct dates. im trying to replace the dates on sheet one from the dates on sheet 2 matching on the item ids from both sheets......
need to add same data to every other existing cell in the column, but not replace the data already in it, but to add to it. I've tried to google the answer and look here, but I probably use bad search terms.
For example, I need to add "QW" after each of these lines:
data1432
data9292
data3933
data3939
so it would look like this:
data1432QW
data9292QW
data3933QW
data3939QW
I have a few thousand rows of data, so wouldn't rather not do it manually cell by cell by typing :-)
It might be a simple question but ive been trying for over a week now and i just cant do it (probably cos my knowledge of VB is useless )
What i need to do is:
I have 2 worksheets: worksheet 1 Articles and worksheet 2 New Prices.
Whats common on both worksheets is Reference Nš (In the case of worksheet 1 its column E and in worksheet 2 its B), what i need to do is replace the price corresponding to reference nš in worksheet 2 (In column E) with the price of the corresponding reference number in worksheet 1 (in column N).
Its a basic find and replace, but its a list of over 17.000 articles so the best way is VB, so i look through your lists and helps, but i just cant do it.
I have two sheets - sheet1 and sheet2. Everytime i run a macro in the sheet1and manually copy the rows from sheet1 {starting (A2:AI2)} and paste as values it in the sheet2 {starting (A2:AI2)}.
Then I ll delete the available datas in the sheet1 and will run a macro for different customer.
Again I need to repeat the same action (manually copy and paste the datas to sheet2) without replacing the existing data in the sheet1.
In simple I require a macro code where it copy the available information in sheet1 and paste it as values in the sheet2 without replacing the existing one.
Basically I've got a back end worksheet with a huge table full of data (hundreds of rows, tens of columns) containing the data for a load of different contracts (each contract is on a different row).
I have all the information I want displayed on a front sheet by means of very simple lookups which looks for one contract at a time to display that information. What I would like to do is to be able to alter that information on the front sheet which will then go back and lookup that entry in the big table and overwrite the old data with the new. Effectively I want the excel sheet to act as a user form, but without actually using a form (a requirement from the people I'm doing this for). I'm struggling to find out how to do this as I don't know which VB functions I would need to use.
Each contract has its own unique reference number, so really what I need is some sort of code that will look for that reference number in the back end table, then look for any differences between what's on the front sheet and on the back end, and then replace anything that's changed.
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
I have a workbook that is used by others and has been working reasonably well for several years. The users are youngsters who tend to play with it when a bit bored but I usually manage to put things right. They seem to have done something special this time.
The formatting for dates, time and money has gone and I cannot correct it. I have tried the usual formatting of cells, painting formats from other sheets, copying and pasting formats all to no avail. The whole sheet is effected so that new entries appear the same way although the correct format appears in the formula bar.
I don't know if this is relevant but the sheet relies on VBA form inputs. The code is password protected and only I know the password.
The PC is not connect to the internet or a network so I have ruled out a virus. (As far as I know, no floppies or USB sticks have been used on it either.)
Having recently started to get involved in Visual Basic I have managed to write some code that has removed all of my menus except for File and Help. In a blind panic I deleted what I think was the offending VB code in a vain attempt to return the menus. This didn't work. I am now stumped.
Also, I managed to change the way the Cell selection works when I press Return. When I press Return, Excel now selects the cell to the right of the one I was in rather than the one below it. Is there a way I can change this back?
I have been chasing this little devil for some time now and I'm fed up!!
I put a formula in cells formula bar and whenever it decides by itself poof its gone!!!
example formula would be say (in formula bar)
=SUMPRODUCT(--($A$5:$A$200>(h4/24)),--($A$5:$A$200<(h5/24)))
in say cell g31 with answer in cell 31.
Now it just dawn on me, this is an array correct so ctrol,shift, enter may be needed?? If so why do other cells with same idea work?
I just spent 8 hours of VBA coding completed and after exiting excel and re-opening the file, I have seem to lost all the 8 hours worth of VBA coding I just completed. For example, I created an extra module (Module 3) and that disappeared; I updated several SUB commands and even those did not update. My code was about accessing databases using the OpenDatabase method and I noticed that towards the end before I shut it, my connection to the database kept failing (hence the reason why I was closing it and reopening it).
View 3 Replies View RelatedI'm essentially trying to place a column that is largely based on another....a function that will calculate the following information
Carriers with 1 Vehicle - 25.00
Carriers with 2-4 Vehicles - 100.00
Carriers with 5-9 Vehicles - 200.00
Carriers with 10-24 vehicles - 400.00
Carriers with 25-49 vehicles - 750.00
Carriers with 50+ vehicles - 1500.00
In other words.
One column in my spreadsheet has number of vehicles. I want another column that will provide the according application fee as found in the information above.
I have two files: let's say File1 and File2.File1 has one column: datas are in a text format.I copy this column. I want to paste it in my second file.File2 is closed.I open it and i paste my column.
Here's my problem. If i let my first file opened, there's no problem(!!): my column is pasted as i want. My datas keep their original format.
But, if i close my first file and after, i open File2 and i paste my column, then my datas lose their original format.My column takes a numeric format.I can see it because my datas are on the left in the column.And i can chek it by a sum: if i do 400+200, for example, the result is 600. In the first case(text format), the result is 0.I don't understand why.If someone can explain me this situation, i'd be happy.I repeat: my second file is closed. I open it after my copy in the first file.
I might have hit a wrong key or button, which caused a part of the current sheet not being displayed.
First column shown is C, but cols A an B are still in the sheet.
How can I get cell A1 in the left hand corner again?
A IT Ordering system has been created using Excel, I am prompted to enable Macros, no problem there. The problem is that when I exit the worksheet using a formulas setup as an exit button, my Excel is displayed with my Toolbars missing. I have been unable right-click at the top and re-add these I am only presented with the option to minimise, close etc. 1. How can I replace my Toolbars? 2. Is there a formula fix so that I can use this worksheet without losing my toolbars?
View 3 Replies View RelatedThe thread title is the entire email message I got from a customer (thru the help desk). I have sent back asking her the following:
Is this happening in all excel documents you try or only some? Are you getting any sort of error message? Can you send me an Excel document you are having trouble with and tell me the exact actions you take?
While I am waiting for her answers, I thought I would ask here if anybody knew:What would cause this to happen?What sort of things I should look for?What other questions I could ask her?At the moment I do not know if she was in an Excel workbook that had vba code behind it or not. So my questions are for both what could be in vba code and what she could have done (without knowing) to cause this phenomenon
I have an excel spreadsheet with numbers and the format has to show zeroes at the beginning (e.g. 0011111) It is always going to be a seven digit number. I can format the cells to show the numbers with the leading zeros, but as soon as I save the file as a csv, the leading zeros are lost.
Does anyone know how to the zeros can be retained when the file is saved in csv format?
I have created a spreadsheet in the field of marine surveying. In that sheet, I have used a lot of macros (21 modules) and a lot of VBA, most of it provided by kind people on this forum. But what has happened now, is that when I use that particular worksheet, the 'Undo' menu item in the 'Edit' menu has become permanently grayed out and it says 'Can't Undo'.
In other words, I cannot undo, even if I have made the wrong entry in an unprotected cell. If I use any other sheet, or make a new sheet, everything returns to normal. So, is there any way I can re-gain the 'Undo' facility, while using that particular sheet. I am using Excel 2003 on a Windows XP Home (SP3) platform.
I have been working on some files in excel for several days, saving regularly, etc. but have run into a huge problem. I was marking cells with a background color to organize and track my work, but the files being saved were .csv's.
From my research, it seems that .csv's only save data, not formatting such as cell background colors.
However, while I was working on the files, they displayed the colors correctly etc.
I am hoping that there is some sort of temporary file created by excel that would allow me to re-open my work and have the coloring appear so I can save the work as an .xlsx file with formatting.
I have a summary sheet using a macro to summarise multiple other worksheets. However, when I run the macro, the summary sheet loses all formatting.
For example, in one column of the "target" worksheets, the data is entered on multiple lines within a single cell, using alt+shift. When I run the macro the formatting is lost and the data in this specific range is displayed in one long row in a single cell. Also, font formatting is also lost.
In the attached example, the top row shows the original formatting on the "target" worksheet, but below that is the formatting after the macro has run and this is what shows on the summary.
I need it to be formatted as on the original if possible.
Have a project going and have had a disturbing trend develop. I'm working in Excel 2003. Upon opening the file and enabling macros, I started receiving File/Path errors, at which point it would shut down the application. When opened, I have a splash that would come up, but it wouldn't go to the next userform. At this point I would get the error message and the file would be closed. If I then reopened the file, disabled macros, saved under a different file name, and then reopened with macros enabled, everything worked fine, until I shut it down and then tried to reopen, at which point the same error would occur.
Lately I have simply been getting the Microsoft Excel dialogue box telling me an unexpected error occurred and it had to be shut down. I'm at a loss for what is causing this. The last time this happened, when Excel recovered the file, it told me a repair had been done, and the repair was to basically delete all of the VBA code from the file.