asked on

copy multiple sheets into a new workbook for the used range

I use the below array to copy multiple sheets into a new workbook but how can I copy multiple sheets into a new workbook for the used range in each worksheet

get name to save new workbook as. change reference as needed
    strSaveName = Worksheets("Sheet2").Range("a1").Value

    ' copy sheets to new workbook
    Sheets(Array("Sheet1", "Sheet3")).Copy
    ActiveWorkbook.SaveAs strSaveName

Open in new window

Roy Cox

Do you mean that you want to combine the data from each sheet into one?

Fabrice Lambert

for the used range in each worksheet

What do you mean here ?

upobDaPlaya

ASKER

Workbook 1 has 2 sheets "Sheet1", "Sheet3" I want to copy Sheet1 and Sheet2 to a New Workbook called Book2. Book 2 should have Sheet1 and Sheet2 just exactly like Workbook1

However, when I do the copy for each sheet from Workbook 1 it be for the UsedRange. The reason for this is if I don't for some reason my array above is copying over a million rows from each sheet even though there is no data or formatting (that I am aware of) beyond 200 rows. Subsequently it takes forever and I usually get a memory error.

Also I just don't want to do a SaveAs as Workbook1 is a macro and has a lot of sheets, buttons, etc.

ASKER CERTIFIED SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

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

Start Free Trial

upobDaPlaya

ASKER

Fabrice what I mean by used Range is if my cells that have data are a1 thru f25 then I consider my used range to be a1 to f25

Fabrice Lambert

I still don't see the point of creating new sheets and copying range.
Uneeded work when this can be done by copying sheets.

upobDaPlaya

ASKER

When I copy sheets it copies over a million rows and I get a memory error..

Fabrice Lambert

Afraid to repeat myself:

Did you try to copy sheets one by one instead of all at once ?

Roy Cox

Workbook 1 has 2 sheets "Sheet1", "Sheet3" I want to copy Sheet1 and Sheet2 to a New Workbook called Book2. Book 2 should have Sheet1 and Sheet2 just exactly like Workbook1

This doesn't look right. Has Workbook 1 more than two sheets?

upobDaPlaya

ASKER

Hi Roy ..I tried sheet by sheet and that worked...thx