How to Automatically Copy Between Sheets in the same Workbook

ianhows

New Member
Joined
Mar 8, 2009
Messages
1
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Please could someone advise me of the best way to do the following;<o:p></o:p>
<o:p></o:p>
I have a workbook with sheets named Week01, Week02, Week03, ..........., Week 52. <o:p></o:p>
<o:p></o:p>
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:p></o:p>
This could either be user initiated or if possible at a predefined time each week.<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Any help is much appreciated.
Ian
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

Not Automatic but try

Code:
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.
 
Upvote 0

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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,
 
Upvote 0

Forum statistics

Threads
1,195,644
Messages
6,010,896
Members
441,571
Latest member
stolenweasel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top