title: Excel vba code for updating workbook from external worksheet
body: <p>Yet another worksheet copying problem! This is a simple problem that has got me stumped. I want the click of a command button (in action.xlsm) to repopulate the values in a range ("stock" - 2 cols &amp; maybe 100 rows - this is the master inventory records) in a separate excel file (inventory.xlsx), from a named range ("newInventory" - same size as other named range) in the active worksheet (in action.xlsm) that has had the original "stock" values reduced by the values of items taken out of stock. The calculations are OK I just can't get the master inventory file to update. I have checked heaps of forums and tried two approaches to no avail.  I have tried:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  Dim InventoryFileName As String
  InventoryFileName = "C:\Users\david\Documents\inventory.xlsx"
  Workbooks(InventoryFileName).Worksheets("Sheet1").Range("stock") = ThisWorkbook.Worksheets("inventory").Range("newInventory").Value
  Workbooks(InventoryFileName).Save
  End Sub 
  </code></pre>
  
  <p>Throws a "Run-time error '9': Subscript out of range" on line 4.  I have also tried:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  Dim wbTarget As Workbook 'workbook where the data is to be pasted
  Dim wsTarget As Worksheet
  Dim wbThis   As Workbook 'workbook from where the data is to copied
  Dim wsThis As Worksheet
  Dim strName  As String   'name of the source sheet/ target workbook
  
  'set to the current active workbook (the source book)
  Set wbThis = ActiveWorkbook
  Set wsThis = ActiveSheet
  
  'get the active sheetname of the book
  strName = wsThis.Name
  
  'open a workbook that has same name as the sheet name
  Set wbTarget = Workbooks.Open("C:\Users\david\Documents\" &amp; strName &amp; ".xlsx")
  Set wsTarget = wbTarget.Worksheets("Sheet1")
  
  'select cell A1 on the target book
  wbTarget.wsTarget.Range("A1").Select
  
  'clear existing values form target book
  wbTarget.wsTarget.Range("A1:B10").ClearContents
  
  'activate the source book
  wbThis.Activate
  
  'clear any thing on clipboard to maximize available memory
  Application.CutCopyMode = False
  
  'copy the range from source book
  wbThis.wsThis.Range("A1:B10").Copy
  
  'paste the data on the target book
  wbTarget.wsTarget.Range("A1").PasteSpecial Paste:=xlPasteValues
  
  'clear any thing on clipboard to maximize available memory
  Application.CutCopyMode = False
  
  'save the target book
  wbTarget.Save
  
  'close the workbook
  wbTarget.Close
  
  'activate the source book again
  wbThis.Activate
  
  'clear memory
  Set wbTarget = Nothing
  Set wbThis = Nothing
  
  End Sub
  </code></pre>
  
  <p>This throws a "Run-time error '438': Object doesn't support this property or method" on line <code>wbTarget.wsTarget.Range("A1").Select</code></p>
  
  <p>What have I got wrong? Any suggestions?</p>