On Wed, 28 Jan 2009 07:25:01 -0800, Terry
wrote:


I want to minimise the amount of copying / pasting that the lecturers have
to do - i.e they can just type in where the objectives have changed and this
will be reflected in their future objectives, all contained within one
workbook. I'm now thinking the best way to do this may be to write a
relatively straightforward macro which will copy all information from the
sheet they have edited and paste to all subsequent sheets, but not ones
before it. My only issue here is that I'm not sure of the coding to basically
say "copy this sheet and copy to all subsequent, regardless of their name". I
can do it if it copy and paste to a specifically named sheet, e.g somethig
like:
Sheets("Mon").Select
Cells.Select
Selection.Copy
Sheets("Tues").Select
Cells.Select
ActiveSheet.Paste


Here's one options

Sub CopyToSubsequent()

Dim i As Long
Dim sh As Object
Dim rUsed As Range

Set rUsed = ActiveSheet.UsedRange

For i = ActiveSheet.Index + 1 To ThisWorkbook.Sheets.Count
Set sh = ThisWorkbook.Sheets(i)
If TypeName(sh) = "Worksheet" Then
rUsed.Copy sh.Range(rUsed.Address)
End If
Next i

End Sub
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com