Copy cells to active worksheet from a defined RANGE of SHEETS

Question

I need to copy a particular cell value to the current activesheet from worksheets between a predefined worksheet range. For example only the sheets between Sheet5 and Sheet7. I cannot specify the worksheet names as they do not yet exist. They will be created by the user via another macro upon request and placed within this predefined worksheet range upon creation.

In essence, I think I need to somehow define a Workbook Range between Sheet5 and Sheet7. Coincidentally, the new sheets will ALWAYS be within the sheet range of sheet 5 to 7.

Davesexcel Davesexcel · Accepted Answer · 2016-02-10T11:48:30

You can use the sheet index such as

    Sub SheetIndx()
    If Sheets.Count >= 7 Then
        ActiveSheet.Range("A1:B4").Copy Sheets(6).Cells(Sheets(6).Rows.Count, "A").End(xlUp).Offset(1)
    End If
End Sub

Or the actual sheet6, regardless of that the name of the sheet is, it is still sheet6 and doesn't matter what order it is in the workbook.

Sub Sheet()
    If Sheets.Count >= 7 Then
        ActiveSheet.Range("A1:B4").Copy Sheet6.Cells(Sheet6.Rows.Count, "A").End(xlUp).Offset(1)
    End If
End Sub

Copy cells to active worksheet from a defined RANGE of SHEETS

1 Answers