copy worksheet problem

Aries

New Member
Joined
Mar 19, 2002
Messages
26
After working on a workbook with several worksheets, I want to copy one of them to a new workbook by a macro. I tried the code

Worksheets("Sheet1").Copy

but it doesn't work and I got a error message from vba (error 75)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
Hi Aries,

Try this one, it works for me!!

ThisWorkbook.Sheets("sheet1").Copy Before:=Workbooks("XYZ.XLS").Sheets(1)
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Aries:

Try ...

Code:
Sub CopySheetToNewBook()
Sheets("Sheet8").Copy
ActiveWorkbook.SaveAs "C:MrExcelNewBook-Oct06" & ".xls"
ActiveWorkbook.Close
End Sub

I have copied sheet8 and saved the file as NewBook-Oct06.xls in MrExcel folder of C: drive -- please adjust the file path as you want.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-07 00:27
 

Aries

New Member
Joined
Mar 19, 2002
Messages
26
On 2002-10-07 00:25, Yogi Anand wrote:
Hi Aries:

Try ...

Code:
Sub CopySheetToNewBook()
Sheets("Sheet8").Copy
ActiveWorkbook.SaveAs "C:MrExcelNewBook-Oct06" & ".xls"
ActiveWorkbook.Close
End Sub

I have copied sheet8 and saved the file as NewBook-Oct06.xls in MrExcel folder of C: drive -- please adjust the file path as you want.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-07 00:27

Thank you Andy and Yogi.
Andy's code doesn't work for me. I find that my code works sometimes and fails sometimes. When my codes fails, Andy's code doesn't work either.
Yogi's code works but I have to assign the output workbook in the codes and it must be saved first. Can I just make new workbook without saving it?
The most frustrating thing is that I don't know what's going wrong with my macro.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Aries:

Your code should work -- are you sure that sheet1 does exist in your workbook from where you are executing the procedure.

for me the following code does work ...

Code:
Sub CopySheetToNewBook()
 Worksheets("Sheet1").Copy
End Sub

Execution of the code created a new workbook for me with only sheet1 as the only worksheet.

You may want to check your actual code as written in the module for any errors that may not be obvious.

Regards!

Yogi
 

Aries

New Member
Joined
Mar 19, 2002
Messages
26
On 2002-10-07 16:41, Yogi Anand wrote:
Hi Aries:

Your code should work -- are you sure that sheet1 does exist in your workbook from where you are executing the procedure.

for me the following code does work ...

Code:
Sub CopySheetToNewBook()
 Worksheets("Sheet1").Copy
End Sub

Execution of the code created a new workbook for me with only sheet1 as the only worksheet.

You may want to check your actual code as written in the module for any errors that may not be obvious.

Regards!

Yogi

Yes, my code works, but not always. I have the experience that my macro works perfectly in testing and it fails at that line one hour later when I test it again on the same computer. I programed the workbook so that I cannot be saved so there should be no difference in the workbook between two testings.
 

Forum statistics

Threads
1,144,510
Messages
5,724,782
Members
422,578
Latest member
annsalinas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top