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

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

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

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

--
Regards,
Tom Ogilvy

"Jac" wrote:

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

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

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



"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Jac" wrote:

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

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

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 Befo=Workbooks("Book1").Sheets(1)
End Sub

--
Don Guillett
SalesAid Software

"Jac" wrote in message
...
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!!

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

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" wrote:

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 Befo=Workbooks("Book1").Sheets(1)
End Sub

--
Don Guillett
SalesAid Software

"Jac" wrote in message
...
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!!

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

The macro recorder is your friend.

--
Don Guillett
SalesAid Software

"Jac" wrote in message
...
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" wrote:

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 Befo=Workbooks("Book1").Sheets(1)
End Sub

--
Don Guillett
SalesAid Software

"Jac" wrote in message
...
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!!

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

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



"Don Guillett" wrote:

The macro recorder is your friend.

--
Don Guillett
SalesAid Software

"Jac" wrote in message
...
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" wrote:

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 Befo=Workbooks("Book1").Sheets(1)
End Sub

--
Don Guillett
SalesAid Software

"Jac" wrote in message
...
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!!

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

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

"Jac" wrote in message
...
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.........



"Don Guillett" wrote:

The macro recorder is your friend.

--
Don Guillett
SalesAid Software

"Jac" wrote in message
...
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" wrote:

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 Befo=Workbooks("Book1").Sheets(1)
End Sub

--
Don Guillett
SalesAid Software

"Jac" wrote in message
...
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!!