Link to home
Start Free TrialLog in
Avatar of Mostafa Hamza
Mostafa Hamza

asked on

VBA copy certain excel sheets & paste them as values in different new workbooks in .xls version

Hi All,

I would like to copy certain  4 excel sheets (in the same workbook) & paste them as values in different new workbooks in .xls version, also I need these new workbooks to be in the same folder of the original workbook

Thank you in Advance
Avatar of Norie
Norie

Which sheets do you want to copy?

What name should the new workbook be saved under?
Avatar of Mostafa Hamza

ASKER

The Sheets name: "File A", "File B", "File C", "File D"

I want each of them in a separate Workbook

Each Workbook should be the same as the Sheet name:
for example:
Workbook 1 name: "File A"
Workbook 2 name: "File B"
Workbook 3 name: "File C"
Workbook 4 name: "File D"
Hi,

pls try
Sub macro()
For Each Item In Split("File A,File B,File C,File D", ",")
    Sheets(Item).Copy
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    ActiveWorkbook.SaveAs Item & ".xls", xlExcel8
    ActiveWorkbook.Close False
Next
End Sub

Open in new window

Regards
Sub macro()
For Each Item In Split("File A,File B,File C,File D", ",")
    Sheets(Item).Copy
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    ActiveWorkbook.SaveAs Item & ".xls", xlExcel8
    ActiveWorkbook.Close False
Next
End Sub

It didnt work, it gave me an error regarding "Sheets(Item).Copy"

Does this create 4 new workbooks? where is the path? & does it past as values ?
Could you send a dummy file?
Mostafa,

Please find attached....
SaveAS-Workbooks.xlsm
Hi Shums,

Thank you for the file, but my file already has 8 sheets, I would like to choose only the 4 sheets not all the sheets in the work book

Also I would like to make the new workbook extension .xls not .xlsx

I have amended your file with the rest sheets & attached it again

I will be waiting your feedback
SaveAS-Workbooks.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you need to do this for more than just this one file, consider a "macro" answer.  
 
If this is the only file, I assume you want 4 new xls files, each having one of your original xlsx sheets.

1st, if you open your xlsx file by dbl clicking it, any saves of new files will go to that folder. So dbl-click on "your.slsx" file to launch Excel.  Once opened a "Save as" and when asked change the type to "*.xls" and give it a name like "NewSheet1". Then do a second "Save as" and name it "NewSheet2", type should remain "*.xls". And repeat for NewSheet3 and NewSheet4.

Now you have four new identical uncompressed files. Sheet by sheet "copy" and "paste special" (values), in place, as you see fit for any of or all of the sheets. For example NewSheet1 - copy and paste-special (values) for all data on sheet1. Delete the sheets you don't need. Now open NewSheet2 -  copy and paste-special (values) for sheet2, and delete the sheets you don't need, etc.
Thank you Shums, the code worked perfect :)
Glad to help you.

Thanks for accepting my solution.
Pls make sure your description is correct

You said files are
"File A", "File B", "File C", "File D"
but then they were "FileA", "FileB" etc.