Reference Sheets With Variable Sheet Names

Dec 4, 2007

I have a workbook with multiple sheets. There is an overview sheet and then 14 sheets allowing for 14 days worth of schedule information. There are then 3 sheets following the 14 days to total some information. The workbook users frequently change the sheet names of the 14 sheets to reflect days of the week.

I'm creating a copy for distribution that takes the active workbook and copies the values to a new workbook. There are columns that I would like to delete from the 14 day sheets and that would be easy enough if the sheet names were never changed.

The code I'm using currently follows (my thanks to Turtle 44 for helping on that section)

Sub Copy_Visible_Sheets()
Dim arr() As String
Dim i As Integer
Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
Application. ScreenUpdating = False

'Make sure template is saved as .xls
If Not ThisWorkbook.Saved Then
MsgBox "Please save this workbook before generating a Client Copy."
Else

ADVERTISEMENT

Excel 2010 :: VBA - Hide Sheets Using Code Names Not Sheet Names

Oct 15, 2013

Code:

Sheets(Array("Sheet 1", "Sheet 2")).Visible = False

How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?

Want to ensure my code will work if the user changes the sheet name.

View 2 Replies View Related

Formula Across Several Sheets With Variable Names

Mar 31, 2014

Is there a way to use formula (Sum in this particular case) across multiple sheets with sheet names having a particular criteria?

The sheet names are 5 digits but not sequential (cost centre numbers). I'm trying to find the total if the sheet name (number) falls within a particular range (e.g. 150xx-152xx, 153xx-168xx), with around 60 or so sheets in total.

I know it's possible to specify the range myself, or to add a blank sheet as a marker/guide to create a "sandwich" of the required sheets (e.g. =SUM(Start:End!A1)), but the cost centre ranges may change if they're changed so I'm trying to find a way to identify them as variables rather than requiring maintenance.

Although the layout of the sheets should not change (and so a macro might work..?) I think I'd prefer to find a formula (if possible).

View 1 Replies View Related

Sum Across Sheets With Variable Cell Reference

Jul 9, 2008

i'm trying to use the sum across sheet function - i.e. - =SUM(Start:End!B1)
Whereby "Start" and "End" are the two relevant sheet names and "B1" is the cell I want added up.

However, can I make B1 variable, whereby there is a cell, say in A1, that contains the string "B1", so that I can make it sum C1 or B20 or anything on the other sheets?

View 9 Replies View Related

Variable Sheet Names In VBA

Feb 26, 2008

Running Windows CP; Excel 2003

The Board looks full of some interesting issues today.

I'm working on a workbook that hides and unhides a lot of columns depending on the month the user has selected. No problems here.

The sheets are named regionally, ie National 2008, California 2008 etc. The user is able to select the month and the year from separate combo boxes.

So when 2009 comes around I will need to rewrite many lines of code to point the VBA to the new worksheet names like National 2009, California 2009. My question is, is there a way to reference either variable worksheet names in the VBA or some other manner in which to write the code so I don't need to rewrite every year change?

I currently reference the sheets something like Sheets ("National 2008").Range("H:H").EntireColumn.Hidden=True

View 9 Replies View Related

Hyperlink Won't Keep Reference When Sheet Names Change?

Apr 30, 2014

