find sheets with by part of name

[Robert H]

I have a workbook that includes many sheets named "TT####" or ST####.
Also two summary sheets named TTall and STall.
Im working a a macro to find all the sheets beginning with TT or ST
then copy data from the sheets and paste that data in the respective
"__All" sheet. I can do all the copy and paste offsetting etc but
cannot figure out how to search through the sheet names for partial
names and work that into a for each statement.

I kick in the right direction would be helpful

 

[dan dungan]

Hi Robert,

Does this example from June 2009 help?
Newsgroups: microsoft.public.excel.programming
From: "Per Jessen" <[email protected]>
Date: Thu, 4 Jun 2009 19:23:29 +0200
Subject: Re: Adding to a "list sheet names" macro ...

HI

With numbers in column A and Sheet names in column B try this:

Sub SHEET_NAMES_list_all()
'list of sheet names starting at B1
Dim Rng As Range
Dim Sheet As Worksheet
Dim i As Long

Worksheets.Add(Before:=Worksheets(1)).Name = "ListOfSheetNames"
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> "ListOfSheetNames" Then
Rng.Offset(i, 1).Value = Sheet.Name
Rng.Offset(i, 0).Value = i + 1
i = i + 1
End If
Next
End Sub

Regards,
Per

 

[Otto Moehrbach]

Perhaps something like this:
For each ws in ThisWorkbook.Worksheets
If Left(ws.name,2)="TT" Or _
Left(ws.name,2)="ST" Then
'Copy/paste
End If
Next ws
Otto

 

[Rick Rothstein]

If Left(ws.name,2)="TT" Or _

Left(ws.name,2)="ST" Then

Or a little simpler...

If ws.Name Like "[TS]T*" Then

 

[Robert H]

Thanks for all the sugestions. I have a good starting point now and
will post back my final results.
Thanks
Robert

   If Left(ws.name,2)="TT" Or _
       Left(ws.name,2)="ST" Then

Or a little simpler...

If ws.Name Like "[TS]T*" Then

--
Rick (MVP - Excel)

Perhaps something like this:
For each ws in ThisWorkbook.Worksheets
   If Left(ws.name,2)="TT" Or _
       Left(ws.name,2)="ST" Then
   'Copy/paste
   End If
Next ws
Otto

- Show quoted text -

 

[Robert H]

My code ended up like so... Probably not the cleanest code but it
works and so far no bugs. next I have to buillt some automated
tallying and report creation.

Thanks again for the inputs it realy helped me get going.
Robert

Option Explicit

Public Sub BldSumry()
'creates the STall and TTall worksheets

Dim ws As Worksheet
Dim lRow As Long, lColToCheck As Long 'for selection
Dim myBegNM


'Get Source Data
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "[TS]T*" And Not ws.Name Like "[TS]Tall" Then

myBegNM = Left(ws.Name, 2)

ws.Activate
'find first empty row in column b
lColToCheck = 2 'Column b
'check last row in this column
If Cells(Rows.Count, lColToCheck).Formula > "" Then
'assume no empty cells in this column
lRow = Rows.Count
Else
lRow = Cells(Rows.Count, lColToCheck).End(xlUp).Row + 1
End If
'Select Range of cells to copy
Cells(lRow, lColToCheck).Offset(-1, -1).Select
Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select
Range(Selection, Selection.Offset(0, 6)).Select

Selection.Copy

'Set Destination either TTall or STall

Worksheets(myBegNM + "all").Activate

'Select next empty row to paste
lColToCheck = 1
If Cells(Rows.Count, lColToCheck).Formula > "" Then
lRow = Rows.Count
Else
lRow = Cells(Rows.Count, lColToCheck).End(xlUp).Row + 1
End If

Cells(lRow, lColToCheck).Offset(0, 0).Select
Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select

'Insert Data
ActiveSheet.Paste


End If
Next ws

End Sub

Thanks for all the sugestions. I have a good starting point now and
will post back my final results.
Thanks
Robert

Or a little simpler...

If ws.Name Like "[TS]T*" Then

- Show quoted text -- Hide quoted text -

- Show quoted text -