Macro for copying sheets into book stops after one sheet

Question

I have a book in which to speed up the calculations (long story) I created a macro to copy three sheets to another file, and then another macro to copy them back.

The copy macro works fine, but the macro is copied back when stopped after copying on the same sheet.

I searched on StackOverflow and found some similar questions, but could not find the answer that worked. One post indicated that it was related to Office versions, and one related to the Shift key.

Here is the code:

Application.Calculation = xlCalculateManual Application.ScreenUpdating = False Application.DisplayAlerts = True Application.EnableEvents = False ' ' Set up the workbooks ' Set ThisWkb = ThisWorkbook Fname = Application.GetOpenFilename( _ fileFilter:="Excel Macro Files, *.xlsm", _ Title:="Select the Storage File", _ MultiSelect:=False) Set StorageWbk = Workbooks.Open(Fname) ' MsgBox ("Beginning process - please click ok to any macro warning - you will see a confirmation when complete") StorageWbk.Sheets("Sh A").Copy After:=ThisWkb.Sheets(ThisWkb.Sheets.Count) StorageWbk.Sheets("Sh B").Copy After:=ThisWkb.Sheets(ThisWkb.Sheets.Count) StorageWbk.Sheets("Sh C").Copy After:=ThisWkb.Sheets(ThisWkb.Sheets.Count) StorageWbk.Close 

Sometimes I find that if I delete a new sheet and run the macro again, it sometimes works and reads all three sheets, but sometimes it also doesn’t.

Any help is greatly appreciated.

Answer 1

Quoting both YowE3K and nbayly to give the correct answer from the comments directly here (I should have looked for it):

Show hidden sheets and copy three links at once using:

 StorageWbk.Sheets(Array("Sh A", "Sh B", "Sh C")).Copy After:=ThisWkb.Sheets(ThisWkb.Sheets.Count)