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?
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
Trying to use a Defined Name as the cell reference in VBA. The idea is to change the value of a couple of other cells base on the value of the cell "FreightRow". I am starting with a Message box just to make sure the code was reading the cell reference.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim FR As Range
Set FR = ThisWorkbook.Names("FreightRow").RefersTo
If FR = 3 Then
MsgBox ("Help")
End If
End Sub
I have a column J that is named Premium. If I use the function =INT(Premium) in K4 then it returns the integer for the value in J4. If I do the same thing with a UDF then the function argument receives all the values in column J. The author of the function says this is how UDFs work by design. Is there a way to use defined names with UDFs and have it work the way it does with built in functions?
View 5 Replies View RelatedI use Vlookup's a lot so i define my range with specific names a lot. But when i want to clear the range names via Insert>Name>Define>"Select the range name then click delete. but instead of clicking on each range name, i would like to to delete all.
I can create a macro to do this but some days i use different range names that other days so the below will not work exactly.
Ive created the following code which I want to use to create a name in excel but it doesnt work.
when i put =INDIRECT i get an error and with out the = it places a text string in with speech marks on it e.g. defined name reference in excel reads ="INDIRECT('Health and Safety'!$AP$3)"
How do I get rid of those speech marks? the cell reference will change each time the macro runs and so will the sheet reference.
myC = ActiveCell.Address
Dim strNAME As String
strNAME = "YAXIS" & "_" & "HS" & Chartnumber
strSHEET = ActiveSheet.Name
ActiveWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:=strNAME, _
RefersToR1C1:="INDIRECT('" & strSHEET & "'!" & myC & ")"
How to delete all defined names from a workbook
View 14 Replies View RelatedI have several SumIf formulas that references a Cell in a Main Reference Tab. This is useful to me as I have over 100 tabs of data points and having a reference cell with that text is easier to change one time vs 100 times.
My question is this: Will Defined Names be "faster" than using a reference cell? With over 100 tabs, there's a lot of formulas and I'm looking for a faster way to get the same output without Excel Memory overload.
Why can't I alphabetize all of my defined name ranges in the Name box?
View 8 Replies View RelatedThis thread is related to this thread. Offset, Match, Max Formula In the Insert - Name - Define window:
If one wants to copy a formula from the "Refers to" box and the formula extends past the right side of the box, how does one use "select all" or mark the whole formula from beginning to end so it can be copied without messing up the formula. At the moment, when I try this, it changes the formula to include the active cell of the worksheet that's open when I use the Ctrl + c etc. I have tried Ctrl + Ins and Ctrl + a and Ctrl + c. None of these are working for me.
I have a situation where I copy a worksheet to further on in the same workbook (essentially, using it as a template to create new sheets). I have certain cells that contain references to named ranges, e.g. on NewSheet1, cell $A$1 contains =StudyNo. Unfortunately, it shows as #NAME?. But, if I go to the formula bar and press enter, it resolves the name properly. I thougt maybe that automatic calculation was on manual but it is not. Why is this happening and what can I do to resolve it.
View 3 Replies View RelatedI want to be able populate a combobox with all defined ranges in the workbook?
View 4 Replies View RelatedI would really appreciate if someone could help me with the following problem:
I wrote this code, but for some reason it doesn't work. I am completely new to VBA and have no idea what went wrong:
as the title says, i need to rotate a range but to keep all defined names (single cell or range) consistent.
i.e.
lets say i have the following cells filled:
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
now i have a few defined ranges. lets say:
range name, refers to
mycell1row1, A1
mycell90roanything, B1
mysomething, C1
mysomthingrange, A1:F1
etc.
what i need in the end is this:
1 1 1 1 1 1 1
2 2 2 2 2 2 2
3 3 3 3 3 3 3
4 4 4 4 4 4 4
and for defined names:
mycell1row1, A1
mycell90roanything, A2
mysomething, A3
mysomthingrange, A1:A7
I created an addin that puts a formula in a cell to make use of a UDF in the addin. It works fine.
But when I make changes to my addin, save it as new name, load the new addin (same as the old but with new code for other things) and then open the workbook that had the UDF working, it now changes from
=customUDFfunction(1,2,3)
to
='C:Documents and SettingshomeuserApplication DataMicrosoftAddInsAddinV123.xla'!customUDFfunction(1,2,3)
The code I'm using to create the formula in the cell is
Cells(x, y).Formula = "=customUDFfunction(1,2,3)"
I'm assuming that when I create the formula in the cell it gets tagged somehow with the addin name and location. How to just put the formula with keeping it specific to the addin that created it?
I'm trying to use some defined names to create charts. Though it works at the very begin, some extremely weird things happen when I try to develop further .....
Attached please find the test file, which simplifies my real case but shows the same problem.
Test.xlsx
In the test file, three names are defined as below:
Line1: "=Xvalue*1"
Line2: "=Xvalue*2"
Xvalue: "=ROW(SampleChart!$A$1:$A$25)"
the chart is very sample: "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line1,1)"
So far, it works very well. However, I just simply can't add the second line into the chart, for example, "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line2,2)"
I canNOT even change "Line1" to "Line2" in the "SERIES" formula!
I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.
View 9 Replies View RelatedI've got a couple of user-defined functions that I coded in via one workbook (wkb A), but would like these functions to be accessible to any workbook (wkb B...Z). How is this done so that when I start a new blank wkb I can use these user-defined functions there as well?
View 9 Replies View RelatedI have multiple workbooks in a particular folder. Each workbook has only one worksheet named "Data". I need a VBA script that could open each file in the folder, copy only particular pre-defined cells that have been mentioned in the script, and then place it one below the other on a new sheet.
Ex, if the pre-defined cell is A2, C6, and D7. I want the code to open all the sheets in the folder one by one, copy A2, C6, and D7 of each workbook opened, and paste it one below the other on the new workbook that is running the macro.
As an add-on, it would also be great if the next column cud carry the file name of from which workbook the information was picked out.
The result thus in the new workbook wud be like...
A2's data | C6's data | D7's data | File Name
Andrew Male Married march3.xls
Peter Male Un-Married march4.xls
Susan Female Divorced april2.xls
I have created 500+ defined names that refer to worksheets that do not exist (yet). When I add the previously nonexistent worksheets, the defined names that refer to them are not "live" and do not work in functions where that name is used. If you go into the defined names and click on the ones that refer to the now existent worksheet, they start to work, but I was hoping there was a way around this step (e.g. a simple macro that says "update all defined names").
I created all the names so that the person who will eventually be using this will only have to add an appropriately named worksheet and put the data in...then everything will automatically work. I do not think adding names is difficult, but the person who will be using this does, so I want to avoid that person mucking around in my names.
I'm trying to simplify a spreadsheet i've been given by defining names for certain values. I'm using Excel 2013. Is there any way to have all of the newly defined names I've created for cells automatically be inserted into all of the formulas that exist in the spreadsheet?
Example.
Old formula in one sheet of a workbook looks up a cell in another sheet with the value of ='sheet1!A1
I've given the value of cell A1 in Sheet1 a name of dgwd.
How do I get every formula in the workbook that references 'sheet1!A1 to change the value within that formula to dgwd?
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.
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!
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 RelatedI'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 RelatedI 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)
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
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]....
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!
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
...............