Paste into grouped sheets using VB

[kara stevenson]

I am trying to paste data from one sheet into an array of grouped sheets, but using "Selection" or other like words isn't working. Please help! I am new to this board, so please pardon any formatting issues. Here's what I have:

Sub copy()
'
' copy Macro
' Macro recorded 1/16/2009 by n893581x
'

'
Sheets("Last").Select
Range("BP1O4").Select
Selection.copy
Dim MyArray() As String 'Dimension an array as string type
Dim iLoop As Integer 'Dimension a variable as integer to be used for a loop
Dim Cnt As Integer 'Dimension a variable as integer for a counter for each sheet named group
' Start a loop through all sheets in the workbook
For iLoop = 1 To Sheets.Count
' Check if the sheets has the name "Group" and then a number
If Worksheets(iLoop).Name Like "#*" Then
' Sheet Match Found So Count
Cnt = Cnt + 1
' Redimension the array and preserve any existing entrys
ReDim Preserve MyArray(Cnt)
' Add the sheet to the array
MyArray(Cnt) = Worksheets(iLoop).Name
End If
Next iLoop
' Check if the array contains any sheets and if so select them
If UBound(MyArray) > 0 Then Worksheets(MyArray).Select
Sheets("48643").Select
Range("BP1").Select
.PasteSpecial Paste:=xlPasteValues
Range("B32").Select


End Sub

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

 

[Don Guillett]

A bit simpler withOUT any selections run from anywhere in the workbook.

Sub copyvaluestoshts()
Dim i As Long
Sheets("last").Range("BP1O4").Copy
For i = 1 To Sheets.Count
If UCase(Left(Sheets(i).Name, 5)) = "GROUP" Then
Sheets(i).Range("bp1").PasteSpecial Paste:=xlPasteValues
End If
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message news:[email protected]...

 

[Dave Peterson]

I'd just loop through the sheets and paste when I found a match in names:

Option Explicit
Sub myCopy()

Dim RngToCopy As Range
Dim wks As Worksheet

Set RngToCopy = Worksheets("Last").Range("bp1:do4")

For Each wks In ActiveWorkbook.Worksheets
If LCase(wks.Name) Like LCase("#*") Then
RngToCopy.copy
wks.Range("bp1").PasteSpecial Paste:=xlPasteValues
End If
Next wks

End Sub