Copied worksheets not referring to previous sheet

[joikd]

Using the following I've been able to generate copied sheets of "Thursday, 07-02-09" from 7/3/09 through 12/27/09:

Sub Copysheets()
Dim x As Integer
For x = 3 To 365
Worksheets("Thursday, 07-02-09").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(DateSerial(2008, 19, x), "DDDD, MM-DD-YY")
Next x
End Sub

I'm having two issues that I would appreciate any help with:

1. I'm trying to generate copied sheets through 6/30/10, but it errors out after 12/27/09 (although, it went into March once). What am I doing wrong?

2. Each sheet has a beginning balance (C3) that is taken from the previous day's ending balance (='Wednesday, 07-01-09'!N3). But, the generated copies all refer to the original sheet instead of the previous day's. Any ideas on a simple solution?

 

[Smitty]

Welcome to the Board!

For #1 that's a known error: http://support.microsoft.com/default.aspx?scid=kb;en-us;210684

For #2 this might be a start:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Copysheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> LastSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>        <SPAN style="color:#00007F">For</SPAN> x = 3 <SPAN style="color:#00007F">To</SPAN> 365<br>            Worksheets("Thursday, 07-02-09").Copy after:=Worksheets(Worksheets.Count)<br>            <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>                LastSheet = .Index - 1<br>                .Name = Format(DateSerial(2008, 19, x), "DDDD, MM-DD-YY")<br>                .Range("C3").Formula = "='" & Sheets(LastSheet).Name & "'!N3"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> x<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[joikd]

Worked perfectly! Thanks so much!