I have got this macro working OK but now I need to add a 2 letter prefix/suffix depending on what time it was created (am or pm). Detail: If the sheet is created between 0600hrs and 1800hrs then "DS" and likewise between 1800hrs and 0600hrs then "NS". Outcome: The sheet will then have a name like - "22 Mar DS"...code included below
View 4 Replieson sheet1 I have a button I need to do the following when clicked:
(1) name the next 30 sheets based on cell values in sheet1
(2) for those 30 sheets, hide some of them based on a y/n input in sheet 1
To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.
Can a macro be written that will automatically NAME a sheet with the contents of a particular cell?
View 3 Replies View RelatedIf I opened a new workbook so i had sheet1 sheet2 sheet3... Starting at sheet1, how could i move on to sheet2 using VBA without actually naming it....
Is there a selectsheet.next or something?
I want my charts to be located in a new sheet each. I also need their names to start with "GR-Chart[number here]". So they should be GR-Chart1, GR-Chart2, GR-Chart3,......
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= "GR-Chart" & [this part I got wrong]
Do I use i? or .Count?
I have a workbook with a sheet titled Variables. There are 6 additional sheets in the workbook and each sheets needs to be named with the cell contents of A2:A7 in the Variables sheet. I have attached the workbook and you can look at the names in the sheets tabs to see what I'm trying to do here.
The workbook is too large to attach but can be viewed here:
http://www.shuffleupanddeal.org/name_sheets.xls
or
http://www.shuffleupanddeal.org/name_sheets.zip
I have a huge db that has a column "C" with a list of names. I have been creating tabs and naming them according to that list. I wonder if there is anyway I can create and name the tabs automatically. I already searched for threads in the forum, but only found this this backwards "tabs to cells". By the way, the names in column "C" sometimes repeat.
View 2 Replies View RelatedIm sure there is a post somewhere on this forum as I have seen it before but I cant find it anywhere. All I want to do is be able to select Sheets Sun,Mon,Tue,Wed,Thu,Fri,Sat without naming each of the sheets. The post I seen would select all the sheets between the two sheets that were stated in the vba code. something like: Sheets( Array("Sun" To "Sat").select
View 5 Replies View RelatedIs there a macro available which will create a pre-defined name for a pivot sheet, which will contain the word "Pivot" followed by a underscore "_" and the name of the sheet on which the pivot is applied.
Example: I have data on a sheet called "Salary" and put a pivot on "Salary" on a new sheet, the new sheet should be auto named as "Pivot_Salary"
Am using Excel 2013
I have an Index Sheet where I would be typing the name of the Sheet and a Command button to execute the operation of Adding the Sheet with the name mentioned in the Column C..
View 14 Replies View RelatedUDC 01_001 template black.jpgBatch 01_385 template white.jpg
UDC 01_001 template blue.jpgBatch 01_385 template blue.jpg
UDC 01_001 template grey.jpgBatch 01_385 template grey.jpg
UDC 01_001 template pink.jpgBatch 01_385 template pink.jpg
UDC 01_001 template white.jpgBatch 01_385 template black.jpg
I have 100s of urls with prefix as the left column. I need to rename them to the right column format. UDC 01_001 will take number Batch 01_385, UDC 01_002 will use Batch 01_386 and so on.
I cant figure out how to do this in one quick macro instead of replace function.
I would like to know if it’s possible to make Excelhighlight a cell dependent on another cells Time. Here is the link to thespreadsheet I have created. [URL] WhatI would like is cells with the times above them (G5 through AJ5) to automaticallyhighlight dependent on the time in the cells (C6 and D6) I highlighted the cellyellow to show what I mean. T
[URl]
There are other dependent drop-down list posts on this forum, but none of them are talking about working with multiple worksheets. I have three worksheets, one called "Main", one called "Universities", one called "Schools".
One the "Main" worksheet, I have two Combo Boxes (the one from the "Form" toolbar). I would like to first pick a university from the combo box and then based on the selection, the second combo box would give me just the schools in that specific univeristy. My guess on how to link the two drop-down list is by the ID column. If I select "University of Washington" which has an ID of "WAS" it would narrow down the second list to ID with "WAS" in it (Which would be "WAS1", "WAS2, etc). The reason I am using Combo Box is because of the "Link Cell" property which give me an output of the number of the selection into a cell. I will use that to get the corresponding ID. I have attached an example to this post.
I wrote a macro that worked for a long time but now a running time error (9) popped out even though the code didn't change at all. If I run the same macro on the same computer but logged on as a different user it runs smoothly. Tried it in other computers and in some of them it will also run in only some of the users but not all.
I'm running Excel 2003 on windows XP, on a pentium 4 PC but found the same in othe computers running Vista.
When I click the debug button the highlighted line is
Workbooks("Detrend").Activate
close to the end of the following code.
I have been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.
Any idea about a dynamic sum function that will do this, in combination with VBA?
Situation:
I have two drop down lists 1) Country and 2) States/province
Country has list: [US, Canada]
If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list
If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list
Scenario:
First, I select "US" and choose the states to "Florida"
Next I change the country to "Canada" and forgot to choose province
Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec],
BUT the current value is still "Florida"
Probem:
Now I have "Canada" and "Florida" selected in the sheet
Question:
If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?
G1 = will be one of these 12 choices: 01 January or 02 February or 03 March...or 12 December
K2 = Date I performed the task
D2 = Name of Inspector
I usually manually input on G2 the name of the report.
Example:
G1 = 12 December
K2 = 12/05/2013
D2 = Chris Mart
Therefore, I need to manually input on D2 = 13-12-MARTCL
Where....
MARTCL comes from a table where I have all the inspectors and their corresponding username.
That table is O2:P63. O1= Title of row (Username), P1 = Title of row (Name of inspector)
13 = year extracted from 12/05/2013
12 = Month of inspection (which can be extracted from G1 or K2)
How do I use cell reference AND VLOOKUP to create 13-12-MARTCL on G2?
I have a colummn of data (text data). For my purposes I would need to add a prefix text to every single cell contained in that column; for instance, if I have a cell with a text "hellohowareyou" ,
I would need to change it by adding to it a constant prefixation text "es_ES@" so that the final result would be displayed like "es_ES@hellohowareyou" , and so on for the rest of the cells under the same column.
Say I have a workbook called test1.xls, where the number 1 at the end of the file name is variable. Tomorrow that character could be 2 so the file would become test2.xls etc (or could even be a letter for what it matters).
Is there a way in Visual Basic to recognize that the final character is not fixed but can get different values each time? Or in other words to recognize that the file starts with "test", and that whatever follows that prefix is not relevant?
For example....
If ActiveWorkbook.name = "'test*.xls" Then...
where * (star) could be any character.
As a Windows analogy, I am thinking of the * (star) character when one searches a certain directory for, say, Excel files (*.xls etc). Obviously, in VBE that doesn't work, and the star is just a character like any other.
I’m making a database to forklifts. Most used code I have found from this forum. I haven’t found a way how to copy existing formulas on summary sheet so that sheet name is also changed in formula. I attached also my workbook, but it’s not in English language but in Estonian. I hope that isn’t problem.
If pressed button on summary sheet (“Pealeht”) then existing blank sheet are copied and renamed. On userform1 is textbox3 which value added to summary sheet, also combobox4 value added to summary sheet. Now I want that if pressed commandButton1 on userform1 then all formulas what exist on summary sheet from F6 to CW6 are also copied to next row and in formula sheet name changed. My second question is what to write that if new sheet created then it goes always to end of existing sheets. Right now I’m using code what copies new sheet after blank sheet but it’s not satisfying me.
i have a cell "h23" that must always start with a "j" or a "J"
the trouble is some of my users are only putting in the number
ie 2345 when it should be j2345 or J2345
I'm not a novice but this might be something simple I didn't realize. I am creating a quoting sheet for a steel fab company. I have created dopdowns for the different materials and sizes but the dropdown is way too big. I need to have a column that helps me filter the dropdown more so I don't have to scroll through litterally hundreds of steels to find the one I need. The good news is that most steels have prefixes that make them easier to filter.
For example:
L-3x3x1/2
L-2x2-1/2x1/4
C6@8.2#
C8@11.5#
W12@16.0#
W21@44.0#
What I'm looking for is a way to have a dropdown that has all of the prefixes (L, C, MC, W, PL, etc) in it and when I select one of them the next column (the actual description of the steel as shown above) will only give me the specific steels for that steel shape (L,C,W,etc)
Currently I'm putting one row of say the MC shapes and one row of the C shapes and one row of the L shapes then copying them as needed. It's a lot of work and you can easily make a mistake.
I have a table like below
A B
1 x1234 value
2 y1234 value
If I want to look for all values that start with "x", is there a way to do it within a vlookup formula? Something like: vlookup("x*", A1:B2, 2, False). I know there's other ways to do this, but I want it all contained in one formula instead of splitting the A column using LEFT(A1, 1).
I read the thread below on how to utilize the Subsitute function to remove periods and thought about being able to use it for this. However, I have some phone #'s in my list that contain multiple 1- scenarios in them because the area code or 3-digit prefix sometimes include a 1- also. How do I make the formula only look at the 1- for long distance and not any other 1- found in the phone #? I want to remove all of the 1- for long distance because we are trying to use a new autodialer that is pre-programmed with the 1-.
View 8 Replies View RelatedVBA code to add prefix "HR" in cell H11. So no matter what I type in cell H11, for example 123456 when I hit enter I would like that in that same cell stands HR123456.
View 4 Replies View RelatedI have several excel workbooks with many worksheets (over 500 in some). Around a third of these worksheets are named "Exp1", "Exp2", "Exp3", etc.
I would like a macro that will delete all worksheets that do not have names starting with the letters "Exp". I do not want any confirmation dialogue, and as the workbooks vary in size, I would like it to finish when there are no sheets left (except the Exp ones of course).
I have often used Data Validation List to create selection lists for cells in a worksheet. The problem is the list has to be on the same sheet you want to use it on. Is there any other way to do this so one common list can be used for all sheets? I have a list of accounts I want to use on 12 different Monthly tabs.
View 5 Replies View Relatedfor a small online database I have a column that lists nationalities:
French
Spanish
American
Latin American
(etc etc)
I need to add a prefix to all nationalities, for example:
Artist Nationality///French
Artist Nationality///Spanish
Artist Nationality///American
Artist Nationality///Latin American
(etc etc)
Is there a way I can select the 700 cells and do this in one shot? I can't add formulas because I will have to paste all this in text pad and then upload it.
I want to add "CR" to the beginning of every cell that already has data. I know I could do =a1 and it would copy all the way down. However each cell has different data and I'd like to keep it that way but add 2 letters in the beginning.
View 3 Replies View Related