How to select all sheets in front of a particular sheet???

[Guest]

Hi,

I have files which the sheets are structured in such a way as below:-
2009 2006 2005 2004 2003 2002 1990 Raw Data

Is there any way that I can use macro to select all the sheets placed in
front of Raw Data sheet and move it to a new file???
I have tried to record a macro to do so but all the sheet's names are being
specified in the code which made the macro not flexible; cause the sheets may
have been named with different years!!

Anyone has any solution; please advice....
Thanking in advance!!

 

[Guest]

Sub AC()
Dim bk2 as Workbook, sh as Worksheet
set bk2 = Workbooks("OtherBookd.xls")
Worksheets(1).Select
For Each sh In Worksheets
If LCase(sh.Name) <> "raw data" Then
sh.Select False
Else
Exit For
End If
Next
ActiveWindow.SelectedSheets.Move After:=Bk2.Worksheets(bk2.Worksheets.count)
Thisworkbook.Select
ThisWorkbook.Worksheets(1).Select
End Sub

 

[Guest]

Hi Tom,

Thanks for your reply and help..........

But if I wish to move all those selected sheets just to a new workbook, how
would I need to modify the code??? Cause I have tried to change the Move
statement as below but it doesn't work!!

ActiveWindow.SelectedSheets.Move

So, what would be your recommendation???
Thanks again!!

 

[Don Guillett]

Try this. Assumes book1 already in existance and open

Sub indexsheets()
For Each ws In Sheets
If UCase(ws.Name) = "RAW DATA" Then Exit For
ma = ma & "," & ws.Name
mx = Right(ma, Len(ma) - 1)
Next
Sheets(Split(mx, ",")).Select
Selection.Move Before:=Workbooks("Book1").Sheets(1)
End Sub

 

[Guest]

Hi Don,

Thanks for the solution!!

But what I want is not a fixed file which mean whenever all the sheets that
were to be moved are created; then the macro would select all of them & open
a new file then place those selected sheets in the new file!!! So, the new
file can be any file which would be created when need!!!

In MS Excel, it can be done by selecting (new book) in Move or Copy....
option!
But how to do it in coding???

 

[Don Guillett]

The macro recorder is your friend.

 

[Guest]

hi,

What do u mean by that???

The macro recorder will record down all the sheets' names in the coding
part!!!
That's what I don't want; & trying to look for other way out.........

 

[NickHK]

You have been shown how to get the list of sheet names.
The macro will show the syntax to move to a new WB.

Combine the 2...

NickHK

 

[Don Guillett]

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 6/28/2007 by Donald B. Guillett
'

'
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\myfolderl\mynewworkbook.xls",
FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub

can be reduced to
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\myfolderl\mynewworkbook.xls"