Grouping and moving worksheets by name using VBA?
 

Hi all,
Is it possible to group sheets ending with a certain name and move them
to a new workbook in my documents named by the name i am grouping by?
Lets say i have 20 sheets each named "**** Today" where **** is a
variable, is it possible in VBA to group all the sheets with name
ending in Today and move them to a new workbook in my documents and
name the workbook "Today"?

Hope you can help!

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=546346

Grouping and moving worksheets by name using VBA?
 

Dim bReplace As Boolean, sh As Worksheet
Dim bk As Workbook
bReplace = True
For Each sh In Worksheets
If sh.Name Like "*Today" Then
sh.Select Replace:=bDontReplace
bReplace = False
End If
Next
ActiveWindow.SelectedSheets.Move
ActiveWorkbook.SaveAs Filename:=bk.Path & "\" & "Today.xls"

--
Regards,
Tom Ogilvy

"Simon Lloyd"
wrote in message
...

Hi all,
Is it possible to group sheets ending with a certain name and move them
to a new workbook in my documents named by the name i am grouping by?
Lets say i have 20 sheets each named "**** Today" where **** is a
variable, is it possible in VBA to group all the sheets with name
ending in Today and move them to a new workbook in my documents and
name the workbook "Today"?

Hope you can help!

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=546346

Grouping and moving worksheets by name using VBA?
 

Hi Tom thanks for the reply,

i tried the code below using an input box to select the sheet name, i
have tried variations but it moved the sheets that were not named in
the input box. If a sheet is called " here Today" and another "there
Today" and another "blog Today" i want to select all sheets ending in
the word today and move them to a new workbook call it Today and save
in my documents. here's the code ive been bumbling with! of course as
usual it doesnt quite work.
Hope you can help....again!
Regards,
Simon

Sub shtmove()
Dim bReplace As Boolean, sh As Worksheet
Dim bk As Workbook
Dim I2 As Integer
For I2 = 1 To 1
t1 = InputBox("Enter Last Name Of Sheets To Be Stored", "Which
Sheets To Store?", "")
bReplace = True
For Each sh In Worksheets
If sh.Name Like t1.Value Then
sh.Select Replace:=bDontReplace
bReplace = False
End If
Next
ActiveWindow.SelectedSheets.Move
ActiveWorkbook.SaveAs Filename:=bk.Path & "\" & t1.Value & ".xls"
Next
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=546346

Grouping and moving worksheets by name using VBA?
 

Sorry just forgot to mention the code you supplied Tom halts a
ActiveWindow.SelectedSheets.Move
I think this is because it selected all visible sheets so would leav
the active workbook without a visible sheet.

Regards,
simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=54634

Grouping and moving worksheets by name using VBA?
 

Hi Tom, I have tried working with this code but just can't get it to
work correctly, if its any help the Tabs are automatically coloured
Tab.ColorIndex = 40 this is done as the newly created sheet is named
during the course of creating these sheets over a 100 may have been
created which is why i wanted them to get moved by their last name to
another workbook called the same name!

Is there any further help you could supply with this?

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=546346