Im 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 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.
Each salesman has an enquiry log / hot prospect sheet that they are responsible for.
At present at the end of the month our receptionist has to consolidate this information manually onto a master sheet.
I would like to create an automatic master sheet that auto updates each time it is opened.
It has to read data from about 13 work books each with varting amounts of date. All works books are formatted identically (or they will be)
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
Can a macro be written that will automatically NAME a sheet with the contents of a particular cell?
View 3 Replies View RelatedI'm not including my spreadsheet because it has company information however, I will try to articulate my request as best I can. I currently have an Excel spreadsheet with about 20 worksheets, I would like to be able to click on the first worksheet, create a "Drop Down box" or "Data Validation" box or whatever I need to do to be able to view information in a range of cells on ANOTHER worksheet within the same workbook.
I've tried Data Validation, Drop Down Boxes, Define name ranges, all to NO avail. I'm missing a step somewhere, because I'm still not able to view the information on the first worksheet.
I am having trouble getting the selection of sheets to work. I have a workbook that has multiple sheets and one constant sheet (Summary). There is code to create new forms in this workbook and insert them after the Summary sheet. These forms all have a date input that is formatted as a date (mm/dd/yyyy), these dates get modified on the day the form is created, there may be any number of sheets created during this process. I have to print the summary sheet and only the newest forms created. I need a code to select sheets to print based on the date input of a user for each form. This is what I have so far:
VB:
Dim i As Variant
i = Range("B5").Value >= InputBox("What date to start PDF from? Format = mm/dd/yyyy")
Sheets(Array("i")).Select [code]....
If the dim can be taken out and just included in the line for the array that would be fine with me. The cell "B5" is where the date is located in each form. I want to input a date and the macro will select the sheets where the date is equal to and greater than the date entered. The Summary sheet will always be included in the print set. I have a dialog box for setting which printer to use - this file will be used at different offices and therefor the printers will be different and it will also allow to create a PDF if desired.
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.
If 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 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 Replies View RelatedI 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 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 RelatedOk I have an input box that takes a date as mm-dd-yy
View 14 Replies View RelatedIs there a way to name a sheet in vba? I am creating new sheets in vba, and renaming these tabs, but is there a way to actually rename the sheet (so that in the properties window it says (Name) "Demand" AND Name "Demand" Instead of (Name) Sheet1, Name "Demand"?
View 9 Replies View RelatedI have a macro that keeps on adding work sheet.I need the sheet to be named say " Ex 1" whenever a sheet is added. Is there a way to do that.
View 9 Replies View RelatedI'm attempting to create a database on Excel 2007 to create a record of all the documents I use at work. At the moment I have an index sheet where I enter the indivudual project numbers. Is it possible in VB to take a value from this index sheet, copy an existing (template within the workbook) sheet and rename the new tab with the project number from the index?
Example:
As there will be many projects within the database is it possible to code it such that:
New Sheet1 created from template within workbook -> Tab renamed according to value of 'Index!B4'
New Sheet2 created from template within workbook -> Tab renamed according to value of 'Index!B5'
New Sheet3 created from template within workbook -> Tab renamed according to value of 'Index!B6'
and so on......
Further, is it also possible to automatically create the new sheet as soon as a project number is entered into 'Index!B4', 'Index!B5', 'Index!B6'?
ie: Text entered in 'Index!B4' -> new sheet created and renamed automatically
I want something strange but i guess you can do just about anything with excel these days if you ask around.
I want names for the sheets that are contained on a specific excel file that i have to be automatically generated accordingly to the following:
sheetname = runxyy_s=zz%
Where x is the lowercase letter of the word formed on the name of the first sheet of the workbook
and yy and zz are values stored on specific cells on the sheet,lets say yy is A1 and zz is H4.
I have a userform with a textbox and an OK button and having clicked OK a particualar sheet is given the name in the textbox. If the character '/' is included in the textbox a standard Excel message appears explaining that this character cannot be used. On clicking 'END' on the message the userform automatically disappears and I have to close the file and reopen to end another name.
Is there any way that I could introducing my own basic messagebox which says 'The characters /, ? etc cannot be used' and on clicking OK the userform stays and you can try again.
I want to have certain sheets in my workbook named from the result of a formula on that sheet in A1. There will never be an instance where two sheets would be the same name. I have ZERO experience in VBA. I tried the code below in each of the worksheets modules where I wanted the sheet name to change but it doesn't work.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then Sh.Name = Target
End Sub
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.
Private Sub Worksheet_Activate()
If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then
Me.ClearPrevious.Visible = True
Else
Me.ClearPrevious.Visible = False
End If
Sheets("MASTER").Select
End Sub
I am trying to using multiple if staements regarding one cell. What I am trying to achieve is:
if b2=0, then null, however if b2=1, then arable, however if b2=2, then manged grassland, however if b2=3, then forestry/woodland, however if b2=4, then semi-natural vegetation, however if b2=5, then urban, however if b2=6, then water
I have these 2, but they don't work - what am i doing wrong?
=IF(B2=0,null, IF(B2=1, Arable, IF(B2=2, Managed_Grassland, IF(B2=3, Forestry/Woodland, IF(B2=4, Semi-natural_Vegetation, IF(B2=5, Urban, IF(B2=6, Water, NO_LAND_USE)))))))
=IF(B2=0,IF(B2=1,IF(B2=2,IF(B2=3, IF(B2=4,IF(B2=5, IF(B2=6, Water, null), Urban),Semi-natural_Vegetation), Forestry/Woodland), Maneged_grassland), Arable), null)
How do you set the code so that, if a condition is met, then several cells are filled with values?
So far, my code is
HTML If Cells(i, 2) < Cells(i, 4) Then
Cells(i, 5) = "A"
Cells(i, 6) = Cells(i, 2)
End If
i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name occurs on.
Vlookup returns only one value. How can I get multiple values?
I have a worksheet that has three columns, TYPE, VALUE, PERIOD.
Within TYPE column it will always be either A or B
Within VALUE column a number common to both A & B for that "period".
Within PERIOD column a month-year.
A shown here:
TypeValuePeriod
A3000Jan-09
A3000Jan-09
A3000Jan-09
A3000Jan-09
A3000Jan-09
B3000Jan-09
B3000Jan-09
B3000Jan-09
A3200Feb-09
A3200Feb-09
A3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09....................
I have a report that I paste into excel. The report contains 4 columns. Column A contains a numeric value. The numeric value relates to a geographical area and the same numeric value may show up more than once. The reason the numeric value may show up more than once is because in column B there is a list of dates showing when a percentage in column D become effective. I have attached an example of the report.
What I am looking for is a was where the user can enter a geographic number in cell J4 and a control date in cell J5 and have cell J7 display the percentage value. I need the lookup to look up the correct area and then use the control date to find the corresponding percentage. So if Area 1 (numeric value) shows up in Column A in rows 2 through 5, the control date will determine which row to pull the percentage from using the effective dates in column B.
I am attempting to design 2 combo boxs by which you can select from a number of equipment types and a criticality:
Equipment Types Criticality
Vac Pump 1A
Filter Dryer 2A
Valve 3A
I want to attempt to write a macro so that when the equipment type and criticality has been selected, a corresponding number of rows will disappear.
For example:
If Vac Pump and 1A are selected then all the rows from 70 downwards will be hidden, and if Filter Dryer and 2A are selected, then the rows from 6 to 38 and from 47 onwards will be hidden.