Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user can
run at the end of each week/month.

In order for this to happen - I need to select all the client worksheets and
delete specific ranges and rows within them. In terms of deleting the ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master report
sheets; and of course, the number of client sheets will grow each month so I
can't simply record the macro as it will be outdated as soon as a new client
is added.

Is there a method I can use to select all worksheets that are to the right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

Hello Paul,

You cannot rely on the user not rearranging sheets. Trust me! The users will
move sheets they are working on to the left where the tabs are visible. What
you need to do is identify the worksheets that you DON'T want included and
then identify their code names.

The worksheet code names can be found in the VBA editors Explorer on the
left. The code name is the first name and the name in parenthesis is the
changeable name. While the codename can be changed within VBA using
properties, it is not changeable by the user in the interactive mode.
Changing the changeable name will not affect the codename.

Having identified the code names as above, the following code will only
select the worksheets that you don't want excluded. I have used Select Case
because it is so easy to simply enter the codenames with double quotes and a
comma between them. (Better and simpler than If statements.)

Sub SelectSpecificSheets()
Dim ws As Worksheet
Dim shtsArray()
Dim i

i = 0 'Initialize to zero
For Each ws In Worksheets
Select Case ws.CodeName
Case "Sheet1", "Sheet2", "Sheet3"
'do nothing
Case Else
i = i + 1
ReDim Preserve shtsArray(1 To i)
shtsArray(i) = ws.CodeName
End Select
Next ws

Sheets(shtsArray).Select

End Sub



--
Regards,

OssieMac


"PVANS" wrote:

Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user can
run at the end of each week/month.

In order for this to happen - I need to select all the client worksheets and
delete specific ranges and rows within them. In terms of deleting the ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master report
sheets; and of course, the number of client sheets will grow each month so I
can't simply record the macro as it will be outdated as soon as a new client
is added.

Is there a method I can use to select all worksheets that are to the right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

Dim SelectedSheets As Variant
Dim CurrIdx As Long
Dim NextSheet As Long
Dim i As Long

With ActiveWorkbook

CurrIdx = .Worksheets("DT").Index
ReDim SelectedSheets(1 To .Worksheets.Count)
For i = CurrIdx + 1 To .Worksheets.Count

NextSheet = NextSheet + 1
SelectedSheets(NextSheet) = .Worksheets(i).Name
Next i

ReDim Preserve SelectedSheets(1 To NextSheet)

.Worksheets(SelectedSheets).Select
End With


--

HTH

Bob

"PVANS" wrote in message
...
Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user
can
run at the end of each week/month.

In order for this to happen - I need to select all the client worksheets
and
delete specific ranges and rows within them. In terms of deleting the
ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master report
sheets; and of course, the number of client sheets will grow each month so
I
can't simply record the macro as it will be outdated as soon as a new
client
is added.

Is there a method I can use to select all worksheets that are to the right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

Hi,

Try this

This line
Sheets(1).Activate
ensures sheets are not already grouped so may not be necessary

Sub SelectSheets()
Dim ws As Worksheet
Sheets(1).Activate
For Each ws In ActiveWorkbook.Sheets
If ws.Index Worksheets("DT").Index Then
ws.Select False
End If
Next ws
End Sub


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user can
run at the end of each week/month.

In order for this to happen - I need to select all the client worksheets and
delete specific ranges and rows within them. In terms of deleting the ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master report
sheets; and of course, the number of client sheets will grow each month so I
can't simply record the macro as it will be outdated as soon as a new client
is added.

Is there a method I can use to select all worksheets that are to the right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

Hi Paul

One way;


Sub Test()
Dim lIndex As Long

Sheets("DT").Activate
For lIndex = Sheets("DT").Index To Sheets.Count
Sheets(lIndex).Select False
Next lIndex

End Sub




--
Regards
Dave Hawley
www.ozgrid.com
"PVANS" wrote in message
...
Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user
can
run at the end of each week/month.

In order for this to happen - I need to select all the client worksheets
and
delete specific ranges and rows within them. In terms of deleting the
ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master report
sheets; and of course, the number of client sheets will grow each month so
I
can't simply record the macro as it will be outdated as soon as a new
client
is added.

Is there a method I can use to select all worksheets that are to the right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

OssieMac,

It's a very good point about rearranging sheets but I hope nobody changes
the codename!!
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"OssieMac" wrote:

Hello Paul,

You cannot rely on the user not rearranging sheets. Trust me! The users will
move sheets they are working on to the left where the tabs are visible. What
you need to do is identify the worksheets that you DON'T want included and
then identify their code names.

The worksheet code names can be found in the VBA editors Explorer on the
left. The code name is the first name and the name in parenthesis is the
changeable name. While the codename can be changed within VBA using
properties, it is not changeable by the user in the interactive mode.
Changing the changeable name will not affect the codename.

