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

Hi Robert,

Does this example from June 2009 help?
Newsgroups: microsoft.public.excel.programming
From: "Per Jessen"
Date: Thu, 4 Jun 2009 19:23:29 +0200
Subject: 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(Befo=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

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
"Robert H" wrote in message
...
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

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)


"Otto Moehrbach" wrote in message
...
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
"Robert H" wrote in message
...
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

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

On Mar 1, 4:56*pm, "Rick Rothstein"
wrote:
* *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)

"Otto Moehrbach" wrote in message

...



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
"Robert H" wrote in message
....
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- Hide quoted text -

- Show quoted text -

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

On Mar 2, 8:24*am, Robert H wrote:
Thanks for all the sugestions. I have a good starting point now and
will post back my final results.
Thanks
Robert

On Mar 1, 4:56*pm, "Rick Rothstein"



wrote:
* *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)

"Otto Moehrbach" wrote in message

...

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
"Robert H" wrote in message
....
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -