Excel vba code for updating workbook from external worksheet

Question

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 & 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:

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 

Throws a "Run-time error '9': Subscript out of range" on line 4. I have also tried:

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\" & strName & ".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

This throws a "Run-time error '438': Object doesn't support this property or method" on line wbTarget.wsTarget.Range("A1").Select

What have I got wrong? Any suggestions?

Solution

Replace

wbTarget.wsTarget.Range("A1").Select

with just

wsTarget.Range("A1").Select

The workbook is already implied from the way you defined wsTarget. I suspect that will do it. If you run the code in the debugger, then when you do a "watch" on the variable you can see exactly what does and doesn't work..

Comments

[Floris]

Replace it (wbTarget.wsTarget.Range("A1").Select with just wsTarget.Range("A1").Select - the workbook is already implied from the way you defined wsTarget. I suspect that will do it. If you run the code in the debugger, then when you do a "watch" on the variable you can see exactly what does and doesn't work...

[Floris]

As for your first method - try opening the workbook with the full path first, then reference it by just the name inventory.xlsx (not the whole C:\... thing.

[Admin]

Thanks Floris, that did it! I couldn't get the first method to work after fiddling around a bit. But second method with your correct reference throughout the code is the solution.