Copy sheet from one WB to another using VBA without opening destination WB

Question

I'm new to VBA and trying to automate updates to a workbook. I have a source Workbook A and a destination Workbook B. Both have a sheet called roll out summary. I want the user to update this sheet in A and click update button which should run my macro. This macro should automatically update the sheet in workbook B without opening Workbook B.

I'm trying this code but it doesn't work and gives me an error:

Dim wkb1 As Workbook
Dim sht1 As Range
Dim wkb2 As Workbook
Dim sht2 As Range

Set wkb1 = ActiveWorkbook
Set wkb2 = Workbooks.Open("B.xlsx")
Set sht1 = wkb1.Worksheets("Roll Out Summary") <Getting error here>
Set sht2 = wkb2.Sheets("Roll Out Summary")

sht1.Cells.Select
Selection.Copy
Windows("B.xlsx").Activate
sht2.Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Answer 1

sht1 and sht2 should be declare as Worksheet. As for updating the workbook without opening it, it can be done but a different approach will be needed. To make it look like you're not opening the workbook, you can turn ScreenUpdating on/off.

Try this:

Dim wkb1 As Workbook
Dim sht1 As Worksheet
Dim wkb2 As Workbook
Dim sht2 As Worksheet

Application.ScreenUpdating = False

Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks.Open("B.xlsx")
Set sht1 = wkb1.Sheets("Roll Out Summary")
Set sht2 = wkb2.Sheets("Roll Out Summary")

sht1.Cells.Copy
sht2.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
wkb2.Close True

Application.ScreenUpdating = True