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!
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!