vba storing a value to paste from a non active window

Question

I have two workbooks. I've automated the code to set different filters and then measure the subtotal in wb2 then take that value and paste it into an 8x11 table in wb1, however it takes ages as I'm using.

Windows("wb2name").Activate
Sheets("sheet2").Select
 ' do the filtering and copy subtotal value
 Windows("wb1name").Activate
 Sheets("sheet1").Range("A1").Paste 

I know its better to try and not use Activate and Select so I was thinking it would be quicker if I did the code below it would work faster. The problem is it isn't storing x=subtotal properly. How do I fix this?

Dim wb1 As Workbook
Dim wb1sh As Worksheet

Set wb1 = ActiveWorkbook
Set wk1sh = wb1.Sheets("sheet1")
Set wb2 = "data file.xlsx"
Set wk2sh = wb1.Sheets("sheet1")

'Then I do the filter selecting which works fine via:

x= wb2sh.Application.WorksheetFunction.Subtotal(3,Range("A1:A100")) 

wb1sh.Range("A1")=x

End Sub

Answer 1

Try this, your syntax was slightly off and I think you were referencing wrong sheets. You should turn on Option Explicit which would have alerted you to wrong variable names.

Dim wb1 As Workbook, wb2 as workbook
Dim wk1sh As Worksheet, wk2sh as worksheet, x

Set wb1 = ActiveWorkbook
Set wk1sh = wb1.Sheets("sheet1")
Set wb2 = workbooks("data file.xlsx")
Set wk2sh = wb2.Sheets("sheet1")

'Then I do the filter selecting wich works fine via:

x= Application.WorksheetFunction.Subtotal(3,wk2sh.Range("A1:A100")) 

wk1sh.Range("A1")=x

End Sub