Naming A Range In VBA
Jun 6, 2007
I want to use the range in this code that works fine in one work book from sheet to sheet...
But i need it to run across 2 workbooks that are both open at the same time... The data its bringing back will be in a seperate workbook.
Sub zzzz()
Sheets("Sheet2").Select
Range("A3:M16").ClearContents
c = 3 'Paste Range Return Test
x = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row 'data source
For a = 1 To x
If Worksheets("sheet1").Cells(a, 3) = Worksheets("sheet2").Range("a1") Then
For b = 1 To 20
Cells(c, b) = Worksheets("sheet1").Cells(a, b)
Next b
c = c + 1
End If
Next a
'Range("C3:C15,F3:F15,I3:I15").Select
'Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub
View 9 Replies
ADVERTISEMENT
Jan 31, 2007
A userform listbox places names in a column in Sheet2, starting with cell E1 and subsequent transfers from the listbox (which is multiselect) going to F1, G1 etc. There will be between 6 and 15 names in each case.
A seperate textbox is used to assign a number to each multiselection, this number is added to "Div" and placed in row 22 of the column to which the multiselection is transfered.
The text in this cell is used for other things later but I also want to use it to Name the range to which it refers. This name is then immediately used to populate another listbox in the userform, and will be used repeatedly later on in the project.
Here is the relavant snip of code that I have been trying to get to work but I get 'object required' for the ActiveWorkbook.Names.Add.......... line. I did manage to get rid of that once (can't recall how!!) but then the ListBox2,RowSource gave an error.
Set DvNm = Sheet2.Range("IV1").End(xlToLeft).Offset(21, 0)
Set Rngc = Sheet2.Range("IV1").End(xlToLeft)
Set Rngp = Sheet2.Range("D65536").End(xlUp).Offset(1, 0)
DvNm.Value = "Div" & TextBox1.Value
Range(Rngc, Rngc.Offset(15, 0)).Copy
Rngp.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:=DvMn.Value, RefersToR1C1:="Range(Rngc, Rngc.Offset(15, 0))"
View 5 Replies
View Related
Mar 26, 2008
After pasting a Range and while the Range is still selected, I would like to Name it. May I have some assistance with the proper statement?
View 9 Replies
View Related
May 16, 2006
I am making a simple database worksheet for the junior. The first thing we need to re-define the new "size" of the database to do some future calculations. I write some code in workbook open to initiate the name area:
Worksheets("Inventory").Activate
maxrow = 0
maxrow = Range("a7"). CurrentRegion.Rows.Count
'Range("A7").Select
Range(Cells(8, "N"), Cells(maxrow, "N")).Name = "tbl_left"
when the junior re-open the workbook after closed, the size of the database
does not growth to a new area even junior add rows of data before close. It seems the currentregion does not work, I've tested it in VBE by press " F8".......
View 5 Replies
View Related
Jun 15, 2006
= OFFSET(CustomerInfo!$A$9,0,0, COUNTA(CustomerInfo!A:A),1)
for a "dynamic" range name...i like the concept, but my data validation in a cell that references this "dynamic" range name (with the offset function) doesn't recognize all the values in the range
(when i just range name it, the data validation sees all the names, but not all when i use the offset...)
View 5 Replies
View Related
Mar 15, 2007
I am trying to name a selected range in VBA in order to use it in a vlookup table. Code I have so far is as follows:
ActiveSheet.Name = TextN
Range("c9"). CurrentRegion.Select
Call formatting
ActiveWorkbook.Names.Add Name = "formTextN", RefersToR1C1 = Worksheets(TextN) & "!" & "r9c2:r45c13"
The formatting works, but the selection does not get named.
View 3 Replies
View Related
Oct 12, 2013
I am having a hard time creating my ultimate gradebook. Right now, I am able to hide a SPECIFIC table on a SPECIFIC worksheet using the following code on a macro button:
VB : Sub HideRow()Range("Table2[#All]").EntireRow.Hidden = True End Sub
The problem is that this sheet will be a template and as a new user inserts a new sheet, I would like to copy and paste this template to each new sheet. Since the Table values change with each new sheet, code wont work on the new tables. How I can name each new sheets table "Class Data", and the macro button to hide will work on each respective sheet?
View 2 Replies
View Related
Jan 22, 2009
Is there a way to name a range beginning with a number? It doesn't accept it. Is there a way around this? range name examples: 2DBSUS 3DBSUS
View 2 Replies
View Related
Feb 5, 2007
I have a range of data starting in C18, C17 is a label (DivsUsed) the number of rows can range from 1 to a few hundred.
I used the code below to name C18 onwards as a named range with the name DivsUsed and then to use this as a RowSource for a ListBox.
Private Sub UserForm_Initialize()
Dim rangeToName As Range
'Sheet2.Range("C18", Range("C65536").End(xlUp)).Name = "DivsUsed" Tried this too
Set rangeToName = Sheet2.Range("C17", Range("C65536").End(xlUp))
rangeToName.CreateNames Top:=True
ListBox2.RowSource = "DivsUsed"
TextBox2.Value = Sheet2.Range("F2").Value 'This works ok
End Sub
Both tries, and anything else I have tried, give the error " Method 'Range' of object _Worksheet failed".
View 7 Replies
View Related
Feb 5, 2007
i'm very new to excel formulas & statistics in general. i only need to calculate averages but am having trouble naming the range in my formulas
in the example below, i want to use the first column to determine the range of data for the mean calculation.... so, I am seeking the average of just the numbers which correspond w/ A only. Similarly, I will then want an average for numbers corresponding to B, and then to C, etc etc...
View 9 Replies
View Related
Mar 13, 2012
I am trying to name a Range in VBA with the following Case statement.
Select Case counter
Case "2": Set Wk(numWeeks) = Range("P1:Q136")
ActiveWorkbook.Names.Add Name:="Wk" & numWeeks, RefersTo:=Wk(numWeeks)
I have many more cases and have defined Wk(5) as a Range. What I am really after it is possible to to name the range using something similar to the code above that uses a predefined variable in the naming process.
View 4 Replies
View Related
Mar 23, 2012
I have a large file where I want to name about 30 ranges. Initially I had done this using the range addresses and it worked well, but I continue to modify the spreadsheet and add and delete rows, so I need to go back into the code and change the ranges manually. I know there has to be a better way.
What I wanted to try doing was finding some specific text. That text (in all the ranges) is actually one column to the left and 33 rows below where I want to start the named range. Additionally the named range will be a 10 x 33 range. For example, if the data starts in B1, the data to be named will be in B1:K33 and the search title will actually be in A34. Hopefully that's clear...
My original code looked like this:
Code:
ActiveWorkbook.Names.Add Name:="[MyRangeName]", RefersTo:=ActiveSheet.Range("B2:K33")
I was attempting to us the Find function with this code, but I can't seem to figure out how to tell it to look for the text (that would be in A34) and then name the range that would be offset by (-33,1) and then name the entire range.
View 3 Replies
View Related
Aug 6, 2006
How to you reference a named range in naming another range? What is the syntax?
I have a dynamic range, and I want a second dynamic range to start in relation to the last cell of the first range.
View 5 Replies
View Related
Apr 15, 2014
I need to name a dynamic range of cells. The only constant is the column - H, and the heading "MRC".
MRC column in a table represents an array formula. Unknown is the row where it is going to show up and the number of rows that this array formula will take. I need to name this range (active cells based on the array formula) but do not know what row does it start with and how many rows will it take.
It is not the last table in column H either but there are 2 empty rows before the next table.
Trying something like that...
=========================
Set aCell = Range("H:H").Find(What:="MRC", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
aCell.Offset(1, 0).Select
========================================
That's how I select the first cell in the range. Not sure how to select the whole range and name it ..
ActiveCell.End(1xDown) ?
View 3 Replies
View Related
Jul 25, 2006
Range("B25").Name = "EndMull"
Its fine but if i insert a new row or column then it mucks the whole thing up. Is there away of naming them but if any cells, row or columns are inserted the range will automatically adjust to suit1
View 2 Replies
View Related
Apr 13, 2009
on sheet 1 which is named front cover i want to type in cell C8 a number or a name and it will change sheet 2 tab to what i type i have 20 sheets to do
i can get it working in b5 on same sheet
but i need it to work from the front cover sheet
View 6 Replies
View Related
Sep 15, 2008
My workbook has a summary page and one page for every day of the month. I am making a template file, so I can duplicate it to use every month of the year.
Each week is separated by a worksheet titled "week 1", "week 2" , etc. This is used in the summary page so I can gather totals for a week.
What I want to be able to do is this:
On the first day of the new month, go to week one and type in the date. Then that sheet tab would equal the date, and each daily tab name thereafter would increment the appropriate amount (1day). Then on week 2, it would be equal to the tab value for Friday +3.
The problems I see are:
1. In week 1 the month can start on any on of the 5 worksheets I have set up to cover the week. So any first day coding I need may have to be repeated in the first 5 worksheets
Is this do-able?
2. Tab naming code I have found on this site is not working for dates. 9-15-08 typed into a1 will not translate into the tab, even tho it is a valid name. I am using code VoGII gave which follows: ...
View 9 Replies
View Related
Apr 30, 2009
Can a macro be written that will automatically NAME a sheet with the contents of a particular cell?
View 3 Replies
View Related
Aug 24, 2009
We utilize a shared spreadsheet in our department named "Arrangement Requests".
From time to time and seemingly very random the sheet renames itself. When this happens anyone adding information to it becomes unable to save since the original "no longer exists".
This causes problems because everything we do is in real time and expected to be viewed by all as soon as save has been indicated.
View 13 Replies
View Related
Dec 7, 2006
I have stored the name of 12 different userforms in a col of spreadsheet.
I want to use the text in these cells to choose the required Userform.
I have tried:
FormName=range("A12")
FormName.Show
but that gives run time error 424, 'Object Required'.
View 9 Replies
View Related
Jul 15, 2009
Ok I have an input box that takes a date as mm-dd-yy
View 14 Replies
View Related
Dec 1, 2009
make the auto naming code displayed on the worksheet tab die after first entry into, so that regardless whether any values are changed in the worksheet--a minute, hour, day after-- the "namedate" stays the same.
View 3 Replies
View Related
Feb 21, 2009
Attempting to modify a list of names. Currently they are last name, first but I want to compare to another list that is first name last.
Ex:
Billingsley, George
formula to change to:
George Billingsley (no comma)
View 5 Replies
View Related
Feb 16, 2012
Is there a way to name a new tab without knowing its name. For instance, when I create a new tab, it could be named "Sheet2" or "Sheet3", but there is sometimes no way to know. Is there a way to edit the name of that new tab without refering to it by name?
View 1 Replies
View Related
May 24, 2012
How do I name a Variable for a formula in VBA? Example: I want to name the results of Cells A1 + A2 = X. Then I want to add C1 + X = and make that results a variable less say Y
I have a very large formula and if I can name varaibles it would be much easier.
View 3 Replies
View Related
Feb 8, 2007
regarding this article:
[url]
Is there a way to name a tab with some combination of 2 or more cells?
A1="first"
B1="last"
so, the tab name would be "firstlast".
also, can a space be inserted?...i.e. A1&" "&B2
View 9 Replies
View Related
Jul 26, 2007
I have a spreadsheet which has four columns and numerous rows
Example:
Location - Desc - Price - ID#
Electric - Duplex Outlet - $5.00 - 12-0001
I would like to use VBA to name ranges. I need to name the Desc., Price, and ID# columns separately and want to use the Location and the headings of each column as the name range. Example - the column containing Duplex Outlet would be named ElectricDesc.
This is what I came up with, but need to figure out how to make "add name" a formula combining Location+Desc. I tried concatenate (B1,A2) but it did not work.
ActiveSheet.Names.Add Name:="MyRange1", RefersTo:="=$A$1:$B$10"
View 10 Replies
View Related
Aug 20, 2007
To automatically make the name of a sheet tab the same as in cell A8 of the same sheet, I'm currently using the following code ...
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
ActiveSheet.Name = Range("A8")
End Sub
however, I find that if I change the name that is in cell A8, the tab name doesn't change accordingly.
I need a code that will name the tab whatever is in cell A8 but also be fluid enough to change the tab name if the contents of cell A8 changes.
I do not want this code to operate all sheets, but only a few.
View 8 Replies
View Related
Apr 30, 2008
Is there a way to name a sheet in vba? I am creating new sheets in vba, and renaming these tabs, but is there a way to actually rename the sheet (so that in the properties window it says (Name) "Demand" AND Name "Demand" Instead of (Name) Sheet1, Name "Demand"?
View 9 Replies
View Related
Dec 22, 2008
Basically my code creates a load of checkboxes and I need to name them appropriately as they will be used in the next part of the code.
I haven't had a problem changing the caption properties etc but I need the name to be meaningful and unique - it works fine if I just want each checkbox to be called i.e. "CB1" etc but I want the name to be derived from a range within the worksheet.
Below is an extract from the
the_row = 10
For Each Item In ActiveSheet.OLEObjects
If Item.progID = "Forms.CheckBox.1" Then Item.Name = WorksheetFunction.Substitute(ActiveSheet.Range("C" & the_row).Value, " ", "_")
If Item.progID = "Forms.CheckBox.1" Then Item.Object.Caption = ""
If Item.progID = "Forms.CheckBox.1" Then Item.PrintObject = False
the_row = the_row + 1
Next
Column C contains the name which is unique to each checkbox, I simply need to replace any spaces with "_" but when I am not defining a set text string the name of the checkbox does not change.
View 9 Replies
View Related
