Copy Section of One Worksheet to Multiple Worksheets

  • Thread starter Thread starter PHisaw
  • Start date Start date
P

PHisaw

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
 
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
 
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
 
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,

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
 
I omitted
Dim i As Integer

you could use copy if prefer but range should give required result.
 
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
 
Back
Top