Excel VBA - Copy worksheet to a new workbook and rename
Вставка
- Опубліковано 9 жов 2021
- How to copy worksheet to a new workbook in VBA?
How to save a workbook using the name of the worksheet?
Functions:
1. For Each ws In ActiveWorkbook.Worksheets
2. ws.Copy
3. ChDir "PASTE YOUR PATH HERE"
4. ActiveWorkbook.SaveAs Filename:="PAST YOUR PATH HERE\" & ActiveSheet.Name & ".xlsx"
5. ActiveWorkbook.Close
Remark: to save as "Macro-Enabled" workbook, use ".xlsm".
Omg, such a life saver!!! After hours and hours looking at various videos on this topic, I finally understand. Great explanation and simple straightforward code! THANK YOU!
My pleasure. Thanks for watching and have a nice day.
Thanks...found this by mistake but it was exactly what I was looking for.
My pleasure. Thanks for watching and have a nice day.
Exactly what I needed. Thank you!
My pleasure. Thanks for watching and have a nice day.
Very Good
Thanks
Thanks!
My pleasure. Thanks for watching and have a nice day.
Thanks for sharing this, can you help us with code where we have more sheets in workbook and form which we want to save only selected sheets in workbook and rename the sheets.
Hi, Shabari. Unfortunately, we could not only save a sheet in a workbook with couple of sheets. However, we could export the selected sheet and save it as a new workbook, i.e. a workbook with single sheet.
Thanks for watching and have a nice day.
Nice
Thanks!
Thanks for watching and have a nice day.
nice you,explain it very well, how about if i want only the specific sheet to copy?
Thanks!
Instead of using "For each ws ..." and "ws.copy", may try "Sheet?.Copy". Hope this helps.
Thanks for watching and have a nice day.
I have two sheets ("Control" and "Update") that i would like to not be copied to the folder path.
Is it possible to exclude worksheets from the copy?
Hi. Can use 'If' statement to exclude them.
Thanks for watching and have a nice day.
Compile error t Sub CopySheet()
why ? Can u help me?
Hi. Could you tell me what kind of compile error you got in more detailed? There are a few types of compile errors.
Thanks for watching and have a nice day.
How to save as pasted values? Please help
Hi Singh. Do you mean to make a copy of a worksheet without formatting/formulas?
If yes, may consider to clear formats after created a copy, then copy contents and paste as special.
To illustrate this better, I have a video for you at ua-cam.com/video/JL01OD0sSDk/v-deo.html.
Hope it helps.
Have a nice day.