title: Copy content from closed work book column
body: <p>As said in the title I'm trying to copy data from a column into a new workbook as its a weekly report where the data I add in this column remains valid.</p>
  
  <pre><code>Sub copyColData00()
  
  Dim lastRow As Long
  
  Dim myApp As Excel.Application
  
  Dim wkBk As Workbook
  
  Dim wkSht As Object
  
  Dim mnt As String
  
  
  
  Set myApp = CreateObject("Excel.Application")
  
  mnt = InputBox("Enter Filename")
  
  Set wkBk = Workbooks.Open("\\n\Documents\" &amp; mnt &amp; ".xlsx")
  
  lastRow = wkBk.Sheets(1).Range("R" &amp; Rows.Count).End(xlUp).Row
  
  wkBk.Sheets(1).Range("R1:R" &amp; lastRow).Copy
  
  myApp.DisplayAlerts = False
  
  wkBk.Close
  
  myApp.Quit
  
  Set wkBk = Nothing
  
  Set myApp = Nothing
  
  Set wkBk = ActiveWorkbook
  
  Set wkSht = wkBk.Sheets("Sheet1")
  
  wkSht.Activate
  
  Range("R1").Select
  
  wkSht.Paste
  
  Exit Sub
  
  
  
  End Sub
  </code></pre>
  
  <p>My problem is that I want it to past it directly while there I get a prompt that ask if I want to copy all the data in the clip board or not and my second problem is that at
   Set wkSht = wkBk.Sheets("Sheet1")
  It gives me the error subscript out of range I've trouble understanding what happens there if someone could help it would be nice! </p>