Hi,

I have been trying to Copy a worksheet into another workbook and rename it using macro. But while running it Gives me an error:
Run-time error '40036': Application-defined or object-defined error.

Any help will be appreciated.

Sub Submit_Click()

LastSheetName = "Sheet1"
OutSheet = "OutputSheet"
OrgFileName = "Book1.xls"
NameWorkbook = "Book2.xls"

Workbooks(OrgFileName).Activate
Workbooks(OrgFileName).Worksheets(OutSheet).Visible = True
Workbooks(OrgFileName).Sheets(OutSheet).Activate
Workbooks(OrgFileName).Sheets(OutSheet).Select
Workbooks(OrgFileName).Sheets(OutSheet).Copy After:=Workbooks(NameWorkbook).Sheets(LastSheetName)
Sheets(OutSheet).Name = "123"

End Sub

Hi,

U r getting error while copying or renaming??
if renameing then :
First Select it and then rename it:

Sheets(OutSheet).Select
Sheets(OutSheet).Name = "NewSheet"
ActiveWorkbook.Save

Regards
Veena

Hi,

Your code is OK but make sure that workbook1 has a sheet named "OutputSheet" and workbook2 is open and contains a sheet called sheet1, else nothing is wrong with your code. Any way on which statment is your system failing.

Hi,

on the following Statement it is giving the errror:

Sheets(OutSheet).Name = "123"

The workbook1(Book1.xls) has a sheet named "OutputSheet" and workbook2(Book2.xls) is open and contains a sheet called sheet1.

Hi,

try giving this before renaming code:

Workbooks(NameWorkbook).Activate
Sheets(OutSheet).Select
Sheets(OutSheet).Name = "123"

Regards
Veena

This article has been dead for over six months. Start a new discussion instead.