selecting all worksheets to the right of specified worksheet
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
|