VBA doesn't copy last sheet in Sheet.Copy command

[Matt_O]

Hi,

Does anyone know why the VBA code to copy all sheets from one workbook to another neglects the last sheet? I've tried the code on various workbooks in case the last sheet somehow is corrupt or named weirdly (which likely should not matter).

I've found variations of this code all over the web and from what I can tell the code below faithfully re-purposed/appropriated.

'GetTargetName' is a variable pulled when opening the workbook with the sheets I need to copy.

The 'Master' book contains one sheet which has control buttons to run various subroutines. I'm wondering if there's a correlation between the number of sheets I'm trying to copy and the number of sheets in the Master (which is just one) as the last sheet from the GetTargetName workook isn't copied. For example, when I expect 6 sheets to be copied to the Master I only get the first 5. It's always the last sheet that doesn't get copied.

Dim wb1 As Workbook
Set wb1 = Workbooks(GetTargetName)
Dim wb2 As Workbook
Set wb2 = Workbooks("Master.xlsm")


For Each Sheet In wb1.Sheets
Sheet.Copy after:=wb2.Sheets(wb2.Sheets.Count)
Next Sheet

Thanks in advance.

Matt

 

[JLGWhiz]

try this and see if it makes a difference.

Dim i As Long
 For i = 1 To wb1.Sheets.Count
     wb1.Sheets(i).Copy After:=wb2.Sheets(wb2.Sheets.Count)
 Next

 

[Matt_O]

Hi JLGWhiz,

Thanks for replying.

Unfortunately I was still getting the same result on any workbook I tested. I combed through the code looking for any type of 'delete' request and found the culprit.

I was trying to ensure that if a sheet called 'Index' was copied over it got deleted.

Code:
On Error Resume Next

Sheets("Index").Select
ActiveWindow.SelectedSheets.Delete

On Error GoTo 0

Since there wasn't a sheet named 'Index' I guess the last visible sheet was the last one copied over and it was deleted.

Thanks again for the prompt reply. It was much appreciated.

Matt

 

[JLGWhiz]

Hi JLGWhiz,

Thanks for replying.

Unfortunately I was still getting the same result on any workbook I tested. I combed through the code looking for any type of 'delete' request and found the culprit.

I was trying to ensure that if a sheet called 'Index' was copied over it got deleted.

Code:
On Error Resume Next

Sheets("Index").Select
ActiveWindow.SelectedSheets.Delete

On Error GoTo 0

Since there wasn't a sheet named 'Index' I guess the last visible sheet was the last one copied over and it was deleted.

Thanks again for the prompt reply. It was much appreciated.

Matt

Yep, that would do it. The copy of the copied sheet is the active sheet until the focus is transfered by another action. So whether the sheet is named Index or not, the way the code is written, it would delete the active sheet. You would need to use something like

Application.DisplayAlerts = False
If ActiveSheet.Name = "Index" Then
    ActiveSheet.Delete
End If
Application.DisplayAlerts = True

To prevent the deletion When 'Index' doesn't exist. Or another way

Application.DisplayAlerts = False
Sheets("Index").Delete
Application.DisplayAlerts = True

 

[Matt_O]

Thanks for the advise JLGWhiz!

Matt

 

[JLGWhiz]

Thanks for the advise JLGWhiz!

Matt

You're welcome,
regards, JLG