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

I'm new to VBA and am trying to automate the update in a workbook. I have a source workbook one and a destination workbook b both have a sheet named rollout summary I want the user to have this sheet Update to A and click the Update button that should run my macro. This macro should automatically updated opened sheet without workbook B Workbook B

I'm trying to get this code, but it does not work and gives me an error:

< Pre> workbook dim sht1 range dim wkb2 workbook dim sht2 limit set wkb1 = ActiveWorkbook set wkb2 = Workbooks.Open ("B.xlsx") set sht1 = wkb1.Worksheets ("Summary rollout") & lt As far as wkb1 dim; The error is coming here & gt; Set sht2 = wkb2.Sheets ("Summary Roll Out") sht1.Cells.Select Selection.Copy Windows ("B.xlsx") active sht2.Cells.Select Selection.Paste Special Paste: = XlPasteFormulasAndNumberFormats, Operation: = _xlNone, SkipBlanks: = False, moved: = false

sht1 and sht2 should be declared as a worksheet . It can be done to update the workbook without opening it, but a different approach will be required. . It's to make to see if you are not open workbook / off ScreenUpdating can turn

Try this:

  dim wkb1 workbook dim sht1 Worksheet dim wkb2 workbook dim sht2 Worksheet Application.ScreenUpdating = false set wkb1 = ThisWorkbook set wkb2 = Workbooks.Open ( "B.xlsx") sht1 = wkb1.Sheets ( "roll out summary") Set as set as Sht2 = wkb2.Sheets ("Roll Out Summary") sht1.Cells.Copy sht2.Range ("A1"). Paste Special xlPasteValues ​​Application.CutCopyMode = False wkb2.Close True Application.ScreenUpdating = True