Copy data to multiple specific worksheets?

[Guest]

I wish to automatically copy a range of cells from one worksheet to several
other worksheets for which their names are contained in a range. For example:

IN the Active Worksheet:
Range a1:a5 contains a list of established worksheet names as follows (note:
there are other worksheets that are not in this list):
Facility 1
Facility 2
Facility 3
Facility 4
Facility 5

Range b10:d50 contains the cells I wish to copy to worksheet named in the
above range.

So, is there a simple macro that can copy b10:d50 in the specified
worksheets that are indicated in a1:a5 ?

Thank you!

 

[PY & Associates]

Adjust to suit, no checking of sheet tags


Dim srcws As Worksheet

Dim desws As Worksheet

Dim copyrng As Range

Set srcws = ActiveSheet

Set copyrng = srcws.Range("B1020")

For i = 1 To 5

n$ = Cells(i, 1)

copyrng.Copy Sheets(n).Range("B10")

Next i

 

[Guest]

Thank you for your response! When I used your suggestion as programmed here,
I get an error - "subscript out of range" for line "copyrng.Copy
Sheets(n).Range("B10").

Here is the exact code I used:

Sub Macro1()

Dim desws As Worksheet
Dim copyrng As Range
Set srcws = ActiveSheet
Set copyrng = srcws.Range("B1020")
For i = 1 To 5
n$ = Cells(i, 1)
copyrng.Copy Sheets(n).Range("B10")
Next i

End Sub


Is there an obvious problem here?

Thanks again!

 

[PY & Associates]

Do you have anything in A1 to A5 please?

Thank you for your response! When I used your suggestion as programmed here,
I get an error - "subscript out of range" for line "copyrng.Copy
Sheets(n).Range("B10").

Here is the exact code I used:

Sub Macro1()

Dim desws As Worksheet
Dim copyrng As Range
Set srcws = ActiveSheet
Set copyrng = srcws.Range("B1020")
For i = 1 To 5
n$ = Cells(i, 1)
copyrng.Copy Sheets(n).Range("B10")
Next i

End Sub


Is there an obvious problem here?

Thanks again!

 

[PY & Associates]

Your messages are falling off our screen.
Can you send messages to us direct please?