PDA

View Full Version : Solved: For, Each, Next Loop with a Skip

tammyl
12-29-2009, 11:39 PM
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

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

Hope somebody can assist.

Cheers
tammyl

GTO
12-29-2009, 11:49 PM
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

xld
12-30-2009, 01:39 AM
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

tammyl
12-30-2009, 05:47 PM
Thankyou very much, that worked perfectly. Had not used Do/Loop before.

Cheers

Have a good New Year's....