Solved: For, Each, Next Loop with a Skip

[tammyl]

Hi,
I'm trying to create a new worksheet for every week within a date range.
I've been trying to get a 'for each, next' loop to step by 7 days but can't seem to get it to work.

Date range in column A. Eg: 1/1/2010 - 31/12/2010 (range also used for other code.
I then select a start date & end date range to create the weekly spreadsheets for a few months at a time.
So far i'm reading the date and trying to do a step + 7 but this isn't working because my vba code is reading every record in the range.
Eg: First wsheet trigger date 1/1/2010, second wsheet trigger date +7 days = 8/1/2010
Current code as below
[VBA]
r = 1
For Each Row In rng
If Row.Value >= Sdate And Row.Value <= Edate Then
Set wksNew = Worksheets("Weekly").Copy(After:=Worksheets(Worksheets.Count))
End If
r = r + 7
Next
[/VBA]

Hope somebody can assist.

Cheers
tammyl

[GTO]

Greetings Tammy,

I am guessing that 'Row' is not a Row, but a cell in your range 'rng', and that rng is vertical (a partial column)?

Least for me, it is hard to tell what we're trying to do with 'r', without seeing the dates etc.

Could you attach a small example workbook and explain what you are wanting to do?

Thanks,

Mark

[Bob Phillips]

[vba]

r = 1
Do Until rng.Cells(r, 1).Value >= Sdate

r = r + 1
Loop
Do While rng.Cells(r, 1).Value <= Edate
Worksheets("Weekly").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(rng.Cells(r, 1).Value, "dd-mmm-yyyy")
r = r + 7
Loop
[/vba]

[tammyl]

Thankyou very much, that worked perfectly. Had not used Do/Loop before.

Cheers

Have a good New Year's....