Excel Macro Help

[Shezan01]

Joe4,

This is exactly the macro I need, but I also need to have the dates change on each week. So I have a worksheet with 7 dates. Cell c3=12/31, d3=1/1, e3=1/2 etc...i3=1/6
This is on week 1 worksheet, so now I need the dates to change on week 2 sheet (c3=1/7, d3=1/8 etc..)
So I need to copy the main sheet that I have 52 times for 52 weeks and have the dates change for each week.

Much appreciated

 

[Shezan01]

Greetings.
I have a workbook with two worksheets.
Sheet 1 is named "Master". Sheet 2 is named "Week 1".
I would like to copy "Week 1" 51 times (to total 52) and rename each of the new copies "Week 2", Week 3", "Week 4", etc. to end with "Week 52".
I also need to have the dates change on each week. So I have a worksheet with 7 dates. Cell c3=12/31, d3=1/1, e3=1/2 etc...i3=1/6
This is on week 1 worksheet, so now I need the dates to change on week 2 sheet (c3=1/7, d3=1/8 etc..)
So I need to copy the main sheet that I have 52 times for 52 weeks and have the dates change for each week. Also how can I make all the sheets protected without going to each sheet and protecting them 1 by 1.

How Can I do this?

Much appreciated

 

[BobUmlas]

Sub OneAnswer()
For i = 2 To 52
Sheets("Week 1").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Week " & i
For j = 1 To 7
Cells(j + 2, 3).Value = Sheets("Week " & i - 1).Cells(j + 2, 3).Value + 7
Next
Next
End Sub

 

[Joe4]

Re: Copy Sheet and rename tabs by week number

This is exactly the macro I need, but I also need to have the dates change on each week. So I have a worksheet with 7 dates. Cell c3=12/31, d3=1/1, e3=1/2 etc...i3=1/6
This is on week 1 worksheet, so now I need the dates to change on week 2 sheet (c3=1/7, d3=1/8 etc..)
So I need to copy the main sheet that I have 52 times for 52 weeks and have the dates change for each week.

I have been away for a few days, so did not see this. But I see you re-posted the question in a new thread and got an answer.

 

[Shezan01]

Re: Copy Sheet and rename tabs by week number

Yes I did get a reply, but it only helps me with creating 52 sheets, but the date macro part is not working. My dates are in the column d3, e3, f3, g3, h3, i3 and j3. Starting from this year d3 is 12/31. e3 is 1/1. Now how can I get the dates to change automatically is each of the 52 sheets made (week 1 to 52) so I dont have to go into each sheet and input the date. There has to be some date macro that uses the calendar because not all months have 31 days.

Thanks again

 

[Joe4]

Re: Copy Sheet and rename tabs by week number

Posts 1, 4, and 5 moved from thread: http://www.mrexcel.com/forum/excel-questions/651931-copy-sheet-rename-tabs-week-number.html

I merged your posts with this other thread. You do not want two different posts/threads going on for the same question. It can be a bit confusing (note rule #9 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).

Also, a little posting tip. If Bob's answer didn't not satisfy your question completely, reply back letting him know what you still need. He is trying to help you, so give him the chance to finish the job. I have been away for a a few days and trying to catch up on a bunch of work, so have not had any time to look at the code I wrote to help that other person last year, and see how to adapt it for you. If when things settle down for me within the next day or two, if you still do not have a satisfactory answer, I'll see what I can do.

 

[rjwebgraphix]

Re: Copy Sheet and rename tabs by week number

Modified Bob's. See if this works for you.

Sub OneAnswer()

For i = 2 To 52
    Sheets("Week 1").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Week " & i
    For j = 1 To 7
        Cells(3, j + 2).Value = Sheets("Week " & i - 1).Cells(3, j + 2).Value + 7
    Next
Next
End Sub

I just switched the column and row references. Should work.