Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Finding A Worksheet Starting With A Letter In Vba

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I was wondering if anyone could help me out with a search/match question. I want to search through the sheets in a workbook and be able to see if one of them starts with a certain letter (such as M01, etc) have the code select that one as the active sheet. For example, if the sheets are T01, T02, T03, M01, M02, M03, I want to be able to check the sheets until I reach the one starting with "M" and select that.

i was trying to approach this by using:


Sub pickSheet()
Dim name As String
name = "M"
If SheetExists(name) Then
Worksheets(name).Activate
Else
' do nothing??
End If
End Sub


However, this chunk of code doesn't work. It doesn't realize when a sheet is named M01 that it starts with an M. Does anyone have any suggestions of things to try?

View Answers     

Similar Excel Tutorials

Capitalize First Letter of Every Word in a Cell - PROPER Function
In Excel you can use a function to capitalize the first letter of every word in a cell.  This allows you to prepare ...
Select Cells in Excel using Macros and VBA
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
Make Column Headings Numbers instead of Letters - Make R1C1 Style References in Excel 2007
In Microsoft Excel you reference columns as letters by default - A1, B3, C5, etc. But you can also reference the co ...
MODE() - Find Most Repeated Value in a List in Excel
The MODE() function in Excel allows you to quickly find the most repeated or frequently occuring value within a lis ...

Helpful Excel Macros

Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac

Similar Topics







I have a workbook with 105 sheets in it and I need a way of searching for a particular string of numbers through all sheets and have the sheet names where the string is found returned in a list on a new sheet. I have this code from another post that gets me started:

Private Function SheetExists(SheetName As String) As Boolean
' Returns TRUE if a sheet exists in the active workbook
Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(SheetName)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function
Sub FindAllSheets()
Dim Found As Range, ws As Worksheet, LookFor As Variant
LookFor = InputBox("Enter value to find")

If LookFor = "" Then Exit Sub

' Clear or Add a Results sheet
If SheetExists("Search Results") Then
Sheets("Search Results").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Else
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Search Results"
End If

For Each ws In ActiveWorkbook.Worksheets
If ws.Name "Search Results" Then
Set Found = ws.Cells.Find(What:=LookFor)
If Found Is Nothing Then
Range("D5").Select
Else
Found.EntireRow.Copy Sheets("Search results").Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
End If
Next ws
End Sub


i have the following code which searches through all the worksheets in a workbook for certain words that I enter in a search box. It works well but it will only post results for 3 instances only as if there is a limit set in the code. Why will it not list more than 3 results?

Private Function SheetExists(SheetName As String) As Boolean
Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(SheetName)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function
Sub FindAllSheets()
Dim Found As Range, ws As Worksheet, LookFor As Variant
LookFor = InputBox("Enter search criteria:")

If LookFor = "" Then Exit Sub

If SheetExists("Search Results") Then
Sheets("Search Results").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Else
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Search Results"
End If

For Each ws In ActiveWorkbook.Worksheets
If ws.Name "Search Results" Then
Set Found = ws.Cells.Find(What:=LookFor)
If Found Is Nothing Then
Range("D5").Select
Else
Found.EntireRow.Copy Sheets("Search results").Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
End If
Next ws
End Sub


I have a little macro which sorts different parts of a worksheet. Each area to be sorted is identified by a Named Range, and the column to be sorted on is also Named. The macro processes all the sheets in the workbook (20-odd) doing a sort on anything from two or three up to about 25 different areas in each sheet, and it does this just by looping through all the Named Ranges, which have been generated by a previous process.

Each Name for an area to be sorted starts with the letter "I" and the Name of the sort key for that area is the same Name minus the initial "I" and suffixed with "Scr". Each "I" range has one associated "Scr" range. (There are other ranges starting with "T" which get processed differently.)

Here's my code, which does the job:

Code:

