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
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
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"
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
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
Regards
ASKER
Sub macro()
For Each Item In Split("File A,File B,File C,File D", ",")
Sheets(Item).Copy
ActiveSheet.UsedRange.Valu e = ActiveSheet.UsedRange.Valu e
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 ?
For Each Item In Split("File A,File B,File C,File D", ",")
Sheets(Item).Copy
ActiveSheet.UsedRange.Valu
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?
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thank you Shums, the code worked perfect :)
Glad to help you.
Thanks for accepting my solution.
Thanks for accepting my solution.
Pls make sure your description is correct
You said files are
You said files are
"File A", "File B", "File C", "File D"but then they were "FileA", "FileB" etc.
What name should the new workbook be saved under?