selecting all worksheets to the right of specified worksheet

[PVANS]

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]

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

 

[Bob Phillips]

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

 

[Mike H]

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.

 

[ozgrid.com]

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

 

[Mike H]

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.

 

[ozgrid.com]

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

 

[Mike H]

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]

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?

 

[PVANS]

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,

 

[PVANS]

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

 

[Mike H]

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.

 

[OssieMac]

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.