find sheets with by part of name
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 -
|