Dim nName As Name, stKeyRange As String, stSht As String
Application.ScreenUpdating = False
For Each nName In Names
    If Mid(nName.Name, 1, 1) = "I" Then
      stSht = nName.RefersToRange.Parent.Name
      Sheets(stSht).Select
      stKeyRange = Range(Mid(nName.Name, 2) & "Scr").Address
      Range(nName.Name).Sort key1:=Range(stKeyRange), Order1:=xlDescending '
    End If
Next


This fails if the line "Sheets(stSht).Select" is not present. i.e. the Named Ranges are not accessible unless the sheet they belong to is the active sheet. Why not? I thought Named Ranges were global. Isn't that why their Scope is described as "Workbook"?

I tried prepending the sheet name to the Range name in the Sort to give "sheetname!rangename", and even extracted the cell address from the range to give "sheetname!A5:H11", but these didn't work either.

I would appreciate any suggestions about how to structure this so I don't have to do a Select on each sheet.


I have tried many ways to use code to check if a sheet exists and, if it doesn't to create that sheet. Lots of searching of this forum and Google keeps leading to me code similar to the following which looks logical to me:
Code:

 
Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False    
On Error GoTo NoSuchSheet    
If Len(Sheets(SheetName).Name) > 0 Then        
SheetExists = True        
Exit Function    
End If
 
NoSuchSheet:
End Function
 
Example:
 
If Not SheetExists("MySheetName") Then    
MsgBox "MySheetName doesn't exist!"
Else    
Sheets("MySheetName").Activate
End If


However I get error code Run-time error '9' Subscript out of range whenever I run this?


Hi,

I'm using the code below to find all references in an Excel workbook and paste them onto a results sheet (Search Results), then I use the contents of cell B2 to search an external folder for matching documents. I have just discovered that all the results from the workbook are in a 01069701-0077 format, but some of the documents are in a 01069701.0077 format (dash replaced by dot) and consequently don't get found. Is there any way to get around this, I've tried just searching for the first or last part of the number, but it returns too many results.

Private Function SheetExists(SheetName As String) As Boolean
' Returns TRUE if a sheet exists in the active workbook
Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(SheetName)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function
Sub FindAllSheets()
Dim Found As Range, WS As Worksheet, LookFor As Variant
LookFor = InputBox("Enter value to find")

If LookFor = "" Then Exit Sub

' Clear or Add a Results sheet
If SheetExists("Search Results") Then
Sheets("Search Results").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Else
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Search Results"
End If

For Each WS In ActiveWorkbook.Worksheets
If WS.Name "Search Results" Then
Set Found = WS.Cells.Find(what:=LookFor)
If Found Is Nothing Then
Range("D5").Select
Else
Found.EntireRow.Copy Sheets("Search results").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Found.EntireRow.Interior.Color = vbYellow
End If
End If
Next WS
Sheets("Search Results").Activate
Columns("A:G").Select
Selection.Columns.AutoFit
Range("A2").Select
Call FIND_DOCUMENTS

End Sub

=============================================================================
'- SEARCH ALL FILES IN A FOLDER & FIND STRING IN FILE NAME
'- PUT NAMES INTO ACTIVE SHEET AT BOTTOM OF COLUMN A
'- (search is not case sensitive)
'& #39;============================================================================
=
Dim FoundRow as integer=1
Sub FIND_DOCUMENTS()
Dim FindText As String
Dim MyFolder As String
Dim MyFileCount As Integer
Dim MyFileName As String
Dim MyFileType As String
Dim f
Dim WS As Worksheet
'-------------------------------------------------------------------------
'- SET VARIABLES
Set WS = ActiveSheet
MyFolder = "H:\SERVICE CENTRE DETAILS\INSPECTION DRAWINGS and DOCUMENTS"
FindText = WS.Range("B2").Value
MyFileType = "*" & FindText & "*.*" ' = "*Test*.*"
'-------------------------------------------------------------------------
'- CHECK FILE NAMES
With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.Filename = MyFileType
.SearchSubFolders = True ' True to search subfolders
'---------------------------------------------------------------------
'- RESULTS
MyFileCount = 0
If .Execute() > 0 Then
MyFileCount = .FoundFiles.Count
For f = 1 To MyFileCount
FoundRow = FoundRow + 1
MyFileName = .FoundFiles(f)
WS.Range("H" & CStr(FoundRow)).Value = WS.Range("H" & CStr(FoundRow)).Value & " " & MyFileName
WS.Hyperlinks.Add anchor:=WS.Range("H" & CStr(FoundRow)), Address:=MyFileName, TextToDisplay:="Document"
Next
Else
MsgBox ("Search for file names containing : " & FindText & vbCr _
& "No matches found")
Exit Sub
End If
End With
'--------------------------------------------------------------------------
'- finish
MsgBox ("Found " & MyFileCount & " file names.")
End Sub

Any help would be greatly appreciated

Regards

Paul


Hello,

in Excel 2003 I want to adjust the code of my userform and would need a little help on two issues.

The following code attached to a userform displays the sheetnames of all sheets in the active workbook.

Code:

Option Explicit
Private bolInitial As Boolean

Private Sub CB_Abbrechen_Click()
  Unload Me
End Sub

Private Sub CB_Anzeigen_Click()
    Sheets(Me.ListBox1.Value).Activate
    Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  If bolInitial = False Then
    Sheets(Me.ListBox1.Value).Activate
    Unload Me
  End If
End Sub

Private Sub UserForm_Initialize()
  Dim objSheet As Object
  bolInitial = True
  For Each objSheet In ActiveWorkbook.Sheets
    Me.ListBox1.AddItem objSheet.Name
  Next
  Me.ListBox1.Value = ActiveSheet.Name
  bolInitial = False
End Sub


1) Instead of the ListBox1_DblClick Event I want to activate a sheet via the Enter button. Why does ListBox1_Enter not work? What am I overseeing?

2) I can navigate through the sheetnames in the box using the letter keys. That is, pressing P jumps to the first sheet in the list starting with P. When pressing the P key again, I want it to jump to the next sheet starting with P. Currently, it stays at the first sheet starting with the letter no matter how often I press the key.

Could anybody help me resolve these issues?

regards, Haui


I got the following code from another post, but it Combines ALL the worksheets in my workbook. Is there a way to...

1. Already have a tab called Combined instead of it creating it.
2. Delete all data from A3...down...
3. Copy the data from all the worksheets that contain the same headers (excluding TEMPLATE worksheet) as the Combine header and paste it in the Combine worksheet.

Yes, I can create a macro, but new tabs are always being created and there are other tabs with other information (News, etc...). Here's the code I WAS using, but I need it to do the above. My headers are in A2 across with data starting in A3.

Thanks!


Sub Combine()
Dim J As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' copy headings
Sheets(2).Activate
Range("A2").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A2")

' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A3").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub


Is this the right way to check? in case if it errors due to no Sheet existance?

Thanks for helping!!!

Code:

 Private Function SheetExists(sName) As Boolean 
 ' Returns TRUE if sheet exists in the active workbook 
 Dim x As Object 
 On Error Resume Next 
 Set x = ActiveWorkbook.Sheets(sName) 
 If Err = 0 Then SheetExists = True _ 
 Else SheetExists = False 
 End Function 
 Sub Demo() 
 Dim sName$ 
 With ActiveWorkbook 
 sName = "Sheet1" 
 If SheetExists(sName) Then 
 MsgBox "yes" 
 Sheets(sName).Activate 
 Range("A1").Value = "'--------------" 
 Else 
 MsgBox "NO" & sName 
 End If 
 sName = "Sheet2" 
 If SheetExists(sName) Then 
 MsgBox "yes" 
 Sheets(sName).Activate 
 Range("A1").Value = "'--------------" 
 Else 
 MsgBox "NO" & sName 
 End If 
 sName = "Sheet3" 
 If SheetExists(sName) Then 
 MsgBox "yes" 
 Sheets(sName).Activate 
 Range("A1").Value = "'--------------" 
 Else 
 MsgBox "NO" & sName 
 End If 
 End With 





I have 3 different excel sheets and data in each sheet starts from b5:q5. In a1 to b4 there is some other data. All i want is to copy the data from b5:q5 from all the three sheets and consolidate into one sheet. I have used the following macro but i am not getting the output properly. Can anyone please help me on this.

