PDA

View Full Version : Need a FOR Loop which selects all worksheets with a number in the worksheet name



markpem
11-06-2015, 01:17 AM
Hello

I have some code at the moment which cycles through all worksheets and I currently have some code which copies all these workbooks and pastes them all on one sheet, which works great. However I now need to skip worksheets that are not named with a number like "Jan 2015" "Mar 2014" (the date will always be at the end) - The worksheets I want to skip will never contain any numbers in their name.

Is there a way to do say a FOR/NEXT or a DO/Loop in which it runs the code below worksheets IF the name of the worksheet selected (e.g. Mar 2015) contains a number, like:-



Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets

if WS ="NumberInSheetName" then
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Rows("1:" & lastrow).Select
Selection.Copy
Sheets("AllDataOnOneSheet").Select
newlastrow = Range("A" & Rows.Count).End(xlUp).Row
Cells("" + Str$(newlastrow), 1).Select
ActiveSheet.Paste
endif

Next


Many thanks if anyone can help!

Leith Ross
11-06-2015, 11:58 AM
Hello markpem,

This will allow the loop to execute if the worksheet contains a number .

Sub TestA()

Dim lastrow As Long
Dim newlastrow As Long
Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets

If WS.Name Like "*#*" Then
lastrow = WS.Range("A" & Rows.Count).End(xlUp).Row
WS.Rows("1:" & lastrow).Copy

With Worksheets("AllDataOnOneSheet")
newlastrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Cells(newlastrow, 1).Paste
End With
End If

Next WS

End Sub

SamT
11-07-2015, 08:03 AM
@ Leith. I like that "*#*".

@ OP, another way
If IsNumeric(Right(WS.Name, 1)) Then

Yet another way to skip certain sheets
dim BadSheets As Variant
BadSheets = Array ("sheet1", "Sheet2", "Etc")

For Each Sht in Worksheets
for 1 = 0 to UBound(BadSheets)
If WS.Name = Badsheets(i) Then GoTo SkipSheet
next i
'Code to run on good sheets
'
SkipSheet:
Next Sht