Trying to copy an entire worksheet into a new worksheet in Excel 2010

Not sure why this is not working below. I simply want to copy the worksheet "Send" from an open workbook and create another separate workbook with the copy with a date identifier in the name. I am getting a Run-time error '91' on the last line "Object variable or With block variable not set" and it is creating another worksheet in my workbook (Send2) in error. Cannot figure this out. Can anyone help me? Thx.

Please Login or Register  to view this content.

You have to assign a workbook object to variable wb1 by using Set statement.

How would you assign a workbook object to variable wb1 by using Set statement?

Originally Posted by TMS

Oh, and then, probably ActiveWorkbook.SaveAs

But then it would save the whole workbook wouldn't it? I just want to save what is on the sheet.

Ok, what would the whole code look like?

Ok, thx. Here is what happens. I am running Microsoft Excel 2010 64 Bit, Windows 7 64 Bit.

First, I get the following notification in a box titled "Microsoft Excel - Compatibility Checker":

"The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in the currently selected file format. Click Continue to save the workbook anyway. To keep all of your features, click Cancel and then save the file in one of the new file formats."

And then below in the box under a title called Summary I get:

"This workbook contains data in cells outside of the row and column limit of the selected file format. Data beyond 256 (IV) columns by 65,536 rows will not be saved." . . . and it goes on to say other things.

This is interesting to me because the test sheet that i am 'sending' is only 5 records by 14 fields. There are other parts of the spreadsheet that have over 750K records though. But why would it give me this box since I am not sending that sheet. I think the problem is, it is not saving the file as an Excel 2010 file.

At the bottom of the box it asks me if I want to continue.

I say yes, and then the program freezes and does not save the file. It only saves a place holder "7BDA8900" (or some other random number) roughly the size of what the saved worksheet would be. I have to kill the program and start it up again to retrieve excel.

thx for your continued help.

Link to my test file. https://www.dropbox.com/s/218zzyz6m1...KarthikII.xlsm

I think what is needed is a separate instance of Excel.