Having identified the code names as above, the following code will only
select the worksheets that you don't want excluded. I have used Select Case
because it is so easy to simply enter the codenames with double quotes and a
comma between them. (Better and simpler than If statements.)

Sub SelectSpecificSheets()
Dim ws As Worksheet
Dim shtsArray()
Dim i

i = 0 'Initialize to zero
For Each ws In Worksheets
Select Case ws.CodeName
Case "Sheet1", "Sheet2", "Sheet3"
'do nothing
Case Else
i = i + 1
ReDim Preserve shtsArray(1 To i)
shtsArray(i) = ws.CodeName
End Select
Next ws

Sheets(shtsArray).Select

End Sub



--
Regards,

OssieMac


"PVANS" wrote:

Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user can
run at the end of each week/month.

In order for this to happen - I need to select all the client worksheets and
delete specific ranges and rows within them. In terms of deleting the ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master report
sheets; and of course, the number of client sheets will grow each month so I
can't simply record the macro as it will be outdated as soon as a new client
is added.

Is there a method I can use to select all worksheets that are to the right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

Protect the Workbook for "Structure" to prevent sheet movement.


--
Regards
Dave Hawley
www.ozgrid.com
"Mike H" wrote in message
...
OssieMac,

It's a very good point about rearranging sheets but I hope nobody changes
the codename!!
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"OssieMac" wrote:

Hello Paul,

You cannot rely on the user not rearranging sheets. Trust me! The users
will
move sheets they are working on to the left where the tabs are visible.
What
you need to do is identify the worksheets that you DON'T want included
and
then identify their code names.

The worksheet code names can be found in the VBA editors Explorer on the
left. The code name is the first name and the name in parenthesis is the
changeable name. While the codename can be changed within VBA using
properties, it is not changeable by the user in the interactive mode.
Changing the changeable name will not affect the codename.

Having identified the code names as above, the following code will only
select the worksheets that you don't want excluded. I have used Select
Case
because it is so easy to simply enter the codenames with double quotes
and a
comma between them. (Better and simpler than If statements.)

Sub SelectSpecificSheets()
Dim ws As Worksheet
Dim shtsArray()
Dim i

i = 0 'Initialize to zero
For Each ws In Worksheets
Select Case ws.CodeName
Case "Sheet1", "Sheet2", "Sheet3"
'do nothing
Case Else
i = i + 1
ReDim Preserve shtsArray(1 To i)
shtsArray(i) = ws.CodeName
End Select
Next ws

Sheets(shtsArray).Select

End Sub



--
Regards,

OssieMac


"PVANS" wrote:

Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user
can
run at the end of each week/month.

In order for this to happen - I need to select all the client
worksheets and
delete specific ranges and rows within them. In terms of deleting the
ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master
report
sheets; and of course, the number of client sheets will grow each month
so I
can't simply record the macro as it will be outdated as soon as a new
client
is added.

Is there a method I can use to select all worksheets that are to the
right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

Protect the Workbook for "Structure" to prevent sheet movement.

And that could be removed in 10 seconds flat.

I think the point that Ossiemac has made is valid but getting a bullet-proof
solution is not going to be easy. To the malicious the presence of protection
is in itself a challenge and there is no functionality in Excel that will
defeat anyone with the ability to use Google and the will to remove
protection.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ozgrid.com" wrote:

Protect the Workbook for "Structure" to prevent sheet movement.


--
Regards
Dave Hawley
www.ozgrid.com
"Mike H" wrote in message
...
OssieMac,

It's a very good point about rearranging sheets but I hope nobody changes
the codename!!
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"OssieMac" wrote:

Hello Paul,

You cannot rely on the user not rearranging sheets. Trust me! The users
will
move sheets they are working on to the left where the tabs are visible.
What
you need to do is identify the worksheets that you DON'T want included
and
then identify their code names.

The worksheet code names can be found in the VBA editors Explorer on the
left. The code name is the first name and the name in parenthesis is the
changeable name. While the codename can be changed within VBA using
properties, it is not changeable by the user in the interactive mode.
Changing the changeable name will not affect the codename.

Having identified the code names as above, the following code will only
select the worksheets that you don't want excluded. I have used Select
Case
because it is so easy to simply enter the codenames with double quotes
and a
comma between them. (Better and simpler than If statements.)

Sub SelectSpecificSheets()
Dim ws As Worksheet
Dim shtsArray()
Dim i

i = 0 'Initialize to zero
For Each ws In Worksheets
Select Case ws.CodeName
Case "Sheet1", "Sheet2", "Sheet3"
'do nothing
Case Else
i = i + 1
ReDim Preserve shtsArray(1 To i)
shtsArray(i) = ws.CodeName
End Select
Next ws

Sheets(shtsArray).Select

End Sub



