select all sheets

[vinny2984]

I'm sure this is simple, but i don't know the answer.
I'm after a command that will activate or select all sheets in my current workbook.
Can anyone help m out?
thanks.

[Bob Phillips]

Sub SelectAll()
Dim i As Long
With ActiveWorkbook
For i = 1 To .Worksheets.Count
.Worksheets(i).Select False
Next
End With
End Sub

[Norie]

Try this.

Worksheets.Select

Though I have to ask why you need to select all the worksheets.

[vinny2984]

well i was hoping that if i can select/activate all the sheets or a group of sheets that i could then copy and paste a range from 1 sheet to all the others in my sheet selection. But i must admit, i'm going round in circles trying to make it work

[georgiboy]

You could use some kind of if or like statement to identify the sheet, then paste to it like in this example, using a for loop.

Sub CopyToShRange()

For Each r In ActiveWorkbook.Sheets

If Right(r.Name, 1) > 1 Then ' could be any if statement

Sheet1.Range("A1").Copy r.Range("A1")

End If

Next


End Sub

Hope this helps

[Norie]

vinny

Perhaps your question has been answered in the cross-post here (http://www.mrexcel.com/forum/showthread.php?t=347977).

[georgiboy]

Naughty-Naughty :no2:

VBAX is the best forum around why go anywhere else !!!

[vinny2984]

afraid not. this is the latest version which is just as useful as my first 30 or 40 attempts, but i feel its getting close

For incr = 1 To 49 Step 4
With ActiveWorkbook
For i = 4 To .Worksheets.count
Sheet1.drng.Copy i.drng.Offset(incr, 0) 'says "i"is invalid qualifier'
Selection.Copy
Next i
End With
Next incr

[Bob Phillips]

With ActiveWorkbook
For i = 4 To .Worksheets.count
For incr = 1 To 49 Step 4
Sheet1.drng.Copy Worksheets(i).drng.Offset(incr, 0)
Next incr
End With
Next i

[vinny2984]

i tried this below, only change is to the name of sheet 1 but is comes up with error

With ActiveWorkbook
For i = 4 To .Worksheets.count
For incr = 1 To 49 Step 4
Sheets("Pump").drng.Copy Worksheets(i).drng.Offset(incr, 0) 'says property or method not supported'
Next incr
Next i
End With

[mdmackillop]

What is "drng"? If it's a range then Sheets("Pump").Range("drng").Copy Similary for Paste

[vinny2984]

thanks, i finally got this one working inthe ealy hours.

[Bob Phillips]

So post the final result for all our info.

[vinny2984]

sorry bout that, i thought i had pasted but clearly not


For incr = 1 To 49 Step 4
With ActiveWorkbook
For i = 4 To .Worksheets.count
Set drng1 = drng.Offset(incr - 1, 0)
drng1.Copy Destination:=Worksheets(i).Range("B1").Offset(incr - 1, 0)
Next i
End With
Next incr