| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Section of One Worksheet to Multiple Worksheets
Hi,
I want to copy range v1:am75 from "Bookings" to the same section for sheets "01-09", "02-09", "03-09", "04-09" and can get nothing to work. Can someone please help with this? Thanks in advance, Phisaw |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Section of One Worksheet to Multiple Worksheets
something like this may do what you want. Place in a standard module.
Sub CopyRanges() Dim wsrng As Range Dim myarray() Set wsrng = Worksheets("Bookings").Range("V1:AM75") myarray = Array("01-09", "02-09", "03-09", "04-09") For i = LBound(myarray) To UBound(myarray) Worksheets(myarray(i)).Range("V1:AM75").Value = wsrng.Value Next End Sub -- jb "PHisaw" wrote: Hi, I want to copy range v1:am75 from "Bookings" to the same section for sheets "01-09", "02-09", "03-09", "04-09" and can get nothing to work. Can someone please help with this? Thanks in advance, Phisaw |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Section of One Worksheet to Multiple Worksheets
Hi
See if this does what you need: Sub aaa() myArr = Split("01-09,02-09,03-09,04-09", ",") Worksheets("Bookings").Range("V1:AM75").Copy For sh = LBound(myArr) To UBound(myArr) ActiveSheet.Paste Destination:=Worksheets(myArr(sh)).Range("V1") Next Application.CutCopyMode = False End Sub Regards, Per "PHisaw" skrev i meddelelsen ... Hi, I want to copy range v1:am75 from "Bookings" to the same section for sheets "01-09", "02-09", "03-09", "04-09" and can get nothing to work. Can someone please help with this? Thanks in advance, Phisaw |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Section of One Worksheet to Multiple Worksheets
John,
Thank you for replying. I tried the code and it caused an error "variable not defined" on line For i = LBound(myarray) To UBound(myarray) with "i" highlighted. Can you please tell me how to define it? I'm a newbie and trying to learn. Can you also please explain your code - LBound, UBound, etc.? Why wouldn't something simple like the code below work? Dim X As Variant X = Array("01-09", "02-09", "03-09", _ "04-09", "05-09", "06-09") Sheets(X).Copy _ Worksheets("Bookings").Range("v1:am75") Thanks again, Phisaw "john" wrote: something like this may do what you want. Place in a standard module. Sub CopyRanges() Dim wsrng As Range Dim myarray() Set wsrng = Worksheets("Bookings").Range("V1:AM75") myarray = Array("01-09", "02-09", "03-09", "04-09") For i = LBound(myarray) To UBound(myarray) Worksheets(myarray(i)).Range("V1:AM75").Value = wsrng.Value Next End Sub -- jb "PHisaw" wrote: Hi, I want to copy range v1:am75 from "Bookings" to the same section for sheets "01-09", "02-09", "03-09", "04-09" and can get nothing to work. Can someone please help with this? Thanks in advance, Phisaw |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Section of One Worksheet to Multiple Worksheets
John,
Thank you for replying. I tried the code and it caused an error "variable not defined" on line: For i = LBound(myarray) To UBound(myarray) with "i" highlighted. Can you please tell me how to define it? I'm a newbie and trying to learn. Can you also please explain your code - LBound, UBound, etc.? Why wouldn't something simple like the code below work? Dim X As Variant X = Array("01-09", "02-09", "03-09", _ "04-09", "05-09", "06-09") Sheets(X).Copy _ Worksheets("Bookings").Range("v1:am75") Thanks again, Phisaw "john" wrote: something like this may do what you want. Place in a standard module. Sub CopyRanges() Dim wsrng As Range Dim myarray() Set wsrng = Worksheets("Bookings").Range("V1:AM75") myarray = Array("01-09", "02-09", "03-09", "04-09") For i = LBound(myarray) To UBound(myarray) Worksheets(myarray(i)).Range("V1:AM75").Value = wsrng.Value Next End Sub -- jb "PHisaw" wrote: Hi, I want to copy range v1:am75 from "Bookings" to the same section for sheets "01-09", "02-09", "03-09", "04-09" and can get nothing to work. Can someone please help with this? Thanks in advance, Phisaw |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Section of One Worksheet to Multiple Worksheets
I omitted
Dim i As Integer you could use copy if prefer but range should give required result. -- jb "PHisaw" wrote: John, Thank you for replying. I tried the code and it caused an error "variable not defined" on line: For i = LBound(myarray) To UBound(myarray) with "i" highlighted. Can you please tell me how to define it? I'm a newbie and trying to learn. Can you also please explain your code - LBound, UBound, etc.? Why wouldn't something simple like the code below work? Dim X As Variant X = Array("01-09", "02-09", "03-09", _ "04-09", "05-09", "06-09") Sheets(X).Copy _ Worksheets("Bookings").Range("v1:am75") Thanks again, Phisaw "john" wrote: something like this may do what you want. Place in a standard module. Sub CopyRanges() Dim wsrng As Range Dim myarray() Set wsrng = Worksheets("Bookings").Range("V1:AM75") myarray = Array("01-09", "02-09", "03-09", "04-09") For i = LBound(myarray) To UBound(myarray) Worksheets(myarray(i)).Range("V1:AM75").Value = wsrng.Value Next End Sub -- jb "PHisaw" wrote: Hi, I want to copy range v1:am75 from "Bookings" to the same section for sheets "01-09", "02-09", "03-09", "04-09" and can get nothing to work. Can someone please help with this? Thanks in advance, Phisaw |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Section of One Worksheet to Multiple Worksheets
dimensioning variables is good practice as it allows the compiler to allocate
the correct memory. example DIM i as Long with arrays, they can be of indeterminite size so for example you could could have X(1 to 10) or X(0 to y) the functions UBOUND() and LBOUND() enable you find these boundaries As yuo have it X is a variant containing strings(text) which are the names of 6 worksheets so Sheets(X).Copy is legitimate and wouild copy all 6 sheets to a new workbook However, your target is a range, and you simply cannot copy 6 worksheets into a range. sheets contain ranges, not vice-versa "PHisaw" wrote: John, Thank you for replying. I tried the code and it caused an error "variable not defined" on line For i = LBound(myarray) To UBound(myarray) with "i" highlighted. Can you please tell me how to define it? I'm a newbie and trying to learn. Can you also please explain your code - LBound, UBound, etc.? Why wouldn't something simple like the code below work? Dim X As Variant X = Array("01-09", "02-09", "03-09", _ "04-09", "05-09", "06-09") Sheets(X).Copy _ Worksheets("Bookings").Range("v1:am75") Thanks again, Phisaw "john" wrote: something like this may do what you want. Place in a standard module. Sub CopyRanges() Dim wsrng As Range Dim myarray() Set wsrng = Worksheets("Bookings").Range("V1:AM75") myarray = Array("01-09", "02-09", "03-09", "04-09") For i = LBound(myarray) To UBound(myarray) Worksheets(myarray(i)).Range("V1:AM75").Value = wsrng.Value Next End Sub -- jb "PHisaw" wrote: Hi, I want to copy range v1:am75 from "Bookings" to the same section for sheets "01-09", "02-09", "03-09", "04-09" and can get nothing to work. Can someone please help with this? Thanks in advance, Phisaw |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Section of One Worksheet to Multiple Worksheets
Thank you both, John and Patrick, for the code and explanation. Greatly
appreciated. "Patrick Molloy" wrote: dimensioning variables is good practice as it allows the compiler to allocate the correct memory. example DIM i as Long with arrays, they can be of indeterminite size so for example you could could have X(1 to 10) or X(0 to y) the functions UBOUND() and LBOUND() enable you find these boundaries As yuo have it X is a variant containing strings(text) which are the names of 6 worksheets so Sheets(X).Copy is legitimate and wouild copy all 6 sheets to a new workbook However, your target is a range, and you simply cannot copy 6 worksheets into a range. sheets contain ranges, not vice-versa "PHisaw" wrote: John, Thank you for replying. I tried the code and it caused an error "variable not defined" on line For i = LBound(myarray) To UBound(myarray) with "i" highlighted. Can you please tell me how to define it? I'm a newbie and trying to learn. Can you also please explain your code - LBound, UBound, etc.? Why wouldn't something simple like the code below work? Dim X As Variant X = Array("01-09", "02-09", "03-09", _ "04-09", "05-09", "06-09") Sheets(X).Copy _ Worksheets("Bookings").Range("v1:am75") Thanks again, Phisaw "john" wrote: something like this may do what you want. Place in a standard module. Sub CopyRanges() Dim wsrng As Range Dim myarray() Set wsrng = Worksheets("Bookings").Range("V1:AM75") myarray = Array("01-09", "02-09", "03-09", "04-09") For i = LBound(myarray) To UBound(myarray) Worksheets(myarray(i)).Range("V1:AM75").Value = wsrng.Value Next End Sub -- jb "PHisaw" wrote: Hi, I want to copy range v1:am75 from "Bookings" to the same section for sheets "01-09", "02-09", "03-09", "04-09" and can get nothing to work. Can someone please help with this? Thanks in advance, Phisaw |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Frozen worksheet section to show on all other worksheets | Excel Discussion (Misc queries) | |||
| Copy same cell in multiple worksheets onto one worksheet | Excel Discussion (Misc queries) | |||
| How to copy worksheet template into multiple worksheets | Excel Programming | |||
| how to copy data from one worksheet to multiple worksheets at once | Excel Worksheet Functions | |||
| Copy range from one worksheet and paste to multiple worksheets | Excel Discussion (Misc queries) | |||
Linear Mode