VB:
Sub RenameTabs()
For i = 1 To Sheets.Count
If Worksheets(i).Range("C1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("C1").Value
End If
Next
End Sub

I change my tab names with the above but than my hyperlinks breaks.

View 4 Replies View Related

Referencing Sheet Code Names By Variable

Jul 18, 2006

I have a set of worksheets (Sheet31 through Sheet49 by codename) produced by a machine in the lab. I want to create a summary sheet that references cells in those worksheets.

Rather than go through the annoyance of doing this manually 40+ times I'm working on creating a macro to populate the summary sheet for me.

This is what I have so far

For J = 31 To 49
ResultSht = SheetJ.Name
Sheet1.Activate
ActiveSheet.Range("A4").End(xlDown).Offset(1, 0).Select

It hangs up on the "SheetJ.Name" however. My objective is to be using the object SheetJ, where the object SheetJ is the codename of the results sheet I'm trying to reference (for example, in the first pass through the loop it would be Sheet31). Could someone point out my mistake in this object reference?

View 9 Replies View Related

How To Reference Other Sheet Names In Workbook But Keep Cell References The Same

Oct 7, 2013

Within one workbook I have 15 sheets, 13 are for separate divisions within the company, 2 are used to present sums across the 13 sheets. All 13 sheets have identical columns and rows, with unique numbers in each cell. Right now I am manually selecting the cells to reference in my master sheet, =sheet name!cell+sheetname!cell+sheetname!cell - so and and so on. How can I reference the sheet name once, and then all of the cells to SUM? =sheetname!(c4+c14+c24+c34) etc?

This would let me be able to copy the formula from cell to cell, and only have to change the sheet name each time.

View 3 Replies View Related

Reference Sheet Names With Indirect Function To Make Formula?

May 21, 2014

Attached I have a document where I am wanting all of the individual sheet names on the Total page. Rather than having to change each formula to match the sheet name I believe there is a way to reference the sheet name column (AO) in the formula so you can drag it down to fill in the columns. I am basic with Excel and do not know how to add this indirect function into my current formulas.

View 5 Replies View Related

Use A Cell/variable As A Sheet Reference?

Jan 10, 2010

I have a formula that pulls data from another sheet using standard reference of sheetname! I want to build this forumla to use several sheets, and would like to pull the sheet names from other cells.

For example... instead of LEMONS!a1 and LIMES!B2

I would like to use REF1!a1 and REF2!B2

where REF1 and REF2 are pulled from cell Z1 which contains test 'Lemons' and cell Z2 which contains text 'Limes'

How can I do this?

View 9 Replies View Related

Referencing To Sheet Names While Consolidating Data From Various Sheets

Feb 23, 2014

I am writing a macro to consolidate data from different worksheets in more than one Summary Sheets.

My workbook has quite a number of worksheets, from different department e.g. OPS001, OPS002,OPS003, ADMIN001, ADMIN002, ADMIN003 and so on.

I want data from OPS001, OPS002 and OPS003 to go on one sheet e.g. "Summary-OPS" and data from ADMIN001, ADMIN002 and ADMIN003 to go on the other sheet name "Summary-Admin"

When I am working on Summary-Ops sheet I want to copy data from sheets starting with name "OPS" and so on.

View 3 Replies View Related

VBA Code For Listing The Sheet Names Of Deleted Sheets

Oct 20, 2008

I have a workbook with 20+ sheets in it, I add sheets and delete sheets on a daily basis, except for one sheet that is like my summary sheet.

Is there a code, formula, or magic spell that will list the names of the sheets that I have deleted? For instance, if my workbook has 50 sheets and I delete 49 of them, I want to see cells A1 thru A49 (or where ever I wish to place them) filled with the names of the sheets I just deleted.

View 9 Replies View Related

Sheet Indirection. Refer To Sheets With Changing Names

Feb 25, 2008

I've been referencing sheets in my code directly with things like

Sheet1. Range(NamedRange)

however in recreating a new workbook (to reduce bloat), the sheet-numbering has changed... to avoid having to mess around adjusting things again, I was wondering if it's possible to do some sort of indirection... something similar to

#define SHEET_SC_CONFIG Sheet1
#define SHEET_CUSTOMER Sheet2

'and then use these throughout the code....
SHEET_SC_CONFIG.Range(NamedRange)

(cannot reference by sheet name, as end user may change these)

View 6 Replies View Related

VBA Select Multiple Sheets Of Varied/Changing Sheet Names

May 18, 2006

How can I select sheets in a workbook based on the premise that the sheetname does not have the letter "Q" in it? New to forum, so apologies if format is not kosher.
I have a group of 50+ workbooks which are all set up in the following format:

-Contains 30+ sheets.
-Sheet names are varied, but follow a pattern. I try to keep the sheet names consistant on all workbooks so that it is easier to reference, but other users make this impossible.
-However, I managed to keep two things consistant: There are two categories of sheets. In one group all sheetnames have a "Q" in them and in the other group all sheetnames DON'T have a "Q" in them.

Here is my problem: I need to run a macro to perform retative tasks on the GROUP OF SHEETS WITHOUT A "Q". To begin the macro I need to select these sheets and copy them to a new workbook, but since the actual sheetnames are varied, I am having trouble coming up with a flexible way of selecting sheets. The closest I have come is using a IF ... LIKE ... THEN statement, but I can only get it to work to select the sheets with a "Q" in the name and not the opposite.

View 2 Replies View Related

How To Reference Cells In Another Sheet Which Remain When Sheets Are Sorted

Jan 9, 2014

I know how to reference cells from one sheet to another but for some reason I cannot for the life of me work out how to do this and that particular figure remains even after sorting sheets? Basically every time I reference some numbers its fine but when I sort the original sheet being referenced the figures are all wrong. I tried linking the cells and that didn't do it either?

View 5 Replies View Related

Summing Variable Values Across Variable Sheets In Multiple Columns

Jun 27, 2014

I need a macro that will create a sheet at the end of the workbook.

Sum data from a variable amount of sheets and display that data on the created sheet.

Here is a step by step:

Starting on sheet 5.

Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.

Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.

(Note: The data would also need started on row 4. Everything above row 4 is headers)

Here is a small example:

D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1

I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).

