title: Excel 2010: Subscript out of range error
body: <p>The error that I am receiving is Run-time error '9': Subscript out of range.</p>
  
  <pre><code>Sub Workbook_Open()
  
  'Turn off any alerts that maybe displayed.
  Application.DisplayAlerts = False
  'Turn of the screen updates
  Application.ScreenUpdating = False
  
  'Declare the workbook, create it, save it and close it
  Dim wk As Workbook
  Set wk = Workbooks.Add
  wk.SaveAs Filename:="C:\Saved File\KPI_Grid.xlsm", FileFormat:=52, Password:="", WriteResPassword:="", _
  ReadOnlyRecommended:=False, CreateBackup:=False
  wk.Close
  
  'Open the workbook again. This will get rid of the 'Compatibilty View' and then activate the orginal workbook
  Application.Workbooks.Open Filename:="C:\Saved File\KPI_Grid.xlsm"
  Workbooks("KPI Grid V5K1 - macro testing.xlsm").Activate
  Worksheets("Weekly").Activate
  'Select all cells and copy them
  Cells.Select
  Selection.Copy
  'Activate the workbook and sheet that we are going to paste into.
  Workbooks("KPI_Grid.xlsm").Activate
  Worksheets("Sheet1").Activate   ' ******************ERROR HERE ******************
  Cells.Select
  Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
      , SkipBlanks:=False, Transpose:=False
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  'Activate the previous workbook again.
  Workbooks("KPI Grid V5k1 - macro testing.xlsm").Activate
  Worksheets("Monthly").Activate
  Cells.Select
  Cells.Copy
  Workbooks("KPI_Grid.xlsm").Activate
  Worksheets("Sheet2").Activate
  Cells.Select
  Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
      , SkipBlanks:=False, Transpose:=False
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  </code></pre>
  
  <p>End Sub</p>
  
  <p>I thought this was a relatively simple task.<br>
  The aim is to create a new workbook, copy the values of two sheets over and then save and close the new workbook.<br>
  Why does this code error?</p>