iam trying to populate a listbox with only the workbooks hidden sheets. iam trying to adapt this code which i found in the forums
Private Sub UserForm_Initialize() Dim wsSheet As Worksheet Dim lngIndex As Long With ThisWorkbook Redim strarray(.Worksheets.Count - 1, 1) As String lngIndex = 0 For Each wsSheet In .Worksheets strarray(lngIndex, 0) = wsSheet. Name lngIndex = lngIndex + 1 Next End With With ListBox1 .List = strarray End With End Sub
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23 Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
I am using a spreadsheet as a score sheet for a competition. One of the columns is the student's GPA. After entering all the scores there are duplicate final scores. I need a way to have it look at the final score and then use the GPA so that it will not put a duplicate value in the final column.
al Column N is the Total Column, Column O has the Names that correspond to the Total Column. Currently I am taking this total and putting it into Column Q (High Scores) in high to low order. Column R should have the names that match the scores. But with duplicate scores, it is only putting the first name associated with the score. I would like to use the GPA as a final determining factor for the duplicate scores. The higher GPA would come before a lower GPA. I have tried to put an additional column to bring the GPA over to correspond with the High Scores Column, but could not get it to work.There are actually more names for the competition and the top 10 will be moved to a different sheet and further judged. I have attached a sample with the exact formulas that I am using.
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).
I have a workbook with a hidden sheet ("Template") and a visible sheet("New Job"). I need code so when cell F1 in "New Job" is populated:
1-the sheet is renamed to the value of F1, 2-a new tab is made (a carbon copy of the hidden sheet "Template") 3-the new tab is named "New Tab" and marked as unhidden.
Public Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Name = Range("F1").Value End Sub
Function WorksheetExists(SheetName As String, _ Optional WhichBook As Workbook) As Boolean Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0) End Function..................
I have a userform which clones the latest sheet and produces copy of it on the next sheet. The first sheet they will be cloning is the sheet called 'template', I however want this to be hidden since I do not want anybody to modify a sheet which basically serves purpose of a template. And once cloned, it is no longer needed.
On the click of the command button, the macro will create sheet1 taking the info from sheet named 'Template' in the same workbook . And now on the next click of the command button, Sheet 2 is created taking the info from sheet1 and Sheet 3 is created taking the info from Sheet 2 and so on. Here is what I currently have, so how can i modify it in order for my scnerio to work?
I have a button on a sheet that runs a macro to unhide another sheet. That works, but I want the sheet made visible to remain forward. Instead, the button unhides the sheet and the sheet the button is on comes forward again. I am unable to figure how to keep the sheet made visible forward. Here is the macro 'as recorded'.
I would like to hyperlink the names in sheet 1 to the same names in sheet two. Is there a way I could do this using a formula rather that do each one by one?
In a sheet I have a two hyperlinks with Sheet4 and Sheet5. when I am clicking on first hyperlink then the sheet4 is opening and when I am clicking on the other hyperlink then sheet5 is opening. But if I hide these two sheets it is not opening,
Is there any way so that hide sheets should be opened? will I have to write code for that?
way of protecting a very hidden sheet, I will still need to open the sheet myself everytime I use the program. The only person to open this very hidden sheet is myself.
Sub Wright() ' ' Wright Macro ' ' ActiveSheet.Shapes("Object 5").Select Selection.Verb Verb:=xlPrimary End Sub
What do I need to add to this so that the Macro will run when the sheet is hidden? The Macro runs an Embeded presentation, which I do not want to be visible in the workbook.
The macro starts on open, opens the workbook with the data to copy, copies and pastes the data into the original workbook. The problem is that "Hidden Sheet" is hidden, so the macro can't see it!
how do I get the macro to use this hidden sheet without keeping it 'un-hidden'?
I've written some VB code in Excel 2003 which hides and unhides worksheets dependant upon 'Yes' or 'No' in a named range. This works Ok in Excel 2003, however if I try and use it in Excel 2007 it shows error 'Compile Error in Hidden Sheet'. When I debug it the error 'Compile Error - Method or Data Member Not Found' appears. The code in question is as follows:
I have placed the code I am using below. It takes values from a hidden sheet (PACTAct) and places them on a visible sheet (Email). The code moves between the two.
It works at the minute by unhiding the PACTAct sheet then at the end re-hiding it. However I don't want the user to see this sheet whilst the vba is running. Is there a way I can simply refer to the worksheet without unhiding it?
However, I need to hide the sheet, but it doesn't work because you can't use .select on a hidden sheet I believe. Any way of doing this to a hidden sheet?
I have below macro for Sheet2 and i have created a button on Sheet1.now whenever i press this button the macro will run and save Sheet2 as PDF file.
The problem is when i hide Sheet2 the macro doesnt work and it gives an error " Invalid procedure call or argument.
how to make this macro run even when the sheets are hidden ?
Sub PDF_Table() 'Sheet2.PageSetup.PrintArea = "$a$1:$x$140" '***** '*** can remove the above line if sheet areas are already set and will not be altered With Sheet2.PageSetup .CenterHorizontally = True .CenterVertically = True .Orientation = xlPortrait .Zoom = 60
I have a workbook, wich copies content from an overview sheet to different other sheets. that works fine, but if I try to hide the content-placeholder sheets, I can't copy my content anymore.
here the part where I get the error;
'OldValue contains the name of the "copy to" sheet as a string
I have been using this to print multiple sheets. The only problem is if you want all sheets in the workbook you have to check every one. How would I add an option to 'print all'? But I still wouldn't want to print the hidden sheets.
Option Explicit
Sub SelectSheets() Dim i As Integer Dim TopPos As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False
' Check for protected workbook If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If...............
I have tried to use absolute references (perhaps I didn't do it right) but it didn't work. Below is the code that does work but I need to first make the sheet visible then activate it. Sub Row_Sort(tmpsheet As String) ' Sort the first 4 columns of the tmpsheet sheet
Dim tmprng As Range Dim OldActiveSheet As Worksheet
' Activate the desired workbook (in this case Linewkbk) Workbooks(LineWkbk.Name).Activate
' Save old active sheet Set OldActiveSheet = ActiveSheet.............
This works fine but what I also need to do is delete the blank rows in the holding sheet "Staff_Import" and copy back to another sheet "July_Sept" without removing the formatting in "July_Sept" sheet ie cell fill & borders.
