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!

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

"Hogometer" <(E-Mail Removed)> wrote in message
news:0BBA4809-34BC-4A4F-A814-(E-Mail Removed)...
> 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!

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!

"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!

Do you have anything in A1 to A5 please?

"Hogometer" <(E-Mail Removed)> wrote in message
news:ACB70D96-E5AA-4E38-891E-(E-Mail Removed)...
> 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!
>
> "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!

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

"PY & Associates" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Do you have anything in A1 to A5 please?
>
> "Hogometer" <(E-Mail Removed)> wrote in message
> news:ACB70D96-E5AA-4E38-891E-(E-Mail Removed)...
> > 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!
> >
> > "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!
>
>