How to Automatically Copy Between Sheets in the same Workbook

[ianhows]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Please could someone advise me of the best way to do the following;<o></o>
<o></o>
I have a workbook with sheets named Week01, Week02, Week03, ..........., Week 52. <o></o>
<o></o>
I want to know if it is possible to copy all the information from cells C3:C50 from Week01 into the same place of Week02 and then lock all the cells in Week01. Then the following week, copy all the information from cells C3:C50 from Week02 into the same place of Week03 and then lock all the cells in Week02. ect............ <o></o>
This could either be user initiated or if possible at a predefined time each week.<o></o>
<o></o>
I think i know of a way to do this if I was to write 52 individual macros but i am unsure if this could be automatic from the time and date???<o></o>
<o></o>
Any help is much appreciated.
Ian

 

[VoG]

Hello and welcome to MrExcel.

Not Automatic but try

Sub test()
With Sheets("Week" & Format(WorksheetFunction.WeekNum(Date), "00"))
    .Range("C3:C50").Copy Destination:=Sheets("Week" & Format(WorksheetFunction.WeekNum(Date) + 1, "00")).Range("C3")
    .Range("C3:C50").Locked = True
End With
End Sub

This requires the Analysis Toolpak - add via Tools > Add-Ins.

 

[Smitty]

Welcome to the Board!

Try recording a macro doing what you want, then we can refine it for you from there.

This may be a start for you:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>        <SPAN style="color:#00007F">With</SPAN> .Range("C3:C50")<br>            .Copy ActiveSheet.Next.Range("C3")<br>            .Locked = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .Protect "PasswordHere"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

It will copy C3:C50 from the active sheet to the next one to the right, but will do so without regard to sheet names, then lock C3:C50 and protect the sheet. You can easily assign it to a Forms control button on each sheet.

Hope that helps,