CODEWITHSUNDEEP
excelvba
nthpl888
nthpl888

Reputation: 3

Copy a sheet to an open workbook without leaving the current workbook?

I'm new to VBA code. So far I have successfully managed to copy the sheet I want to another open workbook. How can I do this without leaving the current workbook?

I've dug around for answers and tried Application.ScreenUpdating = False but that doesn't seem to have any effect, perhaps I'm not using this correctly?

Here's what I've got so far, its fairly simple but it works so far as copying the sheet.

Sub Sample1()
Application.ScreenUpdating = False
Workbooks("Workbook1.xlsm").Sheets("Sheet1").CopyAfter:=Workbooks("Workbook2.xlsm").Sheets("Copy_After")
Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 83

Answers (1)

sgp667
sgp667

Reputation: 1875

All you have to do is record which sheet was the ActiveSheet on the begining of your script and Activate it once your script finishes.

Sub Sample1()
  Dim OriginalSheet as Worksheet

  Application.ScreenUpdating = False

  OriginalSheet = ActiveSheet
  Workbooks("Workbook1.xlsm").Sheets("Sheet1").CopyAfter:=Workbooks("Workbook2.xlsm").Sheets("Copy_After")

  OriginalSheet.Activate

  Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions