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

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

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

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

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

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

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

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