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

View 6 Replies


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







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