How to assign a "WS(i)" worksheet name and have the assigned variable (i) in the name equal the consecutive steps in a total page count.
Here's the code that does not work:
Code:
Dim WB as Workbook
Dim WS as Worksheet
Set WB = ThisWorkbook
For i = 1 To WB.Sheets.Count
Set WS(i) = WB.Sheets(i)
Next
I can see why it does not work. I can't figure out what to declare in my Dim or how to word the statement using the (i) variable in order to capture the value to attach to "WS."
Details: The issue is that the number of pages created will vary day to day depending on staff needs. There's an array of staff names to be assigned to page tabs and there will be skips and deletions along the way depending on daily attendance so "WS(i)" worksheet names can't be assigned at creation. What happens if they are is that the succession of worksheet names winds up being (e.g.) "WS1, WS2, WS5, WS12," etc. instead of consecutive.
There's a very complex data-sort-and-assign system that depends upon how many staff-assigned sheets there are, and as such each sheet has to have a "WS" worksheet name for the process to stay smooth. If the names are not consecutive, though, the sort-and-assign process becomes confused. This step takes place at the end of the initial workbook set up process, immediately before the data are addressed, so there really isn't an earlier opportunity. It can't be later since from this point on the system depends upon the "WS(i)" names being there.
It started fine when there were 5 people. Now there are 37.
I think I'm using the correct terminology with the term "variable", but to explain what I'm trying to do, I want to get RAND() to hold the first returned variable so I can compare it to other places RAND() has been used & if the returned value is the same as the other place, then run RAND() again.
I'm trying to say in the formula above that if rand = what was already in A4 then run random again -- but this doesn't keep if from returning the same value as A4 on the second pass.
What would work is something like (where X is the assigned variable):
I know that I cannot automatically assign UniqueCount the value of that formula. How would I format the line such that I can indeed assign UniqueCount the row value of that line?
I could really use some excel function. Within my office, we work with several hundred files. Each employee is assigned files based on the last two digits of the file number. What I need is a way to identify what file is assigned to which employee based on the term digits of the file.
So for example, I have the following list of files:
I'm able to do a formula to get the term digits (meaning the last two numbers), but i'd like to have another column that can put names based on the term digit column. For example, Tom might work 00-04, Sally works 05-09, Greg works 10-15, Lucy works 16-21.. etc
I came across the below IF formula that is exactly what i need, except it only works for two associates and not the multiple that i need.. but it looks to be a good starting point nonetheless.
------------------------------ =IF(C2<50,"Sheryl","Lisa"). You should enclose Sheryl and Lisa with quotation marks as these are string values.
You can also use (if A2 is where the Loan # is):
=IF(Right(A2,2)*1<50,"Sheryl","Lisa")
The formula will acquire the last 2 digits of the loan and check it if it's for Sheryl's or Lisa's. -----------------------------
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.
Basically I have a list that user can select up to 6 values from. I have set them as individual strings.
Code: Public IH1 As String Public IH2 As String Public IH3 As String Public IH4 As String Public IH5 As String Public IH6 As String
What I want to be able to do is loop through them and do certain actions. How can I get a for statement to do that.
I tried something like that but it doesnt work. How to get it working I need to do tests on the variable strings and I want to be able to reference them and I'm not sure sure how
Code: For i = 1 To 6 If "IH" & i = vbNullString Then Else MsgBox "IH" & i End If
I'm having trouble finding a way to switch between two workbooks that I have open whose names are stored as variables. If the variable name were variable, for example, I have tried the following:
I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the
Const lngLastPossRow As Long = 65536 Dim strDataRng As String strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _ strDataRng
While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).
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).
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
I use a web query to import into an excel workbook. As data is changed on the web that the query runs against, I would like the name of the sheet that is being imported into to change to one of the cells that is being imported.
With Wb.strMyBookINT Set S70Wscopy = Sheets("s70 pivot data") 'set s70 pivot data sheet for kpi 44 s70 pivot data Set IMFWscopy = Sheets("imf pivot data") 'set imf pivot datasheet for kpi 44 imf pivot data End With
With Wb.strMyBookEXT Set IMFEXWscopy = Sheets("imf ex") 'set imf ex sheet for kpi 15 imf ex End With
I am trying to create an array in VBA with a loop, where new variables are assigned each time the loop starts. These variables are then used in calculations and the final answer found by using Solver. I have managed to get this working by setting specific cell references, but I want to do it without interacting with the sheet and populate and array.
I am not sure how to use the variable, eg. x in the place of the SetCell:=Range("A1") part.
So to put it clearer, I want to substitute the Range references with variables x and y from my VBA variables, without interacting with the worksheet to get values.
I have a spreadsheet with a real-time data link in cell A1 and I use Worksheet Calculate() to paste the new value, on any update,below cell A1. Once I have 10 values from A2 down to A11, I over-write the first one, so that I have the 10 most recent. From this I calculate the moving average.
Anyway, I would like to be able to do this without the pasting bit ie storing the most recent ten data points in a variable say x(i) and just averaging x(1) to x(10).
I'm guessing I need to use some kind of static variable or array, but I don't really know how to do this.
Using Excel 2007, I'm trying to figure out (and not succeeding!) on how to declare worksheet and range - in order to:
input a formula to cell xfd1, then copy down to xfd2:xfd100 - and here's what I've been playing about with
Code: Sub filldownxfd() Dim src As Range, out As Range, wks As Worksheet Dim sRangeName As String Workbooks.Item(1).Sheets.Item ("Sheet1") Dim example As Range Set example = Range("xfd2:xfd100")
there is a named range on the worksheet in cell A1- "GiGS"
I want to type "GIGS" in 1st cell and then in 2nd one to write formula "=A1*5". So excel need to understand that in A1 there is a name and not just a string
What I want to do is have a ComboBox (I think it should be a ComboBox, I want the user to be able to select multiple options at once) and display the names of the worksheets in the ComboBox. I could not find a way to do this directly with rowsource and thought about writing a loop to check the current worksheets with their names and put the values into cells, to this way have the names displayed in the ComboBox via rowsource?
I have this logic that clears cells in all WS in WB.
The logic is using each sheet name to reference the logic to clear the contents. Look:
Private Sub CommandButton1_Click() ActiveSheet.Unprotect Password:="" With Application .ScreenUpdating = False .EnableEvents = False Response = MsgBox("This Action Will Prep For A New Week. Do you want to Continue?", vbYesNo) If Response = vbNo Then Exit Sub End If
But I also want to change each sheet tab name to reflect a cell that has the date. But how to do this with the sheet reference in the logic? Couldit be dynamic?
Looking for a way to use VBA to get a list of the tab names from a closed workbook and put them in a column of the worksheet that has the macro. Is there a good way to do that?
I currently have a workbook that copies the first sheet and emails it to an individual on the first day of the week.
New requirements are for it to be sent to a list of people.
I am at an impasse on how to proceed due to lack of knowledge. The current code obtains the recipient from Sheet3 D4 and i would like to be able to list down this column for additional emails without restricting this to a set number of cells.
I have searched the forum and have been unable to find what i am after.
I have included a test file to show what i have so far. We use Outlook 2k3 We use Excel 2k3
Basically I want to be able to get the names of all sheets in a workbook. I know how to get Excel to print the name of the sheet in a cell once the file is saved, and I know how to hide sheets to make sure that all sheets are referenced in a range. If the name of the worksheet is in the same cell across all sheets (except the ones I don't want included on the totals page), is there a way to call that range and have Excel print off all the worksheet names in different rows or columns via a fill function?
Difficulties: the user may add/delete, rename, and have any number of, worksheets in the workbook. Because these variables are unpredictable, I am having difficulty figuring out the way to do this. Also, the formula should be able to ignore the sheets which have no value in the expected cell.
Is it possible to have a list in a summary sheet, containing the names of all other worksheets in the workbook, which will automatically update when a worksheet is added or deleted? I use Excel 2003.
I was trying to re-create an example from Chapter 4 the 2nd Edition of Professional Excel Development in which there is a data validation list, that is conditionally populated according to the selection of another data validation list.
When you choose b/t Fruits or Vegetables (Source:=Categories) in column D, the list under Item (column E) will read as a group of Fruits or Vegetables (Source:=If(IsBlank(D4),"",Indirect(D4)).
When I look at the Define Name dialog box, there is a column indicating the sheet name, which I cannot re-create/build myself. My first question is how do I do this, and secondly, the 2nd formula above will not work when I plug it into the data validation, but it may be on account of these worksheet level names. If you guys can see another reason why, I'm all ears (or eyes as it were).
UPDATE: Of course, I just tried it again this morning and it worked, but i would still lke to know how to use Worksheet/book level names.