After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.

So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).

View 2 Replies View Related

Can Create Data Validation List Of Names Created In Name Box Or Sheet Tab Names

May 7, 2012

Can I create data validation list of the names created in the name box or of the sheet tab names?

View 5 Replies View Related

How To Reference A Variable Stored Within Another Variable

Jul 27, 2009

how to achieve what I'll call "parameter driven" code where the "parameters" are variable names.

For example, in the following code... I'm looking for an answer to the question posed...

Sub Test_Sub()
Dim vX, vY, z
vX = 10
vY = "vX"
For z = 1 To 3
'This next line generates a Run-time error... But...
'=====================================
MsgBox z * vY '

View 9 Replies View Related

Create Array Of File Names/sheet Names

May 1, 2008

Two part question:

1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?

2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).

View 9 Replies View Related

List Sheet Names And Internal Names Within Workbook

Feb 25, 2011

Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?

If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.

View 3 Replies View Related

Defined Names Vs Reference Cells?

Mar 23, 2012

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

View 9 Replies View Related

Concate Variable Names Using VBA

Jan 5, 2009

Here is the code loop that I have so far

For Each Dn In Rng
If Not .exists(Dn.Value) Then
.Add Dn.Value, ""

End If
Next Dn
I want to declare a varaible called peg and then for each Dn assign it to peg

so in the end for each Dn I should have peg1, peg2, etc, etc. Each of these should have a default value of zero that I will later modify.

View 9 Replies View Related

Setting Variable Names In Vba

Aug 10, 2009

I am working on a macro for excel which has a userform where the user puts in the number of samples they have, then takes them to a new userform to input the weights of the samples. I want to be able to set the sample mass variable dynamically based on the number of samples they have…

i.e.

spl1
spl2
spl3
.
.
.
splx

x=number of samples

This number will be different each time so I am stumped at how to do this since I have just started in vba and do not know too much yet.

View 9 Replies View Related

Variable Sheets, Variable Input

Mar 23, 2009

I run a machine that scans parts and is able to output the scans into Excel. Each part scanned creates a new sheet, and the number of sheets is variable as the number of parts scanned depends on the size of the lot. Also, since each part scanned is going to have a different number of features, the information being output on each sheet will be variable as well.

I am trying to figure out how to write a macro that will find the first feature, find the values for that feature I am looking for, output a max and min into a final sheet, and repeat for each feature, and for each sheet. I hope this is clear.

View 3 Replies View Related

Hyperlink Names In Sheet 1 To Same Names In Sheet Two?

