View Full Version : select all sheets
vinny2984
10-19-2008, 05:14 AM
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
10-19-2008, 05:41 AM
Sub SelectAll()
Dim i As Long
With ActiveWorkbook
For i = 1 To .Worksheets.Count
.Worksheets(i).Select False
Next
End With
End Sub
Norie
10-19-2008, 09:30 AM
Try this.
Worksheets.Select
Though I have to ask why you need to select all the worksheets.
vinny2984
10-19-2008, 10:21 AM
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
10-19-2008, 10:41 AM
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
10-19-2008, 11:07 AM
vinny
Perhaps your question has been answered in the cross-post here (http://www.mrexcel.com/forum/showthread.php?t=347977).
georgiboy
10-19-2008, 11:11 AM
Naughty-Naughty :no2:
VBAX is the best forum around why go anywhere else !!!
vinny2984
10-19-2008, 11:30 AM
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
10-19-2008, 12:05 PM
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
10-19-2008, 12:33 PM
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
10-19-2008, 01:14 PM
What is "drng"? If it's a range then Sheets("Pump").Range("drng").Copy Similary for Paste
vinny2984
10-20-2008, 12:43 AM
thanks, i finally got this one working inthe ealy hours.
Bob Phillips
10-20-2008, 01:43 AM
So post the final result for all our info.
vinny2984
10-20-2008, 01:53 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.