I have a workbook with 5 sheets. Sheet 1 is my index sheet which lists upto
60 employees. The other 4 sheets are scheduling sheets. My situation is I
want to be able to copy the index sheet 4 columns and 60 rows from the index
to all 4 of my worksheets, and if possible I would like the updates to run
automatically either when I hit the enter key or click on the mouse. The main
problem I am having is I want to devide the index (4 columns 60 rows) into
three sets of columns with 4 columns and 20 rows so I can view all of the
employee names on one screen. Below is the code I have come up with from
reading other discussion threads so I don't know if it is right or not.
Please advise any changes and what I need to do to have the worksheets update
as I enter the data.
My other question is do you know if when I sort the index (after making
changes) will the other worksheets sort the data into the right order or can
that not be done with the index split into three groups.
Here is what I would like to have:
1 2 3 4 1 2 3 4 5 6 7 8 9 10 11 12 13
14 15 16
5 6 7 8
9 10 11 12
13 14 15 16

Here is the code I have now which works but does not run automatically. The
code is stored in Sheet1

Sub CopyRanges()
'
' Test_cop_2 Macro
' Macro recorded 10/20/2009 by
'

'
Range("A20:D39").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A20:D39").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("A20:D39").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
Range("A20:D39").Select
ActiveSheet.Paste
Sheets("Sheet5").Select
Range("A20:D39").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A40:D59").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("F20:I39").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("F20:I39").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
Range("F20:I39").Select
ActiveSheet.Paste
Sheets("Sheet5").Select
Range("F20:I39").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=24
Range("A60:D79").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("K20:N39").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("K20:N39").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
Range("K20:N39").Select
ActiveSheet.Paste
Sheets("Sheet5").Select
Range("K20:N39").Select
ActiveSheet.Paste
End Sub

--
BillD

I re-wrote you code to be more efficient

Sub CopyRanges()
'
' Test_cop_2 Macro
' Macro recorded 10/20/2009 by
'
for each sht in Sheets
if sht.name < "Sheet1" then
for Xoffset = 0 to 2
Set CopyRange = sht1.Range("A20:D39").offset(20 * Xoffset,0)
CopyRange.Copy _
Destination:=sht.Range("A20:D39").offset(0,6 * Xoffset)
next Xoffset
end if
next SHt

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=146124

Put this in the Sheet1 Module. I have the copy and destination all in one
line when I copied it.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("A20:D39")) Is Nothing Then
Me.Range("A20:D39").Copy Destination:=Sheets("sheet2").Range("A20:D39")
Me.Range("A20:D39").Copy Destination:=Sheets("sheet3").Range("A20:D39")
Me.Range("A20:D39").Copy Destination:=Sheets("sheet4").Range("A20:D39")
Me.Range("A20:D39").Copy Destination:=Sheets("sheet5").Range("A20:D39")
End If

If Not Intersect(Target.ME.Range("A40:D59")) Is Nothing Then
Me.Range("A40:D59").Copy Destination: Sheets("Sheet2").Range ("A40:D59")
Me.Range("A40:D59").Copy Destination: Sheets("Sheet2").Range ("A40:D59")
Me.Range("A40:D59").Copy Destination: Sheets("Sheet2").Range ("A40:D59")
Me.Range("A40:D59").Copy Destination: Sheets("Sheet2").Range ("A40:D59")
End If

If Not Intersect(Target, Me.Range("a60:d79")) Is Nothing Then
Me.Range("A60:D79").Copy Destination: Sheets("Sheet2").Range ("K20:N39")
Me.Range("A60:D79").Copy Destination: Sheets("Sheet2").Range ("K20:N39")
Me.Range("A60:D79").Copy Destination: Sheets("Sheet2").Range ("K20:N39")
Me.Range("A60:D79").Copy Destination: Sheets("Sheet2").Range ("K20:N39")




End Sub


HTH,
Barb Reinhardt


"BillD" wrote:

I have a workbook with 5 sheets. Sheet 1 is my index sheet which lists upto
60 employees. The other 4 sheets are scheduling sheets. My situation is I
want to be able to copy the index sheet 4 columns and 60 rows from the index
to all 4 of my worksheets, and if possible I would like the updates to run
automatically either when I hit the enter key or click on the mouse. The main
problem I am having is I want to devide the index (4 columns 60 rows) into
three sets of columns with 4 columns and 20 rows so I can view all of the
employee names on one screen. Below is the code I have come up with from
reading other discussion threads so I don't know if it is right or not.
Please advise any changes and what I need to do to have the worksheets update
as I enter the data.
My other question is do you know if when I sort the index (after making
changes) will the other worksheets sort the data into the right order or can
that not be done with the index split into three groups.
Here is what I would like to have:
1 2 3 4 1 2 3 4 5 6 7 8 9 10 11 12 13
14 15 16
5 6 7 8
9 10 11 12
13 14 15 16

Here is the code I have now which works but does not run automatically. The
code is stored in Sheet1

Sub CopyRanges()
'
' Test_cop_2 Macro
' Macro recorded 10/20/2009 by
'

'
Range("A20:D39").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A20:D39").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("A20:D39").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
Range("A20:D39").Select
ActiveSheet.Paste
Sheets("Sheet5").Select
Range("A20:D39").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A40:D59").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("F20:I39").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("F20:I39").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
Range("F20:I39").Select
ActiveSheet.Paste
Sheets("Sheet5").Select
Range("F20:I39").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=24
Range("A60:D79").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("K20:N39").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("K20:N39").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
Range("K20:N39").Select
ActiveSheet.Paste
Sheets("Sheet5").Select
Range("K20:N39").Select
ActiveSheet.Paste
End Sub

--
BillD