Mar 27, 2014

I would like to hyperlink the names in sheet 1 to the same names in sheet two. Is there a way I could do this using a formula rather that do each one by one?

View 3 Replies View Related

Declaring Variable Names Dynamically

Jun 11, 2007

Is it possible to declare n variable names dynamically in a macro? For example: Cell A2 in Sheet1 contains the number of variables to be considered in the macro (n). I would like all the variables to take names in the macro from node1 to noden using the dim statement. I tried running a for loop:

View 14 Replies View Related

Allocating Employee Names With Unique Reference Number

Oct 31, 2008

I have been given a database with peoples names that require a unique number to hide/replace the name to hide their identity for a meeting and to act as a ghost system.

The problem i have is that some individuals names on the list appear more than once so i cannot simply call Joe Bloggs number 3 as he can appear further down the list at number 120.

I was wondering is there is a way to do this without manually replacing each name with a number and making and logging the persons name that corresponds to the number.

as there are 600 names and some can appear up to 4 - 5 times!

View 9 Replies View Related

Reference Different Concatenated Range Names In A LOOKUP Formula

Apr 2, 2009

I want to create a series of VLOOKUP commands with different lookup tables whose names are concatenated from two different pieces. Here's what I'm trying to do:

VLOOKUP(x,LUTWK01,y,false)
VLOOKUP(x,LUTWK02,y,false)
VLOOKUP(x,LUTWK03,y,false)

LUTWK01, etc., are named ranges. I have the text "WK01", "WK02", "WK03" stored in other places, and would like to be able to concatenate "LUT" and the particular week (e.g., "WK01"). The formula that I've created, which isn't working is:

VLOOKUP(x,CONCATENATE("LUT",A1),y, false) where cell A1=WK01.

When I pull out just the CONCATENATE portion, it resolves to LUTWK01, but apparently this is not recognized as a range name. Any idea as to how I make the VLOOKUP formula recognize a concatenated range name? Or is there a better way to do this?

View 2 Replies View Related

Adding Invalid Characters To Cell Reference Names?

Jun 18, 2013

I am currently using cell references to create a depedendent list with data validation. How to make cell references show up with some invalid characters?

For example, one of my cell references is Youngs_Market_Company. However, when pulling up the cell, I want it to be displayed as Young's Market Company.

View 2 Replies View Related

Uses Three Different Variable To Poll Up People's Names That Are Available For The Task

Dec 3, 2009

I've created a file that uses three different variable to poll up people's names that are available for the task. I've got it to work using long formulas, containing if, index, match, etc. It's working well, but I think that the spreedsheet will become difficult to maintain (delete or add people to). I'm looking for a way to simplify my code, so that it won't be as time consuming to edit.

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

Recognize Text Values As Variable Names

Jul 1, 2006

I want VBA to take an equation that has been entered into an worksheet cell as text and evaluate it. Here's a very simplified example that shows the concept:

Cell A1 may contain the text: "A * B + C"

I would like to do something on the order of this in VBA:

Sub Test()
Dim A As Integer, B As Integer, C As Integer

A = 2
B = 4
C = 6

MsgBox Evaluate(Workbooks("Book1").Sheets("Sheet1").Range("A1").Value)

End Sub

Obviously, this doesn't work, but I want it to show a result of 14. The actual situation is much more complex, but it all comes down to being able to evaluate the text in a worksheet and recognize the variable names that are in it.

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

Variable Reference

Dec 8, 2008

I have a list of employees in a spread sheet. I am trying to add a macro that will add to the bottom of the list. I have managers that are not familiar with excel so I do not want them to just add lines. I have written a macro that will add lines or subtract, but I want the macro to always add at the bottom of the speadsheet.

View 9 Replies View Related

Reference Variable With Value From Another

Mar 3, 2008

Is there any way of referring to a variable name based on the value of another variable? I have the following code: (where the value for "aux" is defined somewhere else)

Dim aux As Integer
Dim number1 As Integer
Dim number2 As Integer
Dim number3 As Integer

