How do I open a worksheet in vba?

Question

Hilariously the following code only works if the worksheet is actually selected in the excel window. I really want to finish this macro soon but can't seem to work out how to select a specific worksheet so it is open in excel? Many thanks if someone knows how. I have to use range and so on.

sheet.Range(Cells(firstRow, 2).Address(False, False), Cells(lastRow, 50)).Select
With Selection
    .Copy
End With
sheet.Range(Cells(firstRow, 3).Address(False, False), Cells(lastRow, 51)).Select
With Selection
    .PasteSpecial xlPasteValuesAndNumberFormats
End With

Answer 1

You can activate the worksheet by name or by 1-based index (the number -- 1st workbook, 2nd, and so on). The syntax is the same, either way.

This will activate the 3rd worksheet:

ActiveWorkbook.Sheets(3).Activate

This will activate the worksheet named stats:

ActiveWorkbook.Sheets("stats").Activate

Of course, you don't have to actually make the worksheet selected in the Excel window to work with it. Your code uses a variable called sheet, which I assume you've assigned to the active worksheet. Instead of doing that, you can set sheet = ActiveWorkbook.Sheets("stats"), and then work with the sheet even if is not in view.

Answer 2

Workbooks(x).Worksheets(x).Activate ?