Programming a function to copy and paste to other worksheets

[Terry]

Hi, I hope someone may be able to offer some help here! I initially posted
this question on "General Questions" and a very helpful member offered me
some advice and suggested i post it here! My excel knowledge is decent
enough, but have little programming knowledge and think this is what I need.
I've copied the conversation from the other board below - sorry if it's all a
little wordy but some help would be much appreciated (my initial post at
bottom!!)

Thanks





Well, I don't envy you your task - I used to work in a college many
years ago !! <bg>

I suppose you could have some event macro that detects if there is a
change to column A, and would then transfer that change to the
appropriate cell of the remaining sheets. If you then entered a loop
(For Each sht ... ) you can have appropriate If statements within that
loop to avoid pasting to "earlier" sheets. Using this approach you
wouldn't need to link the sheets back to earlier sheets - just have
straight copies of the first sheet for the initial "days".

Other people might add to this post here, or you might get further
advice if you post in the .programming group.

Be prepared, though - once you end up with something that works you
will have to support those lecturers with appropriate training (and
hand-holding) <vbg>

Hope this helps.

Pete

 

[paulgu]

Terry,
Do you need to have all these later sheets? Why not, at the start of Thursday, copy Wednesday's sheet and call it Thursday? Or is there data on each sheet that is specific to that day and different from all other days?
Paul

 

[Dick Kusleika]

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