I created a poll in my website, which have a field for the users write. The field says: "What is the name of your best friend?" And the users have to write the names.
So... I got a big big excel file, and now I want to know the most "friendly names" of the pool. You know, I have to create a automatic counter for the names. Like:
John = 4560
Ken = 1304
How can I write a For-Next Loop and have the counter skip values in between. For example:
For i = 8 to 17
When that is completed, the counter jumps to 21:
For i = 21 to 30
When that is completed, the counter jumps to 34:
For i = 34 to 43
I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.
View 9 Replies View RelatedCan I create data validation list of the names created in the name box or of the sheet tab names?
View 5 Replies View RelatedTwo 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).
Is there a command/formula that will automatically put the progressing date in the tabs of the sheets? Like if I am doing a spreadsheet containing 52 sheets for all the weeks of the year, is their a way I can label the first tab 1-1-2013, and then excel will auto label the remaining 51 tabs when I hold the control key, click/hold cursor and drag the sheet to make another? otherwise I am going to have to manually enter around 1000 dates for the tabs.
View 2 Replies View Relatedsupposing i have column A which is vehicle number
and column b is fuel quantity
how to make in c automatic sum by vehicle number as i may have many entried in column A
example
vehno1 20
vehno1 21
vehno2 25.5
vehno2 40
vehno3 50
I want to create a row for each of the 52 weeks in a year. I want the weeks to begin and end on thursdays. Is there a way to automate this so that I do not have to maunally enter in each week? I need to do this for 2008 and 2009.
For example
7/31/08 - 8/7/08
8/7/08 - 8/14/08
8/14/08 - 8/21/08
etc....
I have an Excel file with multiple worksheets. 4 of these worksheets have the same format and have concatenate formulas in cells D14 through D28. What I need is an automatic pop-up message to tell me when I open the file if on any of these 4 worksheets text is generated from the concatenate formula that matches either "air cooling unit", "air heating unit", or "ceiling".
And, if so, I need for the pop-up to state the name of the Worksheet/tab and which of the above text strings match. For example, if one tab is named "6-5-09 C" and within the range of D14:D28 the text "ceiling" is listed, the pop-up should read something like, "Maintenance Needed for ceiling on 6-5-09 C!" If there are multiples on multiple worksheets, then I'd want all of them listed on the same pop-up. Then I would want to be able to click OK or CLOSE to close the pop-up. If I can even color the pop-up a color different than the regular gray, that'd be cool too.
I am not too sure what this is called but I am using Excel 2010 and need particular document that I use repeatedly. Basically, if a particular cell in the document does not contain a phrase then I want Excel to automatically change the text in that cell to bold and red and 2 other cells in Excel to bold and red as well. For more information, the cell is 19–20 and K–O and the phrase in the cell is "No Inconsistency".
So if "No Inconsistency" is not found in that cell then it automatically changes the text in that cell to bold and red, and it also changes two other cells that are found in that document to bold and red as well. The coordinates of those two other cells are 19 with H–J, and the other one is 17 with H.
I have two files,
1. Form Templete & 2. Student Data Index. (Both files will be placed in same folder)
About Form Templete : This is a simple form, containing some fields. Fields which i require are Form No., Student's name & Standard.
About Student Data Index : This file is to maintain records.
Each time when I fill form for a new student i will copy paste the form templete file & rename it by form no. (Entered in the Form Templete file, Form No. field)
My question is : Is it possible that whenever i create a new file in the same folder with student's name the required fields in the Student data Index get filled automatically, apart from that the new file created get hyperlinked & when i click on form no. the form for that student gets open?
Is it possible to create a macro that automatically saves a backup of the excel document in another desired location?
I have this formula:
Sub backupbutton()
Dim fname
fname = "D:" & Format(Now, "dd mmm yy hh mm") & ".xlsm"
ThisWorkbook.SaveAs Filename:=fname
End Sub
But that just keeps on making multiple copies of the file rather than overwriting the backup in the D: location.
Also, that particular macro requires me to have to click on a button in order for it to work but I would prefer that it happens automatically when the original file is saved.
I want to create an automatic page break at each value change in Column A, and also a page footer that "restarts" at each change in the same column.
For example, I would like a "Page x of y" footer, but I don't want "Page 1 of 3,500", I want "Page 1 of 3", etc...every time that Column A value changes.
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
Ive created the following code which I want to use to create a name in excel but it doesnt work.
when i put =INDIRECT i get an error and with out the = it places a text string in with speech marks on it e.g. defined name reference in excel reads ="INDIRECT('Health and Safety'!$AP$3)"
How do I get rid of those speech marks? the cell reference will change each time the macro runs and so will the sheet reference.
myC = ActiveCell.Address
Dim strNAME As String
strNAME = "YAXIS" & "_" & "HS" & Chartnumber
strSHEET = ActiveSheet.Name
ActiveWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:=strNAME, _
RefersToR1C1:="INDIRECT('" & strSHEET & "'!" & myC & ")"
Have a list of aprox 50,000 names. in that list names may repeat themselves multiple times. Is there a way in excel to extrapolate from that list of 50k to display only a single instance of each name in a separate Colum?
View 2 Replies View RelatedI 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.
I have an excel ranking 8 participants in a tournament.
In the excel you can see the people participating and there cumulative score next to their name.
What i want to do now is create a table that reads:
Cell A Cell B
1st place "name of individual with minimum points"
2nd place "name of individual with min points, from the leftover people"
""
8th place
I have used vlookup function, but I can either get it only to display first place individual but not the rest, sometimes it wont work because values of peoples scores have to be in ascending order.
i got a workbook made by one of the members in which on clicking of a button (make sheet)---->tabs of all the data in column A is made in that sheet. but if i want to copy a specific data that is present on main sheet to all the sheets created on click of button.
View 4 Replies View RelatedI have 8 different workbooks which is 100% similar. Only values registred on each sheet is different. the format and rows/columns are the same.
The sheets is something that changes, when I want to improve layout etc.
I have one Template book where I do the changes and try to export this to the other books (call it upgrades). For macro moduls it works nice, just replace.
The problem is the sheets. When I have changed a sheet, replace it, it would not adopt values from the other sheets.
Ex. In sheet 2 I have named the cell 'B60' for U_KU in the new sheet 1 I expect at using =U_KU in cell I7 would adopt the values from B60 in sheet 2. But nope.
I can see that there is an link to the template workbook in edit ->linkage. Would this cause problem?
Every month I work on an audit that has data from 35 different distributors. I have code below that puts each distributors audit/sales information on a new tab and each new tab is given the distributors name. This also creates a new workbook for each distributor.
When the new workbooks are created, how can I name each workbook with the distributor name it's being created for? Is it possible to predefine a file path to where these new workbooks will be saved?
The distributor names I'm using are in column AF.
Sub FormatList()
'The code below creates and names a new tab for each members info
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")
'extracts a list of member or distributor names
ws1.Columns("R:R"). AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("T1"), Unique:=True
r = Cells(Rows.Count, "T").End(xlUp).Row
'set up Criteria Area
Range("U1").Value = Range("R1").Value
For Each c In Range("T2:T" & r)
'adds the member name to the criteria area above
ws1.Range("U2").Value = c.Value
...............
I have a worksheet where a number of the cells have been assigned names. I want to copy these cell names & create a list on another part of the worksheet. Does anyone have some code I could use to achieve this?
View 8 Replies View RelatedI'm trying to create a macro which will run through every salesperson in a list, and copy and paste their product and client data (from separate sheets) to a template sheet. The template sheet will then by copied to the Salesgroup workbook, and renamed to the salesperson's name. Then the macro should go back to the original workbook and do it all again for the next salesperson, putting them into the same workbook.
The problem I'm having is that the references to the Template worksheet seem to be being messed up by copying the template sheet to the new workbook, and it then ends up creating all the worksheets, but it stops taking the data from the data sheets, and ends up copying the previous worksheet and pasting that into the new copied template, which means that I get the template multiple times across the same sheet.
Sub SalesSheets()
Dim Salesbook As Workbook
Dim NewBook As Workbook
Dim SalesDataSheet As Worksheet
Dim ClientDataSheet As Worksheet
Dim SalespersonListSheet As Worksheet
Dim Template As Worksheet
Dim rRange As Range
Dim rCell As Range
Dim strText As String
Dim SalesGroup As String
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Set Salesbook = Application.Workbooks("2008 Salesperson TEST.xls") 'change filename here
Set Template = Salesbook.Worksheets("Salesperson Template")
Set SalesDataSheet = Salesbook.Worksheets("SP product YoY")
Set ClientDataSheet = Salesbook.Worksheets("Client YoY")
Set SalespersonListSheet = Salesbook.Worksheets("Salesperson List")..........................
I want to make several charts with dynamic ranges. To do that, I wrote the offset functions I need in cells. I've attached a sample spreadsheet. I want to have a macro so that when I run it, it will take the contents of the active cell, insert a name called those contents, and make it refer to the cell 4 to the left of it.
In the spreadsheet, I'd like to be able to click on E2 (sentdate1), create a name called sentdate1, and make its value A2, or =OFFSET('Sentiment'!$A$3,0,0,COUNT('Sentiment'!$A:$A),1). Here's what I have so far.
Sub Macro8()
ActiveCell.Offset(0, -4).Range("A1").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveWorkbook.Names.Add Name:="sentdate1", RefersToR1C1:= _
"=OFFSET(Sentiment!R3C1,0,0,COUNT(Sentiment!C1),1)"
End Sub
Auto Merged Post Until 24 Hrs Passes;Here's the sample worksheet.
I am getting an automation error
The project is the create checkboxes out of the tab names in a workbook. After that, create a new workbook with the selections made. It is at the point where I pass the array of sheets to be copied that the error occurs. The only thing I can figure is that my variable curWB is not what I think but I declare it to be the original workbook before that point. All of the code is below.
Another possibility is that this code with some other code sits in the worksheet called index. This sheet is being copied to the new workbook also.
VB:
Private Sub CommandButton2_Click()
Dim oleObj As OLEObject, ole As Object
Dim sheetSelection() As String
[Code]....
I have a folder with 20 Excel files. I'm trying to create a master list of all the tab names. I can see all of the files opening, but it only copies some of the names.
VB:
Sub GetTabNames()
Dim wkBook1, wkBook2 As Workbook
Dim stFilePath1 As String
Dim FileList(1 To 18) As String
Dim iLoopSheet, iLoopProg As Integer
Application.ScreenUpdating = False
[Code] .....
I have a list of names in column A, and want to automatically create worksheets within this same workbook based on the list.
List of values:
Dairy NI
Dairy SI
Sheep and Beef
Equine
Sports Turf
Water Industry
Horticulture Services
Production Horticulture
Other Agriculture
Therefore, a macro or coding to automatically create 9 spreadsheets named as per this list.... My list is approximately 50+ hence inquiring if there is an easier way!
I am at it again and maybe I am asking to do something that is not possible. I am trying to build a program that will allow the user some flexibilty down the road if I am no longer here to support my code. I have a workbook that contains several sheets. The sheets are labled by equipment name. The Sheets themselves contain specific data with regards to the equipment. What I want to do is as follows. Populate a user form with command buttons based off of the worksheet names. The reason for this would be if 2 years from now we add a new piece of equipment all the user will have to do is add a sheet in the workbook and the user form would reflect the new sheet with a new button. Like wise if I delete a sheet in the workbook the user form would refelect that change as well.
View 14 Replies View RelatedIs there a way to automatically create list of the Tab names, and have it automatically update when new tabs are added?
View 2 Replies View RelatedI am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.
View 9 Replies View Related
