Heres My Code...
Code:
Sub HideDatedSheets()
Dim wb As Workbook
Dim ws As Worksheet
Dim dDate As Date
[Code]...
So what im trying to do is keep the Union* sheets shown as well as the Report* sheets shown. But i believe i have the code wrong because they are not showing up when the work book opens...
Trim(ws.Name) = "Union*" Or _
Trim(ws.Name) = "Report*" Or _
If any excel expert who know how to compare names that are similar to one other with a wild card,
for example
A
COLUMBUS STEEL CASTINGS CO. 2ND LIEN (AKA BP METALS)
B
BP METALS AKA COLUMBUS STEEL Total
A
LS POWER - BROADWAY GEN FUNDING LLC
B
BROADWAY GEN FUNDING, LLC Total
My live sheet is a lot more complex but this will help me well on my way.
Ill let the example say everything:
Sheet3
ABC1Condition 1Condition 2Condition 32red lorrybig £ 92,621.98 3yellow carbig£ 145,498.33 4yellow vanbig£ - 5pink carsmall£ - 6red carsmall£2,552,404.50 7pink lorrybig£ 862.37 8green carbig£ - 9green lorrybig£ 707,083.31 10red vansmall£ 30,010.73 11green carbig£ 2,276.13 12 13 14Total of all cars that are big: 15expected answer £ 147,774.46
Excel tables to the web >> Excel Jeanie HTML 4
Data in B2 looks like this: "[13.0]DEV;[13.0]AUDIT" or can be "[13.0]DEV"
I need to wild card B2.
Although, not working.....my formula looks like this:
=if(and(A2="audit",B2"*"&"[13.0]AUDIT"&"*"),"Closed Audit",A2)
Im trying to use a wild card to count the number of cars regarless of color (Red, Blue and Black) in Maryland which are USED.
A: Cars Red, Cars Blue, Cars Black.
B: State
C: Type: NEW, USED
So I tried
=SUMPRODUCT((A2:A3100="Car~*"),--(B2:B3100="Maryland"),--(C2:C3100="USED"))) and tried with countif
=Countif(A2:A3100="Car~*"),--(if(B2:B3100="Maryland"),--(if(C2:C3100="USED")))
Suppose the data is:
beaut* 1
abc* 2
and I use the command:
Vlookup("beautiful", A1:B2, 2, false)
I want this formula to return 1. I have done it before, the other way around when the wildcard is used in the formula but not in data.
I'm trying to use the COUNTIFS function & have run into trouble because the 2nd criteria is a date. Typically I would just set the range & then put "01/01/2014" if I was looking for all of the January 1, 2014 cells. However in this spreadsheet my source data contains the date with the time. (01/01/2014 11:27:39 AM). Becasue the time is included in the cell, the function returns no results when I set teh date.
I tried to use a wild card "01/01/2014*" which I thought would bypass the time but it still retuns no results. Is there a way to use a windcard with a date cell?
I have a workbook containing 2 sheets. In the first sheet I have a list of values, let's say that the list contains names:
Worksheet1
Column A
Peter
Paul
In another worksheet I have another column, also containing names:
Worksheet2
Column A
James Harrow
Paul Givens
I wish to perform a vlookup in worksheet2 but cannot use wildcards in the table array :
Vlookup ("*"&ColumnA&"*";Table1[Table or table and column];1;false). What I want should look like the following: ("*"&ColumnA&"*";"*"&Table1[Table or table and column]&"*";1;false)
In this problem, it is not appropriate to query back to front; i.e. vlookup worksheet1 instead of vlookup in worksheet 2.
I have seen an interesting post that uses macros (on this site, I think) but was not able to implement it.
The usage of the wild card character is not getting me the results I need for a formula I am using at work. When the wild card asterisk character is removed, I get the correct result but only for an exact match of the particular text. I need it to match the "Particular Text plus another text. The Text is CBKC. Then there is also CBKC 5400 and so on. I need to have the wild card expression character before and after the CBKC characters. The formula is below that I used.
=sum(--k2:k500),--(c2:c500=" Y"),--(D2:D500=" Permit"),--(s2:s500="*CBKC*))
Excel doesn't like the way I am expressing the wild card inside the double quotes......the column getting added if the matches are found is column K. The other columns have to meet the criteria in the formula.
I have a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.
View 4 Replies View RelatedI am trying to figure out a code where hides all sheets in "ThisWorkbook" veryhidden except the ones names "Template" and "Report" and "Product"
I tried with the code below but the debugger stops at the line highlighted in red. perhaps i am missing something in the code.
Code:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Range("rReport").Value = "" Then
Cancel = True
[Code] .........
I would like to format columns "L" and "M", if the value in column "O" contains the letters "EU" or "US". The values in column "O" will look like EU 3 & 2, or US 1 up. or just text with no numbers. I was thinking if I could write a formula that will say 'if the value in cell ?? contains "EU" format color A if the value contains "US" format color B. I know this will take two separate statements but I am not sure how to write it with a wild card to allow for the variations. Something like If value is EU and some wild card string then, format.
View 7 Replies View RelatedI am trying to get a code that will Hide ALL sheets (xlVeryHidden) except for "Meeting Minutes" and "Index"
I want to do it 2 ways.
1) User Click a button and it happens
2) Upon Exiting the Workbook it happens
I tried modifying a code that I had to Reveal sheets but I can get it to operate the other way:
Sub HideMINUTESandMASTER()
Dim Sh As Worksheet
If Sheets("Meeting Minutes").Visible = True Then
End If
If Sheets("Index").Visible = True Then
End If
For Each Sh In Worksheets
Sh.Visible = xlVeryHidden
Next Sh
Sheets("Meeting Minutes").Select
Range("C1").Select
End Sub
I am looking for a formula which will allow me to count unique values in a database, based on multiple criteria. Sample file attached (Formula required in cells J and K).
View 6 Replies View RelatedI have a workbook with about 20 worksheets in it.
6 are visible
3 are hidden
the remaining are very hidden
I'm creating a "welcome" page to the workbook with instructions on how to update data. The data between the 20 sheets consists of pivot tables, charts and summary data. The Visible sheets are data for management's review (all protected). The 3 hidden sheets are pivot tables that the user needs to pull data from and the very hidden sheets are not to be seen by anyone.
In my "welcome" page, I added the instructions of how to update data, but wanted an area where I could assign a checkbox or button to click on for the user to unhide my 3 hidden sheets (say Sheet1, Sheet2 and Sheet3). I do not want it to unhide my very hidden sheets. Then, when the information needed is retrieved from those sheets, I would like the user to use a checkbox or button to hide the 3 hidden sheets again. Is there a way to do this?
I tried creating custom view but couldn't do that because of the pivot tables (the option was disabled). I'm a beginner in VBA so don't even know how to begin.
I have a workbook w/ 120+ sheets, and the first one is a table of contents, while each following sheet is a record sheet for one of 10 individuals. That is, sheets 2-8 are for Alice, 9-12 for Bob, 13-29 for Charles, etc. Individual names are in column C, sheet names are hyperlinked text in column A on the table of contents sheet.
I want to know how to hide all the sheets which are not meant for the individual based on the autofilter of Column C. If I select Alice as the autofilter criteria for Column C, I want all the sheets except the table of contents and her sheets 2-8 to be hidden.
Im working across Multiple Sheets and trying to get the following working.
I want a an input by the user of "NO" in cell M11 on Sheet 1 of my workbook to hide rows 32 - 41 on Sheet 2 and not to hide them if the user keys in "YES"
I have a workbook where each sheet is password protected. I'm trying to find some code that would unprotect each sheet, and hide rows 1-12 on each sheet, and then re-protect all sheets.
View 4 Replies View RelatedI would like to hide all worksheets and chartsheets in a workbook. I use
For Each sh In Sheets
sh.Visible = xlSheetVisible
Next sh
but I think it breaks down for chart sheets. Any solutions?
I need to hide all sheets appart from one sheet.
View 4 Replies View RelatedI would like to have all my worksheets except one hidden when a user saves the workbook. I can do it with a macro but not all users use the macro to save the document and sheets are left visible.
View 3 Replies View RelatedI am looking to modify my combobox from a userform to Filter the list based on the first entry on what the user will add.
I have a code that will search for all entries in my "Control" sheet and passed this on to my combobox:
[Code] ......
I have, within a module, the following function code that the user find the entry by adding the first 3 letters of the search they need:
[Code] .....
I would like to know what to change in the function to filter the list, once the user adds a first letter and then press the dropdown from the combobox, to filter the list based on all entries from the first letter to only show entries with that letter.
If A is entered, then, show only the entries strating with the starting letter A, but at the same time if the user adds more letter to filter to the first then second letter:
If AB is inserted, then, show entries with AB only.
Where do I add this code also, is this another function or this could be inserted within my function?
continued from: [URL]
Option Explicit forces explicit declaration of all variables in the code. That is why i had to declare ws as worksheet using the Dim statement before using it in the code. It is recommended to have it - however for this code it wouldn't have mattered.
I'm not sure I exactly follow what you imply here. But yes, you can hide all the sheets at first and then "unhide" at the start of the macro. Let's say you have a "Start" sheet.. then:
[Code] .....
The workbook is used by other people too. I cannot just tell them to hide the sheets, it defeats the purpose. What I want is, when macro is disabled, they will only see the START sheet, otherwise, all sheets will be visible. To add to this, a new sheet is added everyday. Sheets name are calendar dates in mmdd format
I'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:
[Code] ......
Why the above code does not respect the Master User "Jane" should be able to see all sheets?
Original source for this code was found here:
HTML Code:Â [URL]....
I want to run 2 different macros:
Macro 1- hides Sheet1 and unhides Sheet2
Macro 2- Hides Sheet2 and unhides Sheet1
I used the macro recorder to attempt to make this work but am running into a problem if Macro1 is run two times consecutively. In this situation the macro displays a debugging error b/c Sheet1 is hidden. Is there a way to get around this...possibly using an if then statement?
I have these following codes. when i click button then it updates the records, like pulling data from sheets and putting it into one. thats working fine. only things is, when it update the records, then it shows whats going on behind, all updating sheets get visiable. is it possible can i just hide whats going on in the back? when i click the button msg box comes up that please wait its updating the record.
Dim Response As Integer
MsgStr = "ARE YOU SURE YOU WANT TO UPLOAD NOW?, PLEASE CONSIDER RE-CHECKING YOU INPUT!!"
TitleStr = "USER MESSAGE"
If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
Sheets("Employee Data Input").Select
[Code] ...........
Let's say I have Sheet1-Sheet6. I also have combobox1 (with item 1, 2, & 3 as the list items) & combobox 2 (with items 1 & 2 as the list items).
If combobox1 = 1 & combobox2 = 1 then hide Sheets 2,3,4,5,6
if combobox1 = 1 & combobox2 = 2 then hide Sheets 1,2,4,5,6
If combobox1 = 1 & combobox2 = 3 then hide sheets 1,2,3,4,6
If combobox1 = 2 & combobox2 = 1 then hide sheets 1,3,4,5,6
If combobox1 = 2 & combobox2 = 2 then hide sheets 1,2,3,5,6
If combobox1 = 2 & combobox2 = 3 then hide sheets 1,2,3,4,5
I would like to also make both of these combo boxes required fields and to default text to say 'Select One...'
What would be the macro if i need to hide multiple select sheets? I'm working on a test with 12 sheets. Even number sheets contains the fields that they need to answer and the Odd number sheets contains the formula for score computing including the answers so it needs to be like this:
Sheet 2 - The test
Sheet 3 - Must be hidden
Sheet 4 - The test
Sheet 5- Must be hidden
Sheet 6- The test
Sheet 7 - Must be hidden
I already have the code to unhide all sheets, just need the macro to hide specific sheets like the ones above.
Here is what I think I want to do (however there may be a better way to get to the end result).
Macro 1:
Unhide all worksheets however, first list only the worksheets that are visible.
Macro 2:
I want to be able to hide all worksheets except those in the above list.
My intent would be to have these in the personal file so I could run either process on any file I have open so I think I would need to insert a tab for the list when unhiding & remove the tab after hiding.
I have found many strings unhiding all & hiding all except a named sheet... but can't find anything on the above?
I have a workbook that I edit daily then pass along to another team. All of my modules are passwork protected and there are also several sheets in the workbook that I hide before email it to the other team. I hide the sheets by changing the sheet properties in the VBA editor to "2 - xlSheetveryhidden".
This isn't a very time consuming process but I am wondering if there is a way to automate it through VBA?
I tried the the fail safe of "see what happens when I record it" but all I got was an empty Module.