Range Names Between Workbooks

Jun 21, 2007

In one workbook, i have a sheet with date like this:

TITLE
Data......

TITLE2
Data2....

In each case, the Data is a block of cells of varying number of columns and rows. Each of the data blocks is a named range. When I reference the ranges from a separate workbook, the results from calling the range don't work at all (return #VALUE!) UNLESS the layout of the second workbook is the same as the source data! so for example, if range Data is in cells A2...F5, then i can reference that data ONLY if i put my formula in cell A2...F5 on the second workbook. this appears downright ridiculous to me and i'm hoping someone can tell me what i'm doing wrong!

View 5 Replies


ADVERTISEMENT

Import Range From Multiple Workbooks With Names Listed In Column

Mar 31, 2008

I have been trying to use a FOR EACH C ...... NEXT loop, where each cell in the range is the beginning of a worksheet name that I need to import.

What I have got so far is:

Sub FindMyFiles()
Dim fs As FileSearch
Dim ImportWB As Workbook
Dim MasterWB As Workbook

Set fs = Application.FileSearch
Set MasterWB = ThisWorkbook

View 4 Replies View Related

Using Defined Names From Different Workbooks?

Sep 15, 2014

Is it possible to use defined names from other workbooks? I would like to use this defined name as part of a VBA code.

Example: ActiveChart.SeriesCollection(1).Values = Range(Name1)

where Name1 is a defined name from a different workbook

View 3 Replies View Related

Defined Names In Workbooks

Feb 24, 2009

I am currently overhauling a massive workbook, that previously relied on an absurd amount of named ranges in order to run vlookups. I no longer need these named ranges and would like to delete them to unclog the workbook. Currently I only see a way to delete them one by one. Is there a way to mass delete all of them?

View 9 Replies View Related

Merge Workbooks With Same Names

Feb 3, 2007

Each month I run 2 separate reports and have macros that break each report out by distributor. The end result is each spreadsheet creates a new tab and new workbook for each distributor’s information. Each spreadsheet has information for 30 distributors, so I end up with 60 new tabs and 60 new workbooks.

Example:
Spreadsheet one separates detailed sales information for:
Abc Company
123 Company
Misc. Company

Spreadsheet two separates summary sales information for:
Abc Company
123 Company
Misc. Company

Currently, I save each spreadsheets new workbooks in a different folder because each spreadsheet creates workbooks with the same distributor name. I then go back and open each spreadsheet with the same name and move a sheet from one workbook to the other and resave. Now I am trying to figure out the best way to merge these back together and am struggling with where to start.

I could keep opening each of the 2 new spreadsheets that are alike and copy or move sheets but with 30 new tabs to move to the matching spreadsheet it becomes time consuming. I’m hoping there’s a better way!

I’ve attached the Sales Detail Example and Sales Summary Example to see how the spreadsheets are being broken out but had to remove some data to trim down file sizes.

View 9 Replies View Related

How To Make Same Macro Run On Workbooks With Different Names

Apr 15, 2013

I tried recording a macro to automate some tasks I perform every day between two workbooks. While in one I want to go get data from the other. The problem I'm having is the name of the workbooks I want to run this on changes every time I download my data into Excel. The files always start with the same name but the date gets appended to it. Is there a way to modify the macro to just work with the two books I've got open?

View 3 Replies View Related

Linking Names To Closed Workbooks

Feb 14, 2007

I've named some cells in the source workbook, and linked these names to the destination workbook. Then I used the names in some formulas in the destination workbook, and it worked great. But when I closed the source workbook it doesnt work. WHen I open the source it works great, but I need for it to be able to link to the workbook when it is closed as well.

View 9 Replies View Related

VBA Macro - VLookup From 2 Workbooks With Changing Names

Feb 17, 2014

I have a user which needs to run a vlookup daily. There is some formatting involved, especially with 2 exported workbooks...

So I wanted to make her a macro to do it. So she exports 2 workbooks where the names change based off the date range she runs the data for.

I was thinking I wanted her to close excel completely, then run them in a specific order and do the vlookups based of Index Numbers.

I can do the entire macro except for the syntax of the vlookup. I think if i write what I need, you guys could put it in proper syntax:

=Vlookup(RC[-10],Workbooks(2).Sheets(1).Range(A:A),1,false)

View 2 Replies View Related

Close Workbooks (with Variable Names) Without Saving

