Solved: Select Tabs using a string

[Hambone]

Hi All, I am trying to find an easier way. I gather data off of multiple pages in the same locations. I have been calling by the tab names which are label as the date ex. 20130604 the down fall is there is no tab 20130602 and 20130601 so this code stops. I am using a string "strSheets" to find the tabs. I know there is a way to just move on through the tabs without having to use this string setup; however, i can't remember it.

Can anyone help

Thanks
Tom

Do Until strSheets = "20130528"

Sheets(strSheets).Select
Range("E28:O28").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A3").Select
Selection = strSheets

Rows("3:3").Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B3").Select

Sheets(strSheets).Select

Range("E15:O15").Select
Selection.Copy
Sheets("Sheet1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A3").Select
Selection = strSheets

Rows("3:3").Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

strSheets = strSheets - 1
Loop

[p45cal]

try this; it processes every sheet in the active workbook which isn't called Sheet1. There may be other sheets apart from Sheet1 that you want to exclude - that's easy.For Each sht In Worksheets
If sht.Name <> "Sheet1" Then
With Sheets("Sheet1")
.Range("B3").Resize(, 11).Value = sht.Range("E28:O28").Value
.Range("A3") = sht.Name
.Rows("3:3").Insert

.Range("B3").Resize(, 11).Value = sht.Range("E15:O15").Value
.Range("A3") = sht.Name
.Rows("3:3").Insert
End With
End If
Next sht
I realise that the sheets may not be processed in the same order, but we can solve that if necessary, either by sorting after the copying's finished or by devising a way to go through them in the right order. Depending on the make up of Sheet1, those 6 lines beginning .Range / .Rows could be reduced to two or three.

[Hambone]

p45cal, i will give it a try and let you know.

thanks for the help

Tom

[snb]

With this code there's only 1 writing operation

Sub M_snb()
Set tr = CreateObject("scripting.dictionary")

For Each sh In Sheets
If sh.Name <> "Sheet1" Then
tr(sh.Name & 15) = sh.Range("E15:O15")
tr(sh.Name & 28) = sh.Range("E28:O28")
End If
Next

Sheets("sheet1").Cells(1).Resize(tr.Count, 11) = Application.Index(tr.items, 0, 0)
End Sub

[p45cal]

With this code there's only 1 writing operation

Sub M_snb()
Set tr = CreateObject("scripting.dictionary")

For Each sh In Sheets
If sh.Name <> "Sheet1" Then
tr(sh.Name & 15) = sh.Range("E15:O15")
tr(sh.Name & 28) = sh.Range("E28:O28")
End If
Next

Sheets("sheet1").Cells(1).Resize(tr.Count, 11) = Application.Index(tr.items, 0, 0)
End SubYou'll need one more to put the sheet names in; something like
Sheets("sheet1").Cells(1).Resize(tr.Count) = Application.Transpose(tr.keys)
but that will also need something to remove the row numbers.
(and let's hope there are no chart sheets! :) )

[snb]

Sub M_snb()
Set tr = CreateObject("scripting.dictionary")

For Each sh In Sheets
If sh.Name <> "Sheet1" Then
tr(sh.Name & 15) = Split(sh.Name & " " & Join(Application.Index(sh.Range("E15:O15").Value, 1, 0)))
tr(sh.Name & 28) = Split(sh.Name & " " & Join(Application.Index(sh.Range("E28:O28").Value, 1, 0)))
End If
Next

Sheets("sheet1").Cells(1).Resize(tr.Count, 12) = Application.Index(tr.items, 0, 0)
End Sub

[p45cal]

Sub M_snb()
Set tr = CreateObject("scripting.dictionary")

For Each sh In Sheets
If sh.Name <> "Sheet1" Then
tr(sh.Name & 15) = Split(sh.Name & " " & Join(Application.Index(sh.Range("E15:O15").Value, 1, 0)))
tr(sh.Name & 28) = Split(sh.Name & " " & Join(Application.Index(sh.Range("E28:O28").Value, 1, 0)))
End If
Next

Sheets("sheet1").Cells(1).Resize(tr.Count, 12) = Application.Index(tr.items, 0, 0)
End SubThis one's interspersing lots of Sheet names in the results, not just on the left.

[snb]

Don't think so.

[p45cal]

Don't think so.Try it with the attached.

[Hambone]

Thanks everyone for the help. i am going to give it a try now to see how it works out. No there is no chart sheets; however, i am gathering this data to make a chart. Eventually, I will want to copy and create the new tab with the date format which i can handle. I am trying to recreate an older version of this program to meet my current needs.

Once again thank you for the responses i will let you know how it turns out.

Tom

[Hambone]

P45 I was getting all the wrong data because it was going through all the sheets in the workbook even the hiden ones.

I am still having issues getting through all the necessary tabs to get my information. would a For | Next statement work better for going through the tabs needed?

Thanks
Tom

[p45cal]

P45 I was getting all the wrong data because it was going through all the sheets in the workbook even the hiden ones.

I am still having issues getting through all the necessary tabs to get my information. would a For | Next statement work better for going through the tabs needed?

Thanks
TomYou can stop hidden sheets from being processed by altering one line to: If sht.Name <> "Sheet1" And sht.Visible = xlSheetVisible Thenbut if that's not enough then depending on just how many sheets we're talking about and whether sheets are frequently added (or removed) or their names changed, we could set up an exclusion list or an inclusion list.

[Hambone]

P45 I add a new sheet evreyday and it follows the same date format 20130605. where I am getting stuck is when a weekend appears I can not get to the next tab because strSheets = strSheets - 1 line creates a name that doesn't exist. I put in on error statement and it works once; however, seeing two are missing that also sends my macro into debug.

Any thoughts on how to get to the next tab? is using a string the wrong way to find a tab in a workbook? it has been at least 7 years since I have used VBA and like usual what you don't use you lose.

Thanks
Tom

[p45cal]

Could we safely say that if:
1. the length of the tab name is 8 characters
2. it starts with "20"
3. it's all numeric
then we should process it - hidden or not?
If so it should last another 86 years or so - is that long enough?

[Hambone]

it isn't pretty; however, i got it to work with the following code and help from another post i had. Now i can work on shortening up the copy and paste you listed above.

Dim dtmdate As Date
Dim strSheets As String
Application.ScreenUpdating = False
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
strSheets = Format(Date, "yyyymmdd")
dtmdate = Now()
On Error GoTo skiptab
Do Until strSheets = "20130528"

Sheets(strSheets).Select
Range("E28:O28").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A3").Select
Selection = strSheets

Rows("3:3").Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B3").Select

Sheets(strSheets).Select

Range("E15:O15").Select
Selection.Copy
Sheets("Sheet1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A3").Select
Selection = strSheets

Rows("3:3").Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

strSheets = strSheets - 1
Loop
Exit Sub
skiptab:
strSheets = strSheets - 1
Resume

Thanks for all the help. I will be marking this as solved. I am sure I will be back with another issue lol...

Tom