Thanks
sansri

Code:

Private Sub CommandButton1_Click()
    
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("b5:q5").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("a1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("b5").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
    
End Sub


Hi,


I am working on a macro to make excel a lookup tool. I have a master sheet built and will have 20+ sheets in the workbook. I want the master sheet to be the look up/data entry point. Once data is input and the search button is hit it will search the pages and find the match. If multiple matches are found, a pop up will appear to select which option sheet to go to. I have a very basic code started below. But it will get massive if I have to write this script out for each sheet. So below are my issues, and then my code. (I am using Excel 2007)

1. Can I simplify the code to search through each sheet without typing it out long as it is now?
2. Can it be made if multiple search criteria is input, to search for the first, if nothing found, then search the next data? (If searching and only finding sheets that match both could be done that would be awesome)
3. Can a pop up be done to give the options for multiple results, then have it capable of being selected to go to that sheet?
4. Is there a way to make the search not be case specific? (my current code wouldn't find the word job because I capitalized the J in one place and not the other)


Thank you anyone who can help me!


My code:
VB:

Sub Search() 
    If Range("g6") <> "" Then 
        If Sheets("sheet2").Range("b1") = Range("g6") Then 
            Sheets("sheet2").Select 
        ElseIf Sheets("sheet3").Range("b1") = Range("g6") Then 
            Sheets("sheet3").Select 
        ElseIf Range("g6") <> "" Then 
            MsgBox "Your Search Returned No Jobs, Please Try Again..." 
        End If 
    ElseIf Range("g8") <> "" Then 
        If Sheets("sheet2").Range("b5") = Range("g8") Then 
            Sheets("sheet2").Select 
        ElseIf Sheets("sheet3").Range("b5") = Range("g8") Then 
            Sheets("sheet3").Select 
        ElseIf Range("g8") <> "" Then 
            MsgBox "Your Search Returned No Jobs, Please Try Again..." 
        End If 
    ElseIf Range("g10") <> "" Then 
        If Sheets("sheet2").Range("b10") = Range("g10") Then 
            Sheets("sheet2").Select 
        ElseIf Sheets("sheet3").Range("b10") = Range("g10") Then 
            Sheets("sheet3").Select 
        ElseIf Range("g10") <> "" Then 
            MsgBox "Your Search Returned No Jobs, Please Try Again..." 
        End If 
    ElseIf Range("g12") <> "" Then 
        If Sheets("sheet2").Range("e3") = Range("g12") Then 
            Sheets("sheet2").Select 
        ElseIf Sheets("sheet3").Range("e3") = Range("g12") Then 
            Sheets("sheet3").Select 
        ElseIf Range("g12") <> "" Then 
            MsgBox "Your Search Returned No Jobs, Please Try Again..." 
        End If 
    ElseIf Range("g12") <> "" Then 
        MsgBox "Your Search Returned No Jobs, Please Try Again..." 
    End If 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




If I am selcting cells on a non-active worksheet, I now do something like this (below) as the qwuickest way to select the cell:

Code:

Sheets("Sheet2").Activate
[A1].Select


Without asking why I am "Selecting" vs. Activating the cells (there are several reasons for this particular thing I am doing), I'm just wondering is there a single line of code (versus my 2 line method of code written above) which will SELECT (not Activate) the given cell on a NON ACTIVE worksheet? In other words, this will NOT work if Sheet2 is NOT the active sheet:


Code:

'This will NOT work if Sheet2 is NOT the active sheet
Sheets("Sheet2").[A1].Select


I'm hoping there is something like this syntax (above) that will work to activate and select a cell on a non-active sheet???


Thank You,
Matt[/code]


What is the syntax to check to cee if a sheet exists in the current workbook.
If it Exists then delete Else Create.

Thanks Charlie Crimmel

Sub InsertBIDTab()
Dim shtName As String
Dim sheetExists As Boolean
shtName = "BID TEST"
sheetExists = ActiveWorkbook.Sheets(shtName).Name ""
If sheetExists = "True" Then
Sheets("BID TEST").Select
ActiveWindow.SelectedSheets.Delete

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Name = "BID TEST"
Range("A1").Select
Else
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Name = "BID TEST"
Range("A1").Select
End If
End Sub


This site has got me hooked on using VB ... Thank you!! Usually I can find my answers in the archieves, but today I am having trouble. Hopefully someone can help with this problem.
I have a workbook that is used by several people. To maintain its size I use command buttons to save completed sheets and save them to a folder as individual files. These Sheets are orders we run throughout the day, each are renamed automatically by a cell value. I use a command button to add more sheets as more orders come in. Sheets are created by a hidden sheet and named accordingly.
I simply want to move these sheets to the end via VB ... Please Help
(As you view the code you can see that all the new sheets are selected together. All I have to do is send them to the end, but inevitably someone will mess the whole thing up!!!)

Sub ADD4()

On Error GoTo AddSheetsFinalize
Sheets("Sheet (A)").Visible = True
Sheets("Sheet (A)").Select
Sheets("Sheet (A)").Copy Befo =Sheets(1)
Sheets("Sheet (A) (2)").Select
Sheets("Sheet (A) (2)").Name = "Sheet (1)"
Sheets("Sheet (A)").Select
Sheets("Sheet (A)").Copy Befo =Sheets(2)
Sheets("Sheet (A) (2)").Select
Sheets("Sheet (A) (2)").Name = "Sheet (2)"
Sheets("Sheet (A)").Select
Sheets("Sheet (A)").Copy Befo =Sheets(3)
Sheets("Sheet (A) (2)").Select
Sheets("Sheet (A) (2)").Name = "Sheet (3)"
Sheets("Sheet (A)").Select
Sheets("Sheet (A)").Copy Befo =Sheets(4)
Sheets("Sheet (A) (2)").Select
Sheets("Sheet (A) (2)").Name = "Sheet (4)"

AddSheetsFinalize:
Sheets("Sheet (A)").Visible = False

Sheets(Array("Sheet (1)", "Sheet (2)", "Sheet (3)", "Sheet (4)")).Select
Sheets("Sheet (1)").Activate

Somewhere here I need the code to send these 4 sheets to the end of the workbook!

ActiveWorkbook.Save

End Sub

CalB


Hi,

I am using the code below to combine several sheets into one.
Can you please tell me how to modify the code in order to combine specifc sheets in the workbook instead of all of them when I run the code below?
Eg. if the Workbook has got Sheet(1), Sheet(2) and Sheet(3) I would like to combine only Sheet(1) and Sheet(2)

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub


Hi all,

I have a workbook and I want to check in a cell, if the value in the cell starts with "S" or "D".
In VB.net there is a method .StartsWith to check the starting letter in a file.
Is there a way to check the starting letter in the cell?

Thanks in Advance.


For example, I have
Sheet1 (Blue)
Sheet2 (Green)
Sheet3 (Red)
Sheet4 (Yellow)

I want the user to be able to rename and reorder tabs. if the User Changes the name Blue to LightBlue, I want the macro to work, so I typically use the sheet1 instead of Blue. They can change it and my code works fine.Even if I re-order the sheets, so Sheet4 is first and sheet1 is 2nd, it doesn't matter.

In VBA I want to select 2 sheets (always Sheet 1 and 2) regarldess of the order. When I use an Array formula, it either lets me put the sheet name (i.e. "Blue","Green") or it lets me put the order of the sheets (in this case if it went sheet 4,3,2,1, then sheet2 and sheet 1 would be ((4),(5)- the 4th sheet and 5th sheet.

Here is my code - I select sheets Blue & Green (Sheets 1 and 2) which are the 4th and 5th ordered sheets, then name them with a named range I defined in my worksheet and then copy the sheets and put them at the end (I then have another macro that re-names them) what I want is to use the exact formula below but use Sheet1 and Sheet2 instead of the 4th or 5th sheet.

Dim ThisSheetName as String

Sheets(Array((4), (5))).Select
Worksheets(Worksheets.Count).Activate
hisSheetName = ActiveSheet.Name
Sheets(Array((4), (5))).Copy After:=Sheets(ThisSheetName)


I wrote this macro that will work on two named sheets but i want to copy the column from the active sheet:
Code:

Sub CreateExportSheet()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    
    If SheetExists("Export Sheet") = True Then
        MsgBox "The sheet Export Sheet already exist"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    Set DestSh = Worksheets.Add
    DestSh.Name = "Export Sheet"
    Sheets("MIC MASTER REF").Columns("A:A").Copy Destination:=Sheets("Export Sheet").Columns("A:A")     <-------Important Line HERE
    Application.ScreenUpdating = True
End Sub

Function SheetExists(SName As String, _
                     Optional ByVal WB As Workbook) As Boolean
    On Error Resume Next
    If WB Is Nothing Then Set WB = ThisWorkbook
    SheetExists = CBool(Len(Sheets(SName).Name))
End Function


Instead of "MIC MASTER REF" I want to to be whatever sheet is active.
I have tried:
Code:

ActiveSheet.Columns("A:A").Copy Destination:=Sheets("Export Sheet").Columns("A:A")


But it will just made a new sheet that is empty, how would I go about doing this?

Thank You


Hi guys, I'm hoping that someone will be able to help me out with this one. I don't think it's a real difficult one, but I guess we'll find out soon.

I have a spreadsheet with columns set up like this: RowName, Date, Value1, Value2, Value3 etc. The date is in format YYYYMMDD.

What I am trying to do follows, in some sort of pseduo fashion.

Code:

FOR EACH Date
   IF WorksheetExists (Current Date Value) = False Then
      Create New Worksheet. Name = CurrentDateValue
      Copy Entire Row from OriginalSheet to NewSheet
   ElseIf WorksheetExists (Current Date Value) = True Then
      NewSheet = Worksheet. Name = CurrentDateValue
      Copy Entire Row from Original Sheet to NewSheet
   EndIf
Next Date


hopefully you have a solid understanding of what Im trying to do now.
The following is where Im up to.

Code:

Function SheetExists(sheetname As String)
   Dim sheet As Worksheet
      For Each sheet In Sheets
         If sheet.Name = sheetname Then
            SheetExists = True
            Exit Function
         Else
            SheetExists = False
         End If
       Next
   End Function


Sub CreateWorkbooks()
   Dim newSheet As Worksheet, origSheet As Worksheet
   Dim cell As Object
   Dim origRange As String
Dim blahblah As String

   Set origSheet = Sheets("Main")

   ' Turn off screen updating to increase performance.
   Application.ScreenUpdating = False

   ' Build a string that specifies the cells in column B that
   ' contain text starting from cell B4.
   origRange = "B3:" & origSheet.Range("B3").End(xlDown).Address

   For Each cell In origSheet.Range(origRange)    
      If SheetExists(cell.Value) = False Then
         ' Add a new worksheet.
         Sheets.Add After:=Sheets(Sheets.Count)
         ' Set newSheet variable to the new worksheet.
         Set newSheet = ActiveSheet
         ' Copy the entire row for the current date and
         ' paste starting at cell A1 in the new sheet.
         cell.EntireRow.Copy newSheet.Range("A1")
         ' Name the new sheet.
         newSheet.Name = cell.Value
      
      ElseIf SheetExists(cell.Value) = True Then
        ' Make the existing worksheet active        
        ' WorkSheets(cell.value).Activate
        Sheets(cell.Value).Select  <- Run-time error '9' 
        ' Copy EntireRow to the new worksheet
        ' Finding the first available empty row in new worksheet
        cell.EntireRow.Copy ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0)
      End If
   Next cell

   ' Notify the user that the process is complete.
   MsgBox "All workbooks have been created successfully"

   ' Turn screen updating back on.
   Application.ScreenUpdating = True

End Sub


the code runs fine up until it encounters a worksheet that's already been created.
The run-time error 9 when trying to select or activate the worksheet is the problem. I can set the cell.Value as a variable and send it out to a MsgBox, can't see any issues with how its formatted or why it would be dying there.
I have tried removing rows to see if it was some sort of formatting (or other problem with that cell).
I'm assuming it's just somewhere in my code I haven't defined something properly or I've called something incorrectly.

optimally I would like to set the worksheet name as a variable and copy it like this:
Code:

cell.EntireRow.Copy VariableName.Range("A65536").End(xlUp).Offset(1, 0)


Anyway, I hope this was explanatory and someone can help out, in a bit of a pickle and need to have it done :-/


I am running the following code and I need a little help.

I first select "Sheet 040810" because because that is where the data I want to search is located. I am going to have many more sheets. How can I tell the program to search all sheets in the file that start with a number (as opposed to a letter)?

Thanks for the help

Sub kola5567()
Dim i As Long
Dim LASTROW As Long
Sheets("040810").Select
LASTROW = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LASTROW
If Sheets("040810").Range("B" & i).Value = Sheets("Sheet1").Range("B3").Value Then
Sheets("040810").Range("L" & i).Value = Sheets("Sheet1").Range("C3").Value
'Record Meadowbrook Date
Cells(i, 12).Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.NumberFormat = "mm/dd/yy h:mm;@"

'Insert Standloss Formula
Cells(i, 13).Select
ActiveCell.FormulaR1C1 = "=(RC[-5]-RC[-2])/(RC[-1]-RC[-4])*1000"
End If
Next i


If Cells(i, 13).Value < 1.75 Then
Sheets("P").Select
Else: Sheets("F").Select
End If



End Sub


I have a macro that works combining all worksheets from my workbook into a new worksheet but I need it to add in a new workbook with the data. It is set up to not copy headers and not copy the first page because it is just a log and is in the wrong format. Any ideas?

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
' copy headings
Sheets(3).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
' work through sheets
For J = 3 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub


I have code that requests the user to enter a sheet name (5 letter customer code) opens any excel file in the designated folder, in this case four files, and if the sheet exists, copies it to the current workbook. The problem I run into is that if the sheet doesn't exist for that customer, it errors out. The problem is that not all customers will have a sheet in all four files.

The files will always have the same names, but I want to leave the option open to add or remove files so I've kept the code generic in that respect. I've got almost zero experience with error handling, so any help would be greatly appreciated.

Code:

Sub CombineSheetsTest()

    Dim SFname As String
    Dim wbk As Workbook
    Dim ws As Worksheet
    Dim wSht As Variant
    Const sPath As String = "S:xxx[real path inserted here]xxx\"
        
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ChDir sPath
    SFname = Dir(sPath & "\" & "*" & ".xls", vbNormal)
    wSht = InputBox("Enter Required Customer Code")
    Do Until SFname = ""
        Set wbk = Workbooks.Open(sPath & SFname)
        Windows(SFname).Activate
        Sheets(wSht).Copy After:=ThisWorkbook.Sheets(1)
        wbk.Close False
        SFname = Dir()
    Loop
    
    Sheets(1).Delete
    Sheets(3).Move After:=Worksheets(Worksheets.Count)
    Sheets(2).Move After:=Worksheets(Worksheets.Count)
    Sheets(1).Move After:=Worksheets(Worksheets.Count)
    Sheets(4).Name = "Inventory"
    Sheets(3).Name = "Shortages"
    Sheets(2).Name = "CTB"
    Sheets(1).Name = "Commit Summary"
    Sheets(1).Select
    Application.Dialogs(xlDialogSaveAs).Show Format(Date, "mmddyy") & " " & wSht & " Report Package.xls"
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
        
    
End Sub





Hi,

I've been a lurker on this site for a bit now grabbing snippits here and there and the time has come for me to call upon your great minds.....

I have two similar spreadhseets and want to basically run the same macro's on both but for them to ignore certain points in the coding if the sheet doesn't exist.

The actions to be carried out if the sheet exists will either be delete the sheet or delete certain columns within the sheet.

To delete the columns I'm using:
Code:

    Sheets("Activity RM").Select
    Columns("AS:AS").Select
    Range("AS2").Activate
    Selection.Delete Shift:=xlToLeft


and to delete a sheet I'm using:

Code:

    Application.DisplayAlerts = False
    Sheets("Notes").Select
    ActiveWindow.SelectedSheets.Delete


These work on the one workbook because I know the named sheets exist, however they may not exist on both. Therefore to continue the above examples I want it to:
Check if Activity RM sheet exists and deletes the columns. If not to ignore that bit of code and go to the next action (which is to delete sheets)
Check if Notes sheet exists and if so deletes it. If not it ignores that and goes to the next bit of code (which will check and delete another sheet).

I hope that makes sense 'cos I'm a bit of a noob at all this.

Cheers

Gordi


I am trying to search column L for letters "N-V" and if found replace it with the letter "P". I have been successful searching for one letter and changing it to "P", but don't want to have tons of code if not needed. Thanks!!

Code:

Sub Change_ReturnType()
'Changes return type N-V to P'
i = 2
Sheets("Listed").Select
Do While Range("A" & i)  ""
If InStr(1, Range("L" & i), "N-V") > 0 Then
Range("L" & i) = "P"
End If
i = i + 1
Loop
End Sub





So I have very basic code that says unhide all hidden worksheets and what I want to do next is select 4 of those specific worksheets and delete contents starting in rows 1:5000 and then bring me back to the "Shipped" worksheet in cell A1.

Below is my code but the code stops at the red bolded text line below and I get an error that says run-time error '1004': select methoid of sheets class failed

Public Sub Unhide()

Dim sh As Worksheet

Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Sheets
sh.Visible = xlSheetVisible


Sheets(Array("Shipped", "Shipped_Balls", "Open", "Open_Balls")).Select
Sheets("Shipped").Activate
Rows("1:5000").Select
Selection.ClearContents
Sheets("Shipped").Activate
Range("A1").Select

Next

Application.ScreenUpdating = True

Sheets("Order_Pace_Summary").Select
Range("BA1").Select

End Sub

When I break up the unhide sheets separate from the clearing contents portion of the code it works fine, but together i get this error




Hello

Problems with the ol' Advanced Filters in 2003.

This code:

Code:

Sub Applyfilters2()
Application.ScreenUpdating = False
Dim x As Worksheet
Dim crit As Range
Sheets("Search").Activate
        Set crit = Range("Search!$D$3:$H$4")
  
        
For Each x In Sheets
    If x.Name  "Search" Then
        If x.Name  "Output" Then
        With x
            If x.Range("A5").Value = "" Then GoTo blnk
            .Select
        End With
With x.Range("A4:E3000")
    .AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("filter1"), _
    CopyToRange:=Sheets("Output").Range("B5:B3000").End(xlUp).Offset(1, -1), _
    Unique:=False
        
        x.AutoFilterMode = False
        Application.CutCopyMode = False
End With
        End If
    End If
blnk:
Next
       
Application.ScreenUpdating = True
Sheets("Output").Activate
For c = 3000 To 5 Step -1
    If Range("A" & c).Value = "DATE" Then
        Range("A" & c).EntireRow.Delete shift:=xlUp
    Else
    End If
Next
        
End Sub


work perfectly fine on the first sheet it comes across that meets the conditions (ie - isn't called 'Search' which is like the title page/search engine, 'Output' which is where the combined results all wind up, and if the sheet's table isn't blank)

So in order, left to right at the bottom of my excel, my sheets in order go "Output","Search","2008","2009","2010".

Whichever sheet is placed 3rd works - as in its data is filtered and winds up on the "Output" sheet. Whichever sheets come after that, nothing happens (I have tried moving the sheets around and this is the case)

So why does it work, and then stop? My header rows are all in the same place on each sheet (each sheet is basically identical except for its Name!) and the title rows have been copied and pasted across, so they all match exactly.

I have tried stepping through the code but it just carries on as if it hasn't found anything in those sheets.

Helpness Puh-lease! I hate these things...