If aux = 1 Then number1 = 1
If aux = 2 Then number2 = 1
If aux = 3 Then number3 = 1

The variables "number1", "number2" and "number3" are always filled with the same value ("1"). Is there any way of writing the "If-part" of the code in only one line? I was thinking that maybe there is a way of calling "number1", "number2" or "number3" depending on the variable "aux". For instance writing something similar to: number + aux = 1

View 4 Replies View Related

Can Use Variable Names To Identify / Assign To Other Variables Or Arrays?

Apr 10, 2014

I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.

Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.

For example, under certain conditions

DataIdentifier would be set to "DataSetA" and under other conditions
DataIdentifier would be set to "DataSetB".

Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.

This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).

View 5 Replies View Related

Variable In Cell Reference

Apr 11, 2007

I have counted the number of rows using i. Now I need to pass that variable to the ActiveCell referances shown.

Do While Not Selection. Offset(i, -6) = ""
ActiveCell.FormulaR1C1 = "= round(R[i]C[-8],2)&""±""&round(R[i]C[-7],2)"
ActiveCell.Offset(0, 1) = "=round(R[i]C[-7],2)&""±""&round(R[i]C[-6],2)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

View 9 Replies View Related

Variable In Filename Reference

Apr 17, 2007

I'm working on a spreadsheet that will access two weeks worth of data at a time. Is it possible to have the filename reference contain a variable or a link to another cell in it?

For example: ...

View 6 Replies View Related

Reference Row With Variable As Part Of

Sep 1, 2007

Dim vCriteria3 As String
vCriteria3 = ActiveCell.Text
Sheets("CUSTOMER OVR REVIEW").Select
Rows("25:25").Select
Selection.Copy
Rows("26:" & vCriteria3).Select
ActiveSheet.Paste

it hits the

Rows("26:" & vCriteria3).Select

and crasses with a "incorrect type" mesage. am i storing this into memory incorrectly? how do i fix it? the function is intended to use a variable to paste over x amount of rows.

View 5 Replies View Related

VBA Workbook Reference Via Variable

Feb 18, 2008

Ive just finished writing a macro that copies data from different parts of one workbook into a new workbook. However after doing all that my boss now informs me that the first workbook will be renamed regularly.

Is there a way adding a reference to a workbook that is not dependant on the name? Possibly declare it as a variable at the beginning or something? The indexing is not possible either as they might have multiple Spreadsheets open at any one time.

View 4 Replies View Related

Evaluating List Of Similar Names And Assigning Common To Variable?

Sep 17, 2013

I am working on a customer report template that generates our customers reports and will send them out automatically.

This issue I have now is that the system that generates the raw data for these reports only lists the Customers name in a column with an entry for each line of data, the thing is though that the system has lots of variations of the customers name, even more so if that customers has different departments.

What I need to do is from this list of customer names, I need to automatically figure out what the "common" name is or main name so to speak, and then make a variable using the correct full name, which will be used later on in the code to import correct logos, and direct the reports to the correct people.

Here is a quick example of what data we get raw from the system:

Customer Name:
John Build
Johns Buildings
Johns Ltd Building
Johns Plumbing Department
Glass Doors Ltd A Department of Johns Buildings
Johns Building Corporation
Hole In One Golf Range

This is just an example, we have thousands of clients, so the length, number of words etc can change alot. Ideally I from a list similar to that I would get a full proper result of "Johns Buildings Ltd" for example, this would then be in a variable to be used in code from then on to reference doing certain things with the reports of Johns Buildings Ltd.

You'll notice there is one name "Hole In One Golf Range" that seems to have no relation at all, this is correct, ideally I would also like to build in some error checking into the code, so that rows like that that have nothing to do with the others would get deleted.

So how would you amazing VBA gurus go about working with data like this? I'd prefer a more general answer with explainations that just straight code, as I'm sure I will have to adapt the hell out of it for it to be useful in context.

View 9 Replies View Related

ADVERTISEMENT