Excel Macro Help

Shezan01

New Member
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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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

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

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.

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

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.

Re: Copy Sheet and rename tabs by week number

Modified Bob's. See if this works for you.
Code:
``````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.

Replies
2
Views
101
Replies
3
Views
118
Replies
12
Views
327
Replies
3
Views
229
Replies
4
Views
153

1,203,674
Messages
6,056,681
Members
444,882
Latest member
cboyce44

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.

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

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