asked on

xlFileFormat for Excel 2007

Hi Experts, I have some code that creates a new workbook then copies a tab from my existing "Master" workbook to the new one and deletes all of the blank default tabs (my ideal solution would be to just use the same functionality as copy tab --> new workbook, but I can't seem to record that)... I keep running into a problem that my new workbook is always created with 65500 rows, but my template is in the excel 2007 format (even though I only have about 50 rows populated) I get the error that I cannot paste the sheet because the sizes are different. I've been trying FileFormat:= 51

Any thoughts?

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

Which Excel application version are you using?In any event, to copy a worksheet into a new workbook, all you need to do is this:

' Note the destination
Workbooks("Some Workbook.xls").Worksheet("Some sheet").Copy After:=ThisWorkbook.Worksheets(1)

' If you leave out the destination, sheet os copied to new workbook
Workbooks("Some Workbook.xls").Worksheet("Some sheet").Copy

Open in new window

Patrick Matthews

Sorry, my code sample had typos...

' Note the destination
Workbooks("Some Workbook.xls").Worksheets("Some sheet").Copy After:=ThisWorkbook.Worksheets(1)

' If you leave out the destination, sheet os copied to new workbook
Workbooks("Some Workbook.xls").Worksheets("Some sheet").Copy

Open in new window

byundt

Do you have a workbook template in your XLSTART folder? If it is the .xls file format, that may be why you get 65536 rows in the new workbook. The XLSTART folder is contained inside hidden folders, and most people have two: one in Documents and Settings/Windows login name/Application Data/Microsoft/Excel/XLSTART and the other in C:/Program files/Office

byundt

Clarification on the Book.xlt template for new workbooks. It would be in the .xlt file format and will result in a .xls workbook when you use it. The default name for it is Book.xlt

Book.xlt is not required to be present in XLSTART, but if you have one it could account for the reported symptoms.

Brad

Norie

James
Why not just created a new worksheet in the destination file and just copy the data from the source file?
I would think that would avoid any version issues.
If you need to save the worksheet as well as copying it then you could copy the new worksheet, with data, from the destination and not the source workbook.

JamesCbury

ASKER

Sorry for the delay... this was the simplest solution and it worked!

-Thanks again