Copy worksheet to another workbook and rename it

[Probleminfinity]

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.

[kb.net]

Hi,

Please post your code in order to be able to help you.

[Probleminfinity]

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

[QVeen72]

Hi,

are u trying to change Source Sheet or Destination Sheet...?

Regards
Veena

[Probleminfinity]

Destination Sheet

[QVeen72]

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

[kb.net]

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.

[Probleminfinity]

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.

[QVeen72]

Hi,

try giving this before renaming code:

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

Regards
Veena

[Probleminfinity]

Thanks its working now.
The error I was getting was due to the other macros written.