--
Regards,

OssieMac


"PVANS" wrote:

Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user
can
run at the end of each week/month.

In order for this to happen - I need to select all the client
worksheets and
delete specific ranges and rows within them. In terms of deleting the
ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master
report
sheets; and of course, the number of client sheets will grow each month
so I
can't simply record the macro as it will be outdated as soon as a new
client
is added.

Is there a method I can use to select all worksheets that are to the
right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

Disagree. You must be a glass half empty type of guy :) . It will stop sheet
movements in 99% of cases and is better than NOT protecting.

You lock your car/house, knowing any teenage kid can break in, so why NOT
protect your spreadsheet?


--
Regards
Dave Hawley
www.ozgrid.com


"Mike H" wrote in message
...
Protect the Workbook for "Structure" to prevent sheet movement.

And that could be removed in 10 seconds flat.

I think the point that Ossiemac has made is valid but getting a
bullet-proof
solution is not going to be easy. To the malicious the presence of
protection
is in itself a challenge and there is no functionality in Excel that will
defeat anyone with the ability to use Google and the will to remove
protection.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ozgrid.com" wrote:

Protect the Workbook for "Structure" to prevent sheet movement.


--
Regards
Dave Hawley
www.ozgrid.com
"Mike H" wrote in message
...
OssieMac,

It's a very good point about rearranging sheets but I hope nobody
changes
the codename!!
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"OssieMac" wrote:

Hello Paul,

You cannot rely on the user not rearranging sheets. Trust me! The
users
will
move sheets they are working on to the left where the tabs are
visible.
What
you need to do is identify the worksheets that you DON'T want included
and
then identify their code names.

The worksheet code names can be found in the VBA editors Explorer on
the
left. The code name is the first name and the name in parenthesis is
the
changeable name. While the codename can be changed within VBA using
properties, it is not changeable by the user in the interactive mode.
Changing the changeable name will not affect the codename.

Having identified the code names as above, the following code will
only
select the worksheets that you don't want excluded. I have used Select
Case
because it is so easy to simply enter the codenames with double quotes
and a
comma between them. (Better and simpler than If statements.)

Sub SelectSpecificSheets()
Dim ws As Worksheet
Dim shtsArray()
Dim i

i = 0 'Initialize to zero
For Each ws In Worksheets
Select Case ws.CodeName
Case "Sheet1", "Sheet2", "Sheet3"
'do nothing
Case Else
i = i + 1
ReDim Preserve shtsArray(1 To i)
shtsArray(i) = ws.CodeName
End Select
Next ws

Sheets(shtsArray).Select

End Sub



--
Regards,

OssieMac


"PVANS" wrote:

Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the
user
can
run at the end of each week/month.

In order for this to happen - I need to select all the client
worksheets and
delete specific ranges and rows within them. In terms of deleting
the
ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of
the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master
report
sheets; and of course, the number of client sheets will grow each
month
so I
can't simply record the macro as it will be outdated as soon as a
new
client
is added.

Is there a method I can use to select all worksheets that are to the
right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

Hello Mike, and Ossie

Thank you both for your suggestions - am managing to select all the
worksheets perfectly.

However, and this is my error, I seem to have assumed that I knew how to
clear the contents of the cells in all the worksheets; however it seems that
I am only successfully clearing the one worksheets' cells, and not the rest
even though they are all selected. The code I am using is:

Sub SelectSheets()
Dim ws As Worksheet
'Sheets(1).Activate
For Each ws In ActiveWorkbook.Sheets
If ws.Index Worksheets("Receipt Saxo").Index Then
ws.Select False
Range("A2:G500").ClearContents
Range("M2:M500").ClearContents

End If
Next ws

End Sub

Could you please suggest an alternative - I realise this is a different
query, and that I have posted it in the same query - its just that the code
would, I believe need to work together.

Thanks again for the earlier advise and hope you are able to help.

Regards,

"Mike H" wrote:

Hi,

Try this

This line
Sheets(1).Activate
ensures sheets are not already grouped so may not be necessary

Sub SelectSheets()
Dim ws As Worksheet
Sheets(1).Activate
For Each ws In ActiveWorkbook.Sheets
If ws.Index Worksheets("DT").Index Then
ws.Select False
End If
Next ws
End Sub


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user can
run at the end of each week/month.

In order for this to happen - I need to select all the client worksheets and
delete specific ranges and rows within them. In terms of deleting the ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master report
sheets; and of course, the number of client sheets will grow each month so I
can't simply record the macro as it will be outdated as soon as a new client
is added.

Is there a method I can use to select all worksheets that are to the right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

Darn it, such a silly error - fixed it by simply adding
ws.Range(A2:G500).ClearContents and it is now doing the trick

Thanks again

"PVANS" wrote:

Hello Mike, and Ossie

