I get sent a spreadsheet on a daily basis and the naming convention is the date, the format is like DDMMYY.xls. I'm trying to set this as a DIM.
I've scanned other threads and I am trying to incorporate what what I've seen, but because I'm not sure what I'm doing I think I have it slightly wrong. (I've also just thrown in too much code I think).
I have been trying to get the row number of a cell to be decided by the integer result of a function. My thought was to do something like this .........(D(MATCHxxxxxx))....... With "D" being the column and "MATCHxxxxx" representing some function that results in an integer.
View 3 Replies View RelatedI have got a named range, which gives me a list of all the students. I would like to put that data returned in named range in excell workbook.
For example:
named range called "Students" is getting populated from A1:A10 with values:
Student1
Student2
Student3
Student4
Student5
Student6
Student7
Student8
Student9
Student10
I would like to put this data in other worksheet.
and instead of putting all the students, it just puts only
Student7
Student8
Student9
Student10
I entered in named range name ("students") in all cells I wanted to put data in.
In my project I have two workbooks. I am working on getting one to pull data from another depending on which employee is selected from a drop down list.
TestLOG.xls contains a worksheet for each employee, with named sections within for various training the employee has, and down each row has information on the date this was received, initial, recurrent, etc. It is only this single worksheet for each employee that any data entry occurs, which makes things a lot more organized and efficient.
TestFORMS.xls is the workbook accessed by the records department which has different worksheets depending on what data is to be presented. When an employees name is selected, I need the form to pull the specific data from another workbook, and post it on the current worksheet. This get repeated a couple times to fill the adjacent columns of data. I can't just select the whole table from the other workbook because in this current worksheet for example, only specific columns are pulled from the other workbook.
To better explain the flow...
Current workbook is TestFORMS.xls
Current sheet is Test
Closed workbook is TestLOG.xls
In TestFORMS, sheet Test, when named cell Employee is selected with a value...
Then open TestLOG in the background and open sheet of same name as Employee, and cope range ACtype.
Back in TestFORMS, sheet Test, paste the ACtype data in the range named Type.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ename As String
Ename = Range("Employee").Value
If IsEmpty(Ename) Then Exit Sub
Workbooks("TestLOG.xls").Sheets(Ename).Range("ACtype").Select
Selection.Copy
Workbooks("TestFORMS.xls").Sheets("Test").Activate
Range("Type").PasteSpecial Paste:=xlPasteValues
End Sub
Within the same sub, I would add additional lines of code to copy and paste the other ranges needed on the current worksheet.
My current hangup is that I get an error at Range("ACtype") as that is not the proper way to call the range.
There's more work to be done with the macro yet, like having it pull the data with TestLOG closed, which I believe is done by listing the path to filename.
Then I need to maintain cell formatting on the new table when the data is posted to it.
Hopefully the final task is to get this working so that when a new employee is selected in TestFORMS, the previous employees data is cut and the new posted, as the worksheet is just printed out for each employee and put on hardfile.
I have two spreadsheets. The sourse spreadsheet already has many named ranges which i would like to use for the main spreadsheet to pick up its information. Is there an easy way when using vlookup or the like to name the range in the other spreadsheet?
View 5 Replies View RelatedI would like to know if their is a way to export about 100 Named Ranges from 1 workbook to another. I work with about 20 pricing templates and have just been tasked to start tracking about 100 of the fields out of each template. I named about 100 ranges on one of the templates and want to name those same fields on the other templates with the same names so I can build a macro to just pull all of those ranges from the different templates into a metric tracking workbook. My question is if there is a way to name those same ranges on the other workbooks w/o doing it manually. If I copy and paste or copy the worksheets from one book to another I will spend just as much time deleting links from the other workbook as well as updating the contract information.
All of the Ranges are Sheets that are named exactly the same and in the same exact cells on each sheet.
I want to copy a named range from an external workbook. Currently I am using some VBA copied from a recorded macro that first opend the 2nd workbook and then selects the named range, changes back to the original workbook and drops the copied range into the active workbook.
I would like to find a way to get around having to open the reference workbook, and instead simply point to the proper workbook name and named range.
I have VBA code that copies over several sheets from a workbook, but I'd rather not have all the named ranges come through. Whenever I run the macro it gives me this notice: "A formula or sheet you want to move or copy contains the name 'rngRegion2', which already exists on the destination worksheet..." The destination sheet doesn't originally have this range already, it runs into problems because the sheets I'm copying have rngRegion2 defined on different sheets. I'd like to try and avoid this problem all together by not copying named ranges if possible.
If thats not a viable option, is there code that I can use to tell it to automatically select "Yes - To use the name as defined in the destination sheet" as opposed to prompting the user to select yes/no?
I have a workbook with a globally defined named range "MyArray" which refers to a constant array = {"Item1","Item2","Item3"}. My general module contains the following
View 4 Replies View RelatedI have a named range within a sheet. It was pulled across when i performed a move/copy - create copy from another workbook. The name contains characters that cannot be displayed or recognised and so they are displayed as square
symbols. This causes me an issue as i cannot delete the name. Even if i paste the list names, i cannot copy/paste the name as the characters are not recognised. I also tried using a macro to delete all names, which did delete all names, with the exception of this one.
The VB code used was :
Sub Del_Names()
Dim myname As Name
For Each myname In ActiveWorkbook.Names
myname.Delete
Next myname
End Sub
I have two workbooks wkA and wkB. I have a named range in wkB called BName.. can someone tell me whether it is possible to set a range variable (say, raA) from wkA to this named range in wkB. I have tried something to the effect of set raA = wkB.range("BName") but this doesn't work.. I've also tried set raA = [wkB.name].range("BName") to no avail. I'm wondering whether named ranges can actually be read from other workbooks.
View 4 Replies View RelatedI have been using a piece of code to save a workbook and name it as the value in a cell, for a long time the value in the cell has been alphanumeric and all has worked well. I now need to save the workbook as a 6 digit number and it has to include the leading zeros.
VB:
Sub Name ()
Dim stBatch1 As String
Range("Batch").NumberFormat = "000000" [code]....
With 000001 in the cell called Batch all that gets returned is 1, I have tried changing stBatch1 from String to Integer and that returns -1, I have also moved the NumberFormat around to various places in the code and mostly it just returns True.
I have tried using PULL from Harlan Grove's posts to workaround this but am coming up with #VALUE errors.
Here's what I have:
Column B contains the acct # being referenced e.g. 5230
Column E="_"&Br where r is the row #
Column F=MATCH($B$3,INDIRECT(Er&"Rows")) where _acct#Rows is a named
range referring to an external workbook e.g. _5230Rows
Columns G-R=INDEX(INDIRECT($Er),$Fr,COLUMN(G$6)) where_acct# is a named
range referencing an external workbook e.g. _5230
I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R however it returns #REF!
I need a solution to replace INDIRECT so I do not have to have both workbooks open together. The named ranges are static, but reference external workbooks.
I need macro to find worksheets (tabs) starting with word 'sample' in name and delete all instances of worksheets starting with that word.
View 1 Replies View Relatedi have fixed headings in row 1. these could use up to 20 columns
in row 3 i could put data under any of the column heading
if i put data in any of the columns i would like a new worksheet created. the name of that worksheet to be the column heading not the data i have just entered. if no data is entered then no worksheet is created
What is the code to automatically save the new template worksheet in a folder and name defined in a couple of cells.
The folder name will be always be: "Q:AssetsCustomersfolder name"
I need the folder name to come from cell C3 on the spreadsheet and the filename to come from cell R3.
I have a spreadsheet that has 3 columns, date, id and amount for the whole year.
what I need to do is have a macro on another sheet or workbook that has an entry for month. when i enter the month, i need to get all the data for that particular month and then save it with the month name. i am not very sure how i can do this. pivot table does not work as i need to save a file each month.
I am trying to use a macro to move a range of excel tabs to the front of the workbook however some of the tabs could be missing due to no information.
If I simply record the macro by moving them manually if the procedure arrives at one that is missing it would report an error.
So in essence I need it to look for the tab and if its there move and if its not move onto the next until it arrives at the last one.
I'm using some workbook-scoped named formulas to define some dynamic ranges which will be referred to by numerous worksheets. The named ranges are defined like:
NAME: gTable_costDetailsEquipment
REFERS TO: =globalParameters!$B$5:INDEX(globalParameters!$B$5:$C$1048576,1+countAdjacentNonBlank
(globalParameters!$B$5,"down"),1+countAdjacentNonBlank(globalParameters!$B$5,"right"))
From either of the tabs "Reports" or "DOR_Template" the user can press the large "+" icon to add a report (which copies the template or the last report to a new sheet).
When this Sheet copy takes place, excel is repeating my named formulas - this time it's making LOCAL versions scoped to the newly made worksheet.
I've used this copy sheet trick before and have never had excel create new, locally scoped, named formula for each workbook level name.
I also just recently started using excel 2013, is this a problem with the new version? I've just never seen this problem, usually workbook-level names are NOT duplicated on sheet copy.
I am successfully opening a .csv file using a variable value stored in a named range in my Main file (the variable includes the directory and path).
I copy data from the .csv file to the Main file then I need to close the .csv file without saving but I want to do that by using the
Windows("xxxx").Activate
command where "xxxx" is the namedrange in my Main file which stores the .csv filename (without the directory and path prefix).
I can use the
ActiveWindow.ActivatePrevious
command but if I have another workbook open, this one closes instead of the .csv file I opened from the macro.
I realise this is probably very basic and I've searched the forums but can't find any identical postings.
I have a master sheet and 102 'advisor named sheets'. The master sheet is updated daily with information (number and text) along 1 row in different columns (A:W). I would like when the advisors name is typed (W) =joebloggs! for this whole row to be auto input on joebloggs sheet. There can be multiple of these entered daily for same person, so the information would need to populate on the next row so not to type over the previous entry.
View 3 Replies View RelatedI need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
View 2 Replies View RelatedI have a "Menu" workbook that has 1 sheet named "MyMenu" which only has macros and Hyperlinks on it.
I would like to add a second sheet to this workbook named "SetUp".
Next, as an example, I would have the user list 20 Excel Workbook.xls files on the 2nd sheet (SetUp sheet) in Cells A1 thru A20 (as my example). In column B, directly adjacent to each file, they would enter a generic name of their choice, representing each file. These are files that they would be using frequently. The file names would be entered in cells B1 thru B20.
Now, I need 20 macros on "MyMenu" that can open these appropriate files listed on the SetUp sheet. As an example, I could have 20 macros that are located in Column D on "MyMenu". They could be located in cells D5 thru D24.
2 Things I want these macros to do . . .
First . . .
The Generic name would be displayed in the appropriate cell in column D so that the user would know which workbook they are going to open.
And Second . . .
Clicking on the Generic name would open the associated file.
NOTE: If the user were to enter a new file name in one of the cells in column A and a new Generic name, the name for the macro on "MyMenu" would automatically be changed and the new file name would be opened when the macro was clicked.
RE-STATED:
What I am looking for is a way of running macros from "MyMenu" sheet by clicking on any paticular macro I want to run just like normal . . . but, I want the macro on "MyMenu" to open the file that is listed in column A of the SetUp sheet.
REASON:
"MyMenu" has to be a locked sheet. Therefore, the user can not enter or change macros on "MyMenu". I actually have 5 or 6 macros that I would like them to be able to change, but again, "MyMenu" has to be "Password" protected. Additionally, many of the users simply don't know anything about macros or hyperlinks
if I can use a named criteria as well as a named range. In essence what I am looking to do is count certain cells that meet the criteria in a certain named named range,
View 9 Replies View RelatedMerge two columns into one list in excel
I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).
Here's the formula from the example:
Code:
=IFERROR(INDEX(List1,ROWS(C1:$C$1)),IFERROR(INDEX(List2,ROWS(C1:$C$1)-ROWS(List1)),""))
I've played around with it, but could not come with any that worked.
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData()
Dim counter As Integer
counter = Sheets("Imported Data").Range("Counter")
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("imported data").Select
Range("a1").Select
i = 1
Do Until i = counter
Range(Cells(i, 1).Value) = Cells(i, 2)
i = i + 1
Loop
Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:
Code] .....
here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.
I'm trying to make my named ranges remember the values of the last active cells used within another named range. The purpose of this is to make my charts dynamically change dependant on two criteria selected. My spreadsheet currently updates itself as and when I change the active cell within a single named range, dynamically changing the chart data by using Lookup based on the active cells value. However I want to get away from having several charts showing, I would like to have a single chart which dynamically changes based on a second selection. So the first selection is for a department (Facility) which changes the chart data relevant to that department, the second selection is to dynamically change the chart shown for the pre selected department.
Picture2.jpg
Using the following code when updating just one criteria with several charts
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, [MeasureType]) Is Nothing Then
[valMeasurePicked] = ActiveCell.Value [code].....
which works fine but I'm not sure how to add a second selection criteria because my code uses Activecell. I thought that the VBA needed to set the last used value of a range as a variable and therefore allow the second criteria to be selected but am not sure how to put it into practice.
So I created a list
A
B
C
D
and named it "Alphabet"
but now I have E and F and would like to make the list
A
B
C
D
E
F
and name it "Alphabet" again
however, when I select it all and make it Alphabet, it selects A to D automatically
is there any way I could make A - F named Alphabet?
I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.
However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.
What part of code do I need to change and to what to make it work for just 2 named ranges ?