I run a macro which runs saves the active file to a different location to ensure that I retain the original like so -
If ActiveWorkbook. Name = "KEY_DATA_CFT77" + ".xls" Then ActiveWorkbook.SaveAs Filename:= _ "H:HOME imcEXCELKEY_DATA_2006KEY_DATA_2006_01KEY_DATA_CFT77_01.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End If .................................
Hi all, I've reinstalled office 2007 because excel 2007 was crashing very often when inserting new rows or when saving the file. I tried with other files and apparently work fine. The specific file can be opened but I don't know if is corrupted, I tried repairing it with open and repair from the file open menu but didn't work (it is still happening the same). I've been working on the file during few days and wouldn't like to start from scrath again, can anyone help me with this?
I have a userform where the user clicks a command button to select a gif/jpeg using the getopenfile function, the file path a is then loaded in to a text box and loaded in an image. I noticed that when the user cancelled before selecting a file it caused a crash. I have added some code to prevent this but now it crashed when an image is selected.
Workbook A has VBA code. The VBA code opens a collection of 2000 workbooks. The workbooks are processed one at a time, with Workbook A opening Workbook B, do something, close Workbook B, get next workbook. Sometimes Workbook B has some kind of access issue that results in:
"[File Name] cannot be accessed. The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding. RETRY ... CANCEL"
If RETRY is clicked, the process returns the same message. If CANCEL is clicked, the process is crashed, Excel is closed, and all data processed is lost. What is the VBA syntax to programmatically trap this condition so it can be processed in an orderly manner?
Sheet 1 contains a column titled "name" and 6 columns to the right of name titled "sat", "sun", "mon"........ the col titled name is not the first col in the work sheet.
In each row I enter the guest name under the name col and I enter a room number (example: 1A, 1B, 2A, etc...) in the column to the right that coresponds with the day the guest will be staying.
Name Sat Sun Mon Tue Wed Thu Fri
Mr. Smith 1A 1A 1A 1A Mr. Jones 3B 3B 3B Ms. Tiller 4A 4A 4A 4A 4A 4A 4A
Sheet 2 is in the same workbook and looks something like this:
Room # Sat Sun Mon Tue Wed Thu Fri 1A 1B 2A 2B 3A 3B
I want it to look at sheet 1 and populate the date columns with the guest name from sheet 1
I tried this formula =IF('Sheet1'!G:G="1A",'Sheet1!$F:$F,"Available") It seems to work on the first row but I have problems with any rows below that. Basically it acts like there is no data in the rows below.... I think the fact that the names on sheet 1 are not alphabetical is creating part of my problem? I tried a lookup formula but it seems like it has to be the first column in the worksheet and it has to be alphabetical to work like that.
I am in a workbook created by the the macro (the variable name is strtempfile)
I then open a new workbook called Missing stocks.xls (this name never changes)
This workbook has vlookup links to the strtempfile.
I cannot work out a way to change these links.
I have tried the following =
Dim R, c, strtempfile R = 1 c = 1 strtempfile = D_temp_File_5
Do While cells(r, c) <> ""
cells(r, c +3+ = "=Vlookup([rc-3],[strtempfile.xls]sheet1!r1c1:r3000c17,17,false)
I get a false answer, even though the number being looked for does exist.
I have also played with the idea of simply changing the links and having the do while loop simply copying the cells with the updated formulas into any new rows that need them, but I am not sure how do do this either?
Let's say I have 2 files: Source file and working file. In source file there are some text names in a column that are updated once in a while.
1. I need to create a column in "working file" such that is taking values from column in "source file" even when "source file" is closed.
2. It should take only non empty values, because I need to create a cell with validation list that consists of text names from the column.
Solving attempt: By searching some solutions in forum I found that the first part I can do in the following way: copy column from "source file", select in "working file" a "paste special" option and choose "paste link". It works, but the problem is that it imports all the column: if in "source file" the column consists of words "a" (cell A1), "b" (cell A2) and all other cells in A column are empty - in "working file", after linking, it appears as "a" (cell A1), "b" (cell A2) and all other cells in A column are "0" (zeros) till cell A65536. And I need that in "working file" column after linking will appear as "a" (cell A1), "b" (cell A2) and all other cells will be empty, so by setting one of cells in B column to be a list (by "Data" - "Data validation" - "List" ) - it will consist only from "a" and "b", and not from "a", "b", "0", "0", "0", .... (65534 zeros).
I am tracking my sales interactions and am trying to link cells so when I update on one sheet it updates my last "customer touch" on a master sheet. Current formula-
On "sheet1" I selected a cell and used this formula... =sheet2!$b$7
The problem is that I want to keep a history of what was in that sheet 2 B7 cell so i insert a row (making B7 move down and become B8) and enter new info into the new b7. if I go back to sheet1 the formula in the selected cell becomes =sheet2!$b$8 I want it to stay to =sheet2!$b$7 no matter what I do... insert rows, delete rows etc...
in short what i am trying to do is have a cell in sheet1 show my last interaction with the customer and keep a history of in sheet2-infiniti.... with the caveat that my last interaction on sheet 2 etc... is listed first (thus the insert row)
I want to be able to associate the first row and first column of Sheet 2 to Sheet 1. I need to make sure the data in each row stays next to its corresponding cell in the first column, even with changes in the first column. The data that is not the labels in row 1 and column 1 are different between Sheet 2 and Sheet 1, however.
To clarify what I'm looking for, I attached an example excel file where I manually entered stuff for each cell that describes what I want to have automated:
The first row and first column of Sheet 2 are always equal to the first row and first column of Sheet 1. However, the data in between them is different. When I add the 'bb' cell into Sheet 1, the 'bb' cell needs to be automatically updated into Sheet 2 and a new row is inserted in both sheets. It is important that the corresponding data for each of a, b, c, d stays next to a, b, c, d, respectively in both sheets (i.e. I want a blank row next to the 'bb' cell in both sheet 1 and sheet 2).
EDIT: In the file that I have attached, there are four sets of data all on one sheet. In reality the 'Sheet 1' set would start on top left corner of Sheet 1 and the 'Sheet 2' set would start on top left corner sheet 2, I just put them this way so you can see them all at once.
So I have 2 spreadsheets of Car Inventory Data that I want to export into what will be printed out as Addendum Stickers...basically we're offering a new product with all vehicles and need to update the Sticker Price to reflect the change. The main worksheet with all of the data I'm trying to export is as follows:
Column A: Stock Number Example: (Column, Row A3) T12345
Column B: Model Number Example: (Column, Row B3) 1234A
Column C: VIN Number Example: (Column, Row C3) ABCDEFG12A3456789
Column E: New Product Cost Example: (Column, Row E3) $399
Column F: Model Name Example: (Column, Row G3) Camry ----
Now the price of the new product being added in is the same: $399. All data runs in rows (A3-E3, A4-E4, etc.). I AutoSum'ed each row and output the new calculated price into Column F next to each row of vehicles.
I want to take the Stock Number, Model Number, VIN Number, Retail Price, New Product Price and New Updated Total or Columns A-F and output them into multiple sheets:
I Am wondering if you can use a checkbox, to update a cell on a different sheet in the same workbook. CheckBox 1 is the box on say, Tab 2. And if it was ticked, would transfer the name to Say Sheet 2, cell B7.
I have a large workbook, split into sheets, with very complex formulas in it. I want one sheet at the end of this workbook to reflect all the data from the other sheets, if you know what I mean - so it is ALL the data from the different sheets, in one "all info" sheet.
I also want it to update automatically so I don't have to re-enter lots of data when updating the separate sheets. Is there any way of doing this? I have looked at some of the answers on here but I got very lost.
=SUMPRODUCT(--(Sheet2!$M$2:$M$11=Sheet1!$B$3),--(Sheet2!$N$2:$N$11=Sheet1!$B$4),Sheet2!$A$2:$A$11) inthe above quoted formula last part "Sheet2!$A$2:$A$11" is the data for Jan in sheet2, if I need Feb results I need to change it to "Sheet2!$B$2:$B$11"
Is there a way to make it dynamic by linking this to a cell on sheet1? Example if sheet1 A1 cell contains name of month, by changing that the results should also change?
I have a database (attached) that has data entered into the "Progression" sheet. This data is continually updated, and once each row has a section date, I have a macro that moves the data from "Progression" to the "Complete" sheet (and from there, into a specific month sheet). I AM REALLY CONCERNED with data accidentally getting deleted (especially when/after getting moved to "Complete"). I know that you can link cell values from one worksheet to another, but since my data is continually changing in the Progression and Complete sheets, the linkage wouldn't maintain the correct cell values from day to day. Is there a better approach to creating "backup" data in excel than this linkage option? Preferably something that runs in the background without my input?
An option could be that I move my data from Progression into both the Complete sheet and then a hidden "Master" sheet when I run the macro. If I chose that route, how would I modify my coding to include transferring to the Master sheet as well?
Lastly, a simple linkage between the Master sheet and another sheet in a second workbook should be sufficient as a backup method?
I am trying to create a summary sheet that automatically gets information from files in a folder.
Lets say I have two folders - Data and Summary. Data folder has many files. Files are added regularly. The files are named in systematic way in following format: [Unit Number][Type][Job Number]
Exampleas of Unit Number: 75845, JBKU238547-6, T-546 Types: ae, hydro, noret, refurb Examples of Job Number: 96789, 96780, 95847
The example of a filename will be like this: T-546refurb96780. Each file has a field called test date and up to 13 rows of information in tabular format.
Now, in my Summary file in folder Summary, I would like to automatically get values from all the files that are in the folder Data. If a file is added in the Data folder, the Summary file should automatically know that. The summary has to be sorted based on the test date.
I have two excel sheets, one has partial information (displayName, title, company, streetAddress, city, state, postalCode, Pager, homephone, fax) the other has the missing information that I need on the first sheet.
Sheet 1 (DB1, has partial info) and Sheet 2 (DB2, has the missing info). I need to somehow link these two, and what they both have in common is name. DB1 has "displayName" which is just First Name Last Name (e.g. John Smith). DB2 has First Name and Last Name, but i'll concatenate that to a new column named "displayName" ( which I assume will be needed to link? ).
The missing information in DB1 is title / streetAddress / city / postal code. DB2 has "Location" (which is a company code, and I want to replace the code with the address, city, postal code) and "Group" (which is title).
To make it easier, I could just do a find and replace on DB2 (e.g. A01-DFW-HWY67) and do it that way right? Or is there any easier way to do that?
Other than that though, how would I link DB1 and DB2, using displayName and fill out DB1 with the information from DB2?
I am trying to link data from a pivot table we have in excel to a separate excel sheet.
The pivot table contains an up to date list of our inventory, however, we want to take that data and put it in a cleaner looking excel that we can e-mail to customers and publish on our website. Going individually, through every product takes too much time to have an up to date stock every day. Its even difficult to manage if we want to update just once a week.
When I link the cells of the pivot table to the new excel sheet they are constantly changing values as people are constantly editing the filters of the pivot table to look up various things.
I have found this sales forecasting template from the Microsoft excel template section on the web, however, when i insert the new rows, it does not automatically update the "linked" sheets. It is the "detailed sales pipleline management sheet".
The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.
how I could add to the code so that it also inserts the file name in column c?
It would make it easier to track the data in column B.
VB: Sub CopyFromFile() Dim fPath As String Dim lRow As Long
I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.
The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.
I have an excel template that needs to be copied multiple times and each sheet needs be named according to a list in an excel spread sheet. I also have a formula in the template that needs the value copied instead of the formula.
I got this script from an site and tried it. It runs but I don't see any spread sheets.