Home>

In Excel, the process to copy the sheet (let's call it sheet a) with the currently open workbook (let's call it workbook A) to the end of other workbook B is written in VBA.

However, I get the error "The index is not in the valid range."

[Code ①]

Dim wb As Workbook
Set wb = Workbooks.Open(ThisWorkbook.Path&"\B.xlsx")
With wb
    'Copy sheet a to the end of book B [*]
    Sheets(a).Copy After:=.Sheets(.Sheets.Count)
    'Overwrite and save
    .Close SaveChanges:=True
End With

In case of code ①, an error will occur at [※].

Did you make a mistake in specifying the sheet value? I thought, but the number of sheets is correct, so I don't know the cause.

https://www.sejuku.net/blog/70165

I tried changing the code to ② or ③ with reference to these sites, but the same error still occurs at [※] in both cases.

[Code ②]

Dim wb As Workbook
Set wb = Workbooks(ThisWorkbook.Path&"\B.xlsx")'[*]
With wb
    'Copy sheet a to the end of book B [*]
    Sheets(a).Copy After:=.Sheets(.Sheets.Count)
    'Overwrite and save
    .Close SaveChanges:=True
End With

[Code ③]

Dim wb As Workbook
Workbooks.Open(ThisWorkbook.Path&"\B.xlsx")
Set wb = ActiveWorkbook
With wb
    'Copy sheet a to the end of book B [*]
    Sheets(a).Copy After:=.Sheets(.Sheets.Count)
    'Overwrite and save
    .Close SaveChanges:=True
End With

I may have overlooked something, but I do not know the cause, so I posted it here.

If i am familiar with this, please tell us.