| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to pull in specific data into multiple worksheets in a workb | Excel Discussion (Misc queries) | |||
| add new data to specific columns in multiple worksheets | New Users to Excel | |||
| Copy data in multiple worksheets | New Users to Excel | |||
| Copy only certain data from multiple worksheets | Excel Discussion (Misc queries) | |||
| How do I retrieve data (specific cells) from multiple worksheets on a shared drive | Excel Worksheet Functions | |||
Hybrid Mode
