Copy data to multiple specific worksheets?
 

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!

Copy data to multiple specific worksheets?
 

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("B10:D20")

For i = 1 To 5

n$ = Cells(i, 1)

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

Next i

"Hogometer" wrote in message
...
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!

Copy data to multiple specific worksheets?
 

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("B10:D20")
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!

"Hogometer" wrote:

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!

Copy data to multiple specific worksheets?
 

Do you have anything in A1 to A5 please?

"Hogometer" wrote in message
...
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("B10:D20")
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!

"Hogometer" wrote:

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!

Copy data to multiple specific worksheets?
 

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

"PY & Associates" wrote in message
...
Do you have anything in A1 to A5 please?

"Hogometer" wrote in message
...
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("B10:D20")
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!

"Hogometer" wrote:

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!