Thank you both for your suggestions - am managing to select all the
worksheets perfectly.

However, and this is my error, I seem to have assumed that I knew how to
clear the contents of the cells in all the worksheets; however it seems that
I am only successfully clearing the one worksheets' cells, and not the rest
even though they are all selected. The code I am using is:

Sub SelectSheets()
Dim ws As Worksheet
'Sheets(1).Activate
For Each ws In ActiveWorkbook.Sheets
If ws.Index Worksheets("Receipt Saxo").Index Then
ws.Select False
Range("A2:G500").ClearContents
Range("M2:M500").ClearContents

End If
Next ws

End Sub

Could you please suggest an alternative - I realise this is a different
query, and that I have posted it in the same query - its just that the code
would, I believe need to work together.

Thanks again for the earlier advise and hope you are able to help.

Regards,

"Mike H" wrote:

Hi,

Try this

This line
Sheets(1).Activate
ensures sheets are not already grouped so may not be necessary

Sub SelectSheets()
Dim ws As Worksheet
Sheets(1).Activate
For Each ws In ActiveWorkbook.Sheets
If ws.Index Worksheets("DT").Index Then
ws.Select False
End If
Next ws
End Sub


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user can
run at the end of each week/month.

In order for this to happen - I need to select all the client worksheets and
delete specific ranges and rows within them. In terms of deleting the ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master report
sheets; and of course, the number of client sheets will grow each month so I
can't simply record the macro as it will be outdated as soon as a new client
is added.

Is there a method I can use to select all worksheets that are to the right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

Glad I could contribute and thanks for the feedback.

Just one point; and I'm sure you've thought of it, don't forget to un-group
the sheets at the end of the code, leaving them grouped could give unexpected
results.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Darn it, such a silly error - fixed it by simply adding
ws.Range(A2:G500).ClearContents and it is now doing the trick

Thanks again

"PVANS" wrote:

Hello Mike, and Ossie

Thank you both for your suggestions - am managing to select all the
worksheets perfectly.

However, and this is my error, I seem to have assumed that I knew how to
clear the contents of the cells in all the worksheets; however it seems that
I am only successfully clearing the one worksheets' cells, and not the rest
even though they are all selected. The code I am using is:

Sub SelectSheets()
Dim ws As Worksheet
'Sheets(1).Activate
For Each ws In ActiveWorkbook.Sheets
If ws.Index Worksheets("Receipt Saxo").Index Then
ws.Select False
Range("A2:G500").ClearContents
Range("M2:M500").ClearContents

End If
Next ws

End Sub

Could you please suggest an alternative - I realise this is a different
query, and that I have posted it in the same query - its just that the code
would, I believe need to work together.

Thanks again for the earlier advise and hope you are able to help.

Regards,

"Mike H" wrote:

Hi,

Try this

This line
Sheets(1).Activate
ensures sheets are not already grouped so may not be necessary

Sub SelectSheets()
Dim ws As Worksheet
Sheets(1).Activate
For Each ws In ActiveWorkbook.Sheets
If ws.Index Worksheets("DT").Index Then
ws.Select False
End If
Next ws
End Sub


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user can
run at the end of each week/month.

In order for this to happen - I need to select all the client worksheets and
delete specific ranges and rows within them. In terms of deleting the ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master report
sheets; and of course, the number of client sheets will grow each month so I
can't simply record the macro as it will be outdated as soon as a new client
is added.

Is there a method I can use to select all worksheets that are to the right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul

The code that Mike has provided here will always include the far left sheet
in the selection irrespective of its name (even it is sheet "DT").

If Sheets(1).Activate is omitted then the code will still include any sheet
to the left of "DT" that is already selected prior to running the code.

--
Regards,

OssieMac


"Mike H" wrote:

Hi,

Try this

This line
Sheets(1).Activate
ensures sheets are not already grouped so may not be necessary

Sub SelectSheets()
Dim ws As Worksheet
Sheets(1).Activate
For Each ws In ActiveWorkbook.Sheets
If ws.Index Worksheets("DT").Index Then
ws.Select False
End If
Next ws
End Sub


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Good morning

I hope someone can help me with this:
I am trying to create a "clean master file" using a macro that the user can
run at the end of each week/month.

In order for this to happen - I need to select all the client worksheets and
delete specific ranges and rows within them. In terms of deleting the ranges
- I can do that.

However, I am unsure how to select all worksheets to the right of the
specific header worksheet called "DT" - I do not want to select the
worksheets to the left of this, as they contain invoice and master report
sheets; and of course, the number of client sheets will grow each month so I
can't simply record the macro as it will be outdated as soon as a new client
is added.

Is there a method I can use to select all worksheets that are to the right
of a specific sheet (the "DT" sheet)?

Thank you for any ideas and help given

Regards,

Paul