Solved: Can I select a Range of Sheets

[daniels012]

Is there a way to select a range of Sheets?:help

I want to select say:

Sheet2 through Sheet4 'Not Sheet1 or Sheet5 or Sheet6

Or if I have
Tom, Bob, Will, Dave, Curt, Lou, Hank

I want Bob through Lou, How can I write VBA to get those Sheets?

Thank you,
Michael

[daniels012]

Out of Courtesy, I have a post here
http://www.mrexcel.com/board2/viewtopic.php?t=298216&postdays=0&postorder=asc&&start=0
That had a different question asked. But I added this question in the middle of the thread.

Thank you,:thumb :thumb
Michael

[rory]

You can use:
sheets(array("Bob", "Will", "Dave", "Curt", "Lou")).select

[daniels012]

Thank you for your response Rory
Is there a way without typing each name and just putting say:

sheets(array("Bob":"Lou")).select

I am trying to avoid the typing of every name. And if i add a name I have to change the macro as well to include the new sheet.

Michael

[Bob Phillips]

No, but you can do



ReDim arysheets(1 To 1)
For i = 1 To 3
ReDim Preserve arysheets(1 To i)
arysheets(i) = Worksheets(i + 1).Name
Next i
Worksheets(arysheets).Select

[Norie]

Michael

How will you be determining which sheets to work with?

By the way why are you selecting them anyway?

It's rarely if ever needed to select worksheets, ranges etc

[daniels012]

Hello Norie,
You bring up a GREAT point!


How will you be determining which sheets to work with?

You bring up a goos point. I know the first listed sheet will never be in the Array. I guess it just won't work without editing the macro manually each time we get a new person.:( :(
Even with a Range of sheets this would not help.

What if I put a list of the sheet names I want on the "Total" worksheet and then have code use this in the Sheets(Array( macro
Would this be feasible?

Michael

[Bob Phillips]

Dim i As Long
Dim LastRow As Long
Dim arySheets

With Worksheets(1)
LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
ReDim arySheets(1 To LastRow)
For i = 1 To LastRow
arySheets(i) = .Cells(i, "M").Value
Next i
End With
Worksheets(arySheets).Select

[Norie]

Michael

Let's turn it round.

Do you know which sheets you don't want to work with?

[daniels012]

XLD,
thabk you for your responses. Since i am not very good with, What is your code doing for me? Is it selecting the sheets I am desiring?

I have about 3 other worksheets that I dont want to print also. So I am not sure of my approach on this?

I appreciate all help given on this though!!!!!:bow:
Michael

[Norie]

Michael

If this is for printing there's no need to select the worksheets.

[daniels012]

No, Here is what i am doing.

Sub CopyTimeData()
Dim ws As Worksheet, NextRow As Long
For Each ws In Sheets(Array("Dave", "Tom", "Bob"))
NextRow = Sheets("Total").Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A6:I24").Copy
Sheets("Total").Range("A" & NextRow).PasteSpecial Paste:=xlPasteValues
Next ws
Application.CutCopyMode = False
End Sub

The sheet names that don't get printed are:
[[SS Total, Total, New Person]]
Thankyou
Michael

[Norie]

Michael

There ain't no printing in that code.

If what you are doing is consolidating data then there is no need to select and there's no need for any array.

Sub CopyTimeData()
Dim ws As Worksheet
Dim wsTotal As Worksheet
Dim NextRow As Long
Set wsTotal = Sheets("Total")

For Each ws In Worksheets
Select Case ws.Name
Case "SS Total", "Total", "New Person"
' do nothing
Case Else
NextRow = wsTotal.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A6:I24").Copy
wsTotal.Range("A" & NextRow).PasteSpecial Paste:=xlPasteValues
End Select
Next ws

Application.CutCopyMode = False
End Sub

[Bob Phillips]

XLD,
thabk you for your responses. Since i am not very good with, What is your code doing for me? Is it selecting the sheets I am desiring?

I have about 3 other worksheets that I dont want to print also. So I am not sure of my approach on this?

I appreciate all help given on this though!!!!!:bow:
Michael

It selects the sheets that you name in M1:Mn.

[daniels012]

Norie,
Could I do the Opposite case as you suggested? Then I can have the list of sheets I don't want data from. I think that was a great Idea, so that way when I add a worksheet it automatically copies it's data.

Thank You,
Michael

[Norie]

Michael

The code I posted does exclude the sheets you mentioned.

ie "SS Total", "Total", "New Person"

[daniels012]

OOps!:bug:
I am soooo dumb sometimes. I am sorry! I just didn't look at your code closely I guess:rotlaugh:
This is soooooo exactly what I want.:clap:

I did ammend it I needed Values and Formats to copy over so here is the code I ended up using:

Sub CopyTimeData()
With Range("A2:I150")
.ClearContents
.ClearFormats
End With
Dim ws As Worksheet
Dim wsTotal As Worksheet
Dim NextRow As Long
Set wsTotal = Sheets("Total")

For Each ws In Worksheets
Select Case ws.Name
Case "SS Totals", "Total", "NewPerson", "Sheet2", "Sheet3", "Roger"
' do nothing
Case Else
NextRow = wsTotal.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A6:I24").Copy
wsTotal.Range("A" & NextRow).PasteSpecial Paste:=xlPasteValues
wsTotal.Range("A" & NextRow).PasteSpecial Paste:=xlPasteFormats
End Select
Next ws

Application.CutCopyMode = False
End Sub
Norie, xld, and Rory will you folks help me everytime!!!:grouphug:

Michael