Loop through a set number of sheets starting with inputted initial sheet name

[Declanscully]

I am trying to set the following piece of code to be able to loop through a set of sheets starting with Date1 (inputted from active worksheet), and continuing for the duration of the Result. An example would be Date1: 03-03-2022, Result: 8, For statement would start on the workbook at 03-03-2022 and copy the date for the next 8 sheets.

Date1 = Worksheets("sheet1").Range("B2").value
Date2 = Worksheets("sheet1").Range("B3").value
Result = DateDiff("D", Date1, Date2)

For i = Date1 To Result 'Starts workbook search after rate sheets
        lr = lr + 1 'sets start row as 5
        With ThisWorkbook.Worksheets("Sheet1").Range("A" & lr)    '<----- Change as required
            .value = cStr(wb2.Worksheets(i).Name)
            .Offset(, 1).value = wb2.Worksheets(i).Range("J61").MergeArea.value
            .Offset(, 2).value = wb2.Worksheets(i).Range("J27").MergeArea.value
            .Offset(, 4).value = wb2.Worksheets(i).Range("J39").MergeArea.value
            .Offset(, 6).value = wb2.Worksheets(i).Range("J50").MergeArea.value
            .Offset(, 7).value = wb2.Worksheets(i).Range("J60").MergeArea.value
            .Offset(, 8).value = wb2.Worksheets(i).Range("B15").MergeArea.value
            .Offset(, 9).value = wb2.Worksheets(i).Range("B16").MergeArea.value
            .Offset(, 10).value = wb2.Worksheets(i).Range("B17").MergeArea.value
            .Offset(, 11).value = wb2.Worksheets(i).Range("B18").MergeArea.value
            .Offset(, 12).value = wb2.Worksheets(i).Range("B19").MergeArea.value
            .Offset(, 13).value = wb2.Worksheets(i).Range("B20").MergeArea.value
            
            End With
    Next i

Loop through a set number of sheets starting with inputted initial sheet name

I am trying to set the following piece of code to be able to loop through a set of sheets starting with Date1 (inputted from active worksheet), and continuing for the duration of the Result. An example would be Date1: 03-03-2022, Result: 8, For statement would start on the workbook at 03-03-2022...

www.excelforum.com

 

[Anbuselvam]

the loop starts from 0 because it's more convenient to work with indexes starting from 0. The Format(Date1 + i, "dd-mm-yyyy") expression generates the date string for the current sheet, based on the start date (Date1) and the current index (i). Finally, the wb2.Worksheets("Date" & (i + 1)) expression select the worksheet with the name "DateX", where X is the current index plus 1

Date1 = Worksheets("Sheet1").Range("B2").Value
Date2 = Worksheets("Sheet1").Range("B3").Value
Result = DateDiff("D", Date1, Date2)

For i = 0 To Result - 1 'Starts workbook search after rate sheets
    lr = lr + 1 'sets start row as 5
    With ThisWorkbook.Worksheets("Sheet1").Range("A" & lr) '<----- Change as required
        .Value = Format(Date1 + i, "dd-mm-yyyy")
        .Offset(, 1).Value = wb2.Worksheets("Date" & (i + 1)).Range("J61").MergeArea.Value
        .Offset(, 2).Value = wb2.Worksheets("Date" & (i + 1)).Range("J27").MergeArea.Value
        .Offset(, 4).Value = wb2.Worksheets("Date" & (i + 1)).Range("J39").MergeArea.Value
        .Offset(, 6).Value = wb2.Worksheets("Date" & (i + 1)).Range("J50").MergeArea.Value
        .Offset(, 7).Value = wb2.Worksheets("Date" & (i + 1)).Range("J60").MergeArea.Value
        .Offset(, 8).Value = wb2.Worksheets("Date" & (i + 1)).Range("B15").MergeArea.Value
        .Offset(, 9).Value = wb2.Worksheets("Date" & (i + 1)).Range("B16").MergeArea.Value
        .Offset(, 10).Value = wb2.Worksheets("Date" & (i + 1)).Range("B17").MergeArea.Value
        .Offset(, 11).Value = wb2.Worksheets("Date" & (i + 1)).Range("B18").MergeArea.Value
        .Offset(, 12).Value = wb2.Worksheets("Date" & (i + 1)).Range("B19").MergeArea.Value
        .Offset(, 13).Value = wb2.Worksheets("Date" & (i + 1)).Range("B20").MergeArea.Value
    End With
Next i