I am redesigning a file with a list of too many links to simply list by hand, so I was hoping there was an Excel shortcut or some handy code that would help me generate a list of all workbooks linked to this one file.
View 9 RepliesI have a a table linked between 2 workbooks.. when one updates, I want the other to update..
I have it working, however it will only work when both workbooks are open at the same time. I have it set to refresh on open however it won't work.
Is there away I can create a macro to control this..
I'm still using Excel 97. I know I'm a dinosaur but I find it does what I want, just like a Ford Model T would.
I have one workbook (workbook "A") which links to another workbook (workbook "B") when "A" opens.
If I now update and save "B" I find I need to close "A" and re-open it to get the updated data from "B".
Is there anything which might be called "update field"
I have workbook 1 with information.
I have workbook 2 that contains cells that are linked to workbook1.
I have workbook 3 that contains cells that are linked to workbook2.
When I open workbook3 I would like it to be update without opening first workbooks 1 or 2.
Can it be done?
I have a set up a workbook which is linked to numerous other workbooks to provide a summary of information in one location.
However, when we reach April the names of all the linked worksheets will change from 'name 09 - 10' to 'name 10 - 11'.
What is the best way of handling this transition, will I have to manually change all of the links?
I have attached a much simplified example, unfortunately I cannot post the original due to sensitive data.
Apologies if the explanation isnt clear enough, let me know and I'll give further detail.
I am trying to write some VBA that will select a row of cells that each have links to cells in another workbook, and then either autofill the formulas down (or pastespecial them down) for 20 rows. I have that part down using either pastespecial or autofill, but -
Since the forumulas are links to another workbook, Excel wants to resolve that link to calculate the values at the time of the pastespecial or autfill. If the sourced workbook is not open, the "browse for file" popup displays. I don't want the popup to display and would rather just get the #REF in the pasted cells and let it resolve the next time the sourced workbook is open. The end result would be like clicking "cancel" to the "browse for file popup", which I'm tired of constantly doing... I would just like to skip that popup all together and get the #REF value.
I would like to make one drop down list work with another. Is this possiable?
For ex: IF I have a list car companys in one list, I would like for me to select a certin brand in one list and all the models of that brand appear in another.
I have a list of movie titles A3-A9999(Infinity), their disc numbers in B3-B9999(Infinity). The titles being written in text, of course.
All I want to do, is have another list, in the same sheet in D3-D9999 that will be the same list as the original list that I input movies onto, except alphabetical and automatic from when I type in the movie title in the original list. When i just use "=A1, =A2, =A3 ~~~ =A999" in the D cells, then try to sort, it only sorts by the cell numbers, not by the actual text that I want it to.
Is there a way to do this? I know excel relatively well, but I'm definitely a noob compared to experts.
I have a list of names in column A with monthly budget figs next to them in columns B,C,D etc. At the bottom of the list there is a total spend summary on line 7. This spreadsheet is then pasted with links to a separate worksheet (see example)
If an additional name is added to the list in Row 7 in the original list, the total is now in row 8 - how do I ensure that the linked sheetl picks up this addition?
(I have a project where I am drawing data from numerous worksheets. I have paste linked them as tabs to a summary worksheet so that funtions such as SUMIF can update when the file is closed or open)
I have 2 columns in FINAL list: A product number and a price. The product number is full, the price one isn't and it's the one I want to fill. I have several other TEST lists with the same two columns, and both are full. I need to have excel check to see if a value in the product number column matches one in the FINAL list, and, if so, to copy the corresponding price value from the TEST list to the proper corresponding place in the FINAL list.
View 3 Replies View RelatedI created a spreadsheet with several combo boxes that that is linked to other data.
When this data is sorted, the values in the combo boxes are automatically changed.
Is there any way to prevent the the values to change?
I purchased the Excel Hacks book, and when attempting to do hack #53 with a drop down list, I can not get it to work. I do exactly what the book says to do, however, my chart and my drop down list are not linked together, and when I change a name in the drop down list, the information on the chart does not change.
View 2 Replies View RelatedI am using excel 2007
I am attempting to put a drop down list using "Data Validation". I can get it working when my list is on the same sheet (sheet 1) and the column of cells I want the drop down list to show up in....(you know..when the drop down list shows up in each individual cell)......BUT...when I put the list on another sheet (sheet 2) and try to do the "Data Validation" back on sheet 1, excel won't let me go highlight the list on sheet 2.
I even tried writing sheet2 and the range and that still doesnt work.
make a list of all Excel Workbooks in specific folder(My Documents). And once clicked, it will open the file immediately.
View 10 Replies View RelatedI want to show the name of opened workbooks in the list box, but the following code does not work.
for example, I opened 'a1.xls' and 'a2.xls'. The listbox of 'main.xls' should show their file name.
create a list of all open workbooks? I've found various examples of code that will do this in a Message Box, but what I am after is for them to be listed actually in the worksheet (let's say in column AA).
View 5 Replies View RelatedI have one Master Sheet that fetches information off of 30 other workbooks. The data is uniform accross all workbooks.
As of right now, I have code written that will update the master, but ONLY if the other workbooks are open.
Is it possbile to get the same data off of closed workbooks?
Here's what I'm using now:
Code:
Sub Master()
Windows("Master.xls").Activate
Dim Reps As Integer
Reps = Range("Reps").Column
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
[code]....
Variable "List" is the list of Workbook names that I'm using.
The code "Windows(List).Activate" is what I'm using to get the data from the other workbooks, but this only works when the workbooks are open.What can I replace this code with?
I have a macro that will go into a workbook and copy all applications from a given date onto a master workbook. I have another workbook that has a list of around 20 workbooks and i want the macro to go into each one, and copy over the data onto the master workbook. I have the code to copy it over but i can't get it to loop thru the list of 20 workbooks.
View 9 Replies View RelatedI am having a bit of trouble with something i am trying to do in my Excel workbook (which we will call the home workbook). Basically what the home workbook does is opens a number of different Excel workbooks that are located in a certain directory. It then finds the first blank cell in on the worksheet "Data Import" ...the cell adress is stored in a variable called found. I need to utilize the values that are in certain cells of the workbooks that the home workbook is opening, but I keep getting a 'subscript out of range' error. I am almost positive it has something to do with accessing the other workbooks. Here is the code I've come up with (well parts of it that are relevant):
Dim found As Range
Dim strFile As String
Dim strPath As String
strPath = "C:directoryPath" 'amend directory as appropriate
strFile = Dir(strPath & "*.xls") ' amend extension as appropriate
Do While strFile <> ""
I have two hundred workbooks in a folder located at "C:ozgrid". I'm trying to extract the information in cell B9 in each of these workbooks and input them into a new workbook starting in cell A1 and continue on down through cell A200. Each workbook name begins with "Summary" and has the format of "Summary XXXXXX.xls".
View 2 Replies View RelatedEvery two weeks: During Payroll, we have a list of managers and specific employees under that manager.
The managers name is in Column A and they repeat.
Normally we filter that manager, copy and paste the data into a new Workbook, save it, and send the info out to that manager. We do not want other managers to see the information provided to the specific manager.
This is a long tedious process as there are close to 30 managers.
1. Is there a way to run a macro (presumably based on a validation list) to filter, copy the worksheet into a new workbook for each of the 30+ managers?
2. Is there a way to save each with a specific name like "Payroll Feb11 - Manager Name.xlsx"
This code loops through all columns in all sheets in all workbooks of a specified directory.
My copy paste to column C in "Loop Folder" works well thanks to this forum especially wigi
I want to paste the file name once in Column A and have it repeat for each new file opened. My code only does this for the first one as I simply can not fathom the coding
In column B I'd like to paste the sheet name once as they get opened.
I am struggling here as well on Sheets.Name coding ....
I do a search on my directory for all *.xls files each month which produces a list some 11,000+ lines long. Attached is a very small sample.
I then import this into Excel – see tab Raw Data
What I want to produce is a list shown on tab Finished Data, so that I can then sort etc.
Has someone already done this before and got the code, or can someone point me in the right direction on how to arrive at the finished data?
I have some very tedious work to do in Excel:
table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .
S7999 u ee
S7999 w aa
My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?
I used the code below to successfully create workbooks from the filtered list using the below code provided on this forum (see link below). The only problem is that I want the resulting columns to fit to width.
Looping Through A Range - Use Result As Criteria In A Filter
The easiest way I can describe the scenario I am trying to create, is to use a company list of personnel (my index) and to generate a time-sheet workbook per person based on a pre-populated template. This time-sheet is saved as the persons name and has the persons name entered into cell D:10
I have a workbook which contains two worksheets;
1) An Index sheet which contains a list of names that I wish to use in Q16 downwards (note the length of this list will vary each time I run this)
2) A "template" sheet which I wish to duplicate in new workbooks
3) A second "data" sheet that I wish to copy across in new workbooks
I need a macro that will take the "template" and "data" sheets and copy it into a new workbook, renaming each new workbook to each name in my Index sheet. I also want that same Name to be copied into cell reference D:10 of the "template" each time.
The end result is that I should have a series of new files generated and saved which are named the same as the Index list, with both the "Template" sheet and the "Data" sheet present, with the cell D:10 pre-populated with the Name provided in the "Template" sheet.
I'm using Excel 2010.
I have 1000 workbooks in a folder, each workbooks format is same.
as of now I open each workbook, and check if values in A10 is equal to B10, that is if the value in A10 is "100" I will check if B10 is also "100" if not I will make a note the file name.
I have more than 1000 workbooks in a folder, It is really difficult to open and check if the values are same. is there a macro, which will check the condition A10 = B10, if not get the file name in one sheet.
Split off from Fill Multi Select ListBox With File Paths & Open Selected Files
if I can add a other request I just thought of.
If a sheet is also in one of the workbooks list, can I add a link directly to it?
For example, Workbook1 has a sheet named "MARCH".
When i'm working on sheet "MARCH" I want to be able to click on a button to see the Userform I created from the script above and see if the sheet "MARCH" is present from the other workbooks listed, if there is a sheet "MARCH" in the workbooks listed, I want another listbox to show what files contain sheet "MARCH" and link to them.
this may or may not be easy for some of you but its driving me nuts. Here's what I need to do.
1.Create a new workbook
2.Cycle through multiple workbooks and all worksheets within each workbook (all in the same folder). All workbooks will have the same structure.
3.Take the worksheet names and put them in rows
4.Take the data from the first column (column A) from the first worksheet of the first workbook and put them across the top (i.e. transpose the data). The first column will be the same in all workbooks so it doesn't matter where I get it from.
5.Copy the data from column E from each worksheet and paste them in rows in the new workbook (again transpose) corresponding to each worksheet.
6. Perform simple mathematical calculations at the end of each row.
I expect to have approximately 26 workbooks with a total of 7000 worksheets. In the target workbook (i.e. the new one), I expect there to be 7000 rows (corresponding to the 7000 worksheets) and about 260 columns.
The only reason I need to transpose stuff is because Excel 2007 doesn't have 7000 columns.
Here's an example of what it would look like.
Example worksheet (Input)-
Worksheet ABC
Col A Col E
1/1/2004 $25
1/8/2004 $30
1/15/2004 $15
Imagine another worksheet called LMN with the same ColA but different values in Col E.
Output workbook
ColA Columns B Column C Column D
Sheet 1/1/2004 1/8/ 2004 1/15/2004
ABC $25 $30 $15
LMN $xxx $yyy $zzz
My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook
[code]....
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Number
Project Description
...
1111E.000000001
[code]....
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.