Mar 7, 2014

I have completed a long macro, and at the end of the macro I want to close a few workbooks automatically without saving. These workbooks have been assigned variable names.

Is there a command line I can use, that will not prompt the user to save?

Variables:
MARM_fileNM
MARC_fileNM
MAKT_fileNM
Temp_fileNM

View 3 Replies View Related

Retrieving Data From Other Excel Workbooks With Various Names (without VB)

May 28, 2014

I would like to create a summary for the ordering history of each customers. The IT department will facilitate us to generate some raw data and I want to retrieve the data to the summary excel when I type the Ref No of the customer.

For example, I have the following raw data generated, in which the file name is "A123456":

Ref No
Name
Address

[Code]....

And I want to extract the data to the following summary. When I type "A123456" in the field "Ref Number" in this summary, it will automatically retrieve data from the corresponding raw file:

Trading Summary
Ref Number:
A123456

[Code]....

View 3 Replies View Related

Switching Between Workbooks Whose Names Are Stored As Variables

Jul 20, 2006

I'm having trouble finding a way to switch between two workbooks that I have open whose names are stored as variables. If the variable name were variable, for example, I have tried the following:

workbook(variable).activate
workbook.activate variable
workbook(variable).select
workbook.select variable

none of these work and I've tried a ton of other ways to get it to work but I just can't figure it out!

View 8 Replies View Related

Create Workbooks & Save-Name By Sheet Tab Names

Oct 10, 2006

Every month I work on an audit that has data from 35 different distributors. I have code below that puts each distributors audit/sales information on a new tab and each new tab is given the distributors name. This also creates a new workbook for each distributor.

When the new workbooks are created, how can I name each workbook with the distributor name it's being created for? Is it possible to predefine a file path to where these new workbooks will be saved?

The distributor names I'm using are in column AF.

Sub FormatList()
'The code below creates and names a new tab for each members info
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")


'extracts a list of member or distributor names
ws1.Columns("R:R"). AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("T1"), Unique:=True
r = Cells(Rows.Count, "T").End(xlUp).Row

'set up Criteria Area
Range("U1").Value = Range("R1").Value

For Each c In Range("T2:T" & r)
'adds the member name to the criteria area above
ws1.Range("U2").Value = c.Value
...............

View 9 Replies View Related

List Sheet Names From Opened Workbooks

Dec 2, 2006

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 ....

View 9 Replies View Related

Open Multiple Workbooks With Variable Names

Aug 17, 2007

I have data spanning many files which are named with the date on which they were created (so there are 31 files for August).
For example:
PL080107, PL080207, PL080307, PL080407...)

I'm trying to compile all of my data into one workbook, and have the macro to append each file to a list. Now I need the macro to either OPEN each file, or ACTIVATE each file so that the rest of the Macro can grab the necessary data.

I say Open or Activate, because I can MANUALLY Open a full month's worth of files if it's easier code. If not, I would like it to open and close each workbook on its own.

I have over 7 month's worth, so opening 31 files 7 times is better than opening over 200 files individually!

View 5 Replies View Related

Produce Numerous Workbooks Based On Filtered Names

Oct 25, 2012

I am trying to produce numerous workbooks based on a filtered name. I will attach a sample spreadsheet that has the data.

On the sheet we have engineer names. I basically want to filter them (not difficult with a macro) but then to copy the results to a new spreadsheet and save the workbook as the engineers name (ie J. Bloggs has 5 jobs so they are filtered and the results are dumped into a new workbook and then saved as J. Bloggs.xls). This will happen for all engineers.

I have though about doing it as a macro and I think that would give me the end result but we have around 20 engineers and these can sometimes go up and down. Is there anyway to do this automatically?

Test Cost report 1.xls

View 6 Replies View Related

Macro To Open Workbooks Of Multiple Specific Names?

May 2, 2014

I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...

[Code] ....

I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.

View 2 Replies View Related

Worksheet Names :: Loop Through All The Workbooks In A Network Folder

Oct 6, 2008

How would one loop through all the workbooks in a network folder and put all of the worksheet names from all of the workbooks into the cells of the current sheet (a local file).

View 6 Replies View Related

Create Multiple Workbooks With Names Based On List

Sep 2, 2007

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?

View 9 Replies View Related

Excel 2010 :: VBA / Creating Multiple Workbooks From A Template And List Of Names?

