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
ADVERTISEMENT
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jun 10, 2013
I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.
I am not sure of two things:
1. how to find the already existing tab?
2. Once I find out how do I programatically delete it, so the code can continue
The code below works with the exception of the last section (trying to achieve the questions stated above).
I am using Excel 2010.
Code:
Private Sub CommandButton1_Click()
If TextBox1.Value = blank Then 'Need name for processing
MsgBox ("Name must not be blank.")
Exit Sub
End If
If Len(TextBox1.Value) > 12 Then
[code]....
View 5 Replies
View Related
Feb 5, 2014
How do I get sheets names automatically? I.E. I want to get in A2 sheet number 2 name. In A3, sheet number 3 name...
View 1 Replies
View Related
Nov 21, 2006
On sheet1 of my workbook I have a table containing the colums : Title, Name, Street+nr, postal code + city of some people, Telephone, email.
I would like to do the following (by clicking a button perhaps?)
- For all the records in my table on sheet1 I would like to insert a new sheet in my workbook named to their names (as in sheet1).
- The new sheet must be an existing sheet that I've already made the content (a kind of expence sheet)
- Somewhere on the top of the sheet I would like to have their names , titles en addresses (fetched from sheet1)
View 9 Replies
View Related
May 15, 2014
i have a names list in range P2:P100
like
P2 = A
P3 = B
P4 = C
P5 = D
P6 = E
P7 = F
then create the sheets with name A, B, C, D, E, F
View 1 Replies
View Related
Feb 2, 2009
I have written some code which asks the using for a name, selcet's a sheet, copy's the sheet, rename's the copied sheet then hide's the original sheet and makes the copied sheet with new name active.
My problem is that I need some sort of code to check for sheets with duplicate names and if true ask the user to rename the sheet or maybe delete the sheet.
View 4 Replies
View Related
Mar 1, 2012
I have a macro that creates sheets from a list of names. I have a template which is copied.
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Dim MySheetName As String
[Code]...
Sometimes I have to update that name list.
What I need is to have some addition that checks if the sheet does exist, if it does I want to skip and move on to the next name.
View 3 Replies
View Related
May 24, 2013
I have an excel worksheet with TableNames and ColumnNames. I would like to copy the different TableNames to it own sheet with the corresponding ColumnNames. I also would like the sheet to be renamed to the tablename. I have the workbook but I don't know how to attach it.
View 4 Replies
View Related
Jan 19, 2009
Is it possible for a macro to delete any sheet that within a workbook that does not match a specific list of sheets. For example a workbook will always have Sheet1, Sheet2, Sheet3, Sheet4... but it can have many other sheets added to it. The names of the sheets can be completely different however the sheets that I want to keep will always have the same name.
View 9 Replies
View Related
Mar 26, 2007
When copying sheets within a spreadsheet I receive numerous examples of the attached error message: when I search for the named range referred to in the source sheet it doesn't exist - I can only assume that it is a variable name that has been assigned somehow - but as far as I can tell, there aren't any macros associated with the spreadsheets in question that are generating the names that are displayed .. so unsure how the names are appearing. how to remove these phantom names so the prompt stops appearing?
NB: If I answer "No" to the prompt I must enter a different name - and if I do that, the system creates the new name in the copied sheet so if I then copy the copy on to another sheet the number of "named" ranges includes both the original "phantom names" AND the additional "phantom names" I've created as part of this process.
View 5 Replies
View Related
May 7, 2014
I have two sheets. Sheet A (Receiving Log) contains all orders with NAME (D1) and QTY ORDERED(E1). Sheet B(Component Reject Tag) contains all rejects with NAME(D1) and QTY REJECT(E1). Sheet A and B are Many to Many relationship
What I am trying to figure out is:
Match the names from Sheet B to Sheet A and sum up the QTY REJECT from Sheet B divide by the sum of QTY ORDERED for the NAME from Sheet A.
Business would like to get QTY Reject percentage by each NAME (Supplier)
In a final table lets say Sheet C I would like to see NAME, QTY Ordered, QTY Reject, Ratio in which will allow my to perform a Top 10 suppliers of highest reject percentage.
Another note to mention, Sheet C presumably to have all the formulas as in Sheet A and B will be overwritten and pasted with new data.
View 10 Replies
View Related
Feb 12, 2013
I'm trying to retrieve the worksheet names to a specif cell and populate from that point only with the names of the visible sheets, but It keeps returning only the last visible sheet name and not the sheets that are visible. Where the code is failing - I'm self learner and start with VBA macros a couple of months so I'm still learning how to identify when the error show up
Code:
Sub Summary()
Dim wks As Worksheet
ultl = Sheets("Control").Cells(1048576, 4).End(xlUp).Row
For i = 31 To ultl
[Code] ..........
View 6 Replies
View Related
Aug 25, 2007
Can a macro be provided to show the list of sheet names in a drop down list at cell B2?
View 9 Replies
View Related
Oct 19, 2006
Need a way to generate a list (i.e. array) of tabs available in the current spreadsheet?
View 3 Replies
View Related
Dec 19, 2006
I made one file with 13 sheets.
sheet1 tab name is : MAIN
and other sheet tab name like following
2. xyz-Sales
3. xyz-Rev
4. xyz-SSN
5. xyz-ddn
6. abc-Sales
7. abc-Rev
8. abc-ddn
9. abc-ssn
10. ddd-sales
11. ddd-Rev
12. ddd-ssn
13. ddd-ddn
In Main sheet There are 3 buttons
1 . XYZ
2. abc
3. ddd
when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide
if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide
i don't want to use
Sheet2.Visible = xlSheetVeryHidden
i want to use finde xyz sheet tab name and shows and other are hide.
View 9 Replies
View Related