Aug 20, 2013

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.

View 9 Replies View Related

List All Sheets Names From Multiple Workbooks & Copy Transpose Certain Columns

Oct 7, 2009

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

View 7 Replies View Related

Create Workbooks From Sheets & Append Current Date To Saved Names

Apr 22, 2008

I have a workbook with multiple worksheets. First 4 tabs are the standard tabs and rest of the tabs are created based ona macro with the unique names. Now i want to create the separate workbook for each tab by its name and , date and time stamp in a C directory.

View 5 Replies View Related

Copy Cells Into Workbooks Names Mapped From Copy Cell

Aug 20, 2008

1. I have a list of data (Collated Data)
2. For every row in collated data I want to export the cells into a corresponding cell in my predetermined workbook (TAF Form), i.e, Cell C1 on Collated data goes into Cell D3 on TAF Form, cell D1 to cell I3 etc etc
3. Once all cells in one row have been copied into the TAF Form I want that TAF Form to save as "TAF Form & Employee Name" (which would come from cell D3).
4. I then want "TAF Form & Employee Name" to close.
5. I then want the Macro to do the same thing for Row 2, copy the cells, save the form, close the form
6. I want to do this for every row that I have (which varies).

Is this possible? If you have any more questions in terms of what I need, don't hesitate to ask.

View 5 Replies View Related

Replace Range Names In Formulas With Range References

Jul 7, 2007

I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?

View 9 Replies View Related

Select Range Using 2 Range Names

May 29, 2009

I know I'm not too bright, but I've really lost it here. Can someone give me the sintax to select all cells between two names: Start thru End

View 5 Replies View Related

Using Range Names

May 16, 2007

I have in column B a link to raw data on another sheet within the same workbook that returns the same of a segment (ie "MSEC"). In column A I have SUMIF statements that total up the volume associated with each segment (ie "MSEC") that is shown in the report. Each SUMIF total is named (through Insert/Name) something that includes the segment name (ie "MSEC_TA"). In column V I want to offer a share number which shows the percentage of the total (ie "MSEC_TA) that each item is. So, I want a formula that allows me to say "E6/the text in B6 & "_TA". Since the text in B6 is "MSEC", that would give me the formula of E6/MSEC_TA, which is the named range which holds the total.

View 9 Replies View Related

Matching First Name In Range Of Names

May 1, 2009

I'm trying to find a way to check if the first and last name that are the content of cell A are anywhere in cell B and beyond. I've included an example: http://tinypic.com/view.php?pic=28cpkr9&s=5. In the example A1 must match B1, C1 and C2.

View 5 Replies View Related

Inserting Into Range Names

Jun 28, 2012

Suppose that i have a table that displays different metrics for each month of the year. I'm dealing with waste management at a thrift store so I'm looking at the weight of donations salvaged, weight of donations sent to landfill and weight sold and so forth. It would look something like this..

weight sent to landfill
weight sold

january
500
450

february
350
390

march
400
789

In my dashboard I'd like to be able to sum the total weight salvaged for certain months like Jan-Feb or Jan-March.

I've named the ranges such as "weightsalvaged.Jan_Feb". So for the sum of the weight salvaged, I have the formula =sum(weightsalvaged.Jan_Feb)

I would like to have another cell where I write "Mar" and the name of the range in the formula automatically changes from

=sum(weightsalvaged.Jan_Feb) TO =sum(weightsalvaged.Jan_Mar)

View 3 Replies View Related

Delete Range Names

Nov 8, 2008

I inhereted a spreadsheet where the designer used range names for all formulas. i hate range names. is there any way i can delete all range names and have all formulas go back to showing cell references instead of range names? When i just go through and delete the range names, i get formula errors.

View 9 Replies View Related

VLookup And Range Names In VBA

May 23, 2009

I have the following
title = Application.WorksheetFunction.VLookup(Q, Range(QandA), 2, False)
QandA is a range that is defined in the active workbook. The code excutes without error but TITLE does not get a value because QandA is reported as empty (when I position my cursor over it).

I have also tried:

title = Application.WorksheetFunction.VLookup(Q, Range("QandA"), 2, False)

View 9 Replies View Related

Hide Range Names

Nov 21, 2007

Suppose I have ten Names in Excel, and I dont like any one see them, How can I hide them (Names not cells)?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved