Excel VBA save Work Sheet

I would like to be able to save a work sheet as an xlsx spread sheet independent of the xlsm workbook that created it.

Please see attached word document for screen shots and more information.
If you have any questions ot need additional information, please let me know.
Thanks in advance
EE-save-WorkSheets.docx
tesla764Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven HarrisPresidentCommented:
You can try something such as:

Sub Q_28256982()
    sName = ActiveSheet.Name
    myDir = ActiveWorkbook.Path
    
    Application.ScreenUpdating = False
    ActiveSheet.Select
    ActiveSheet.Copy
    ActiveSheet.SaveAs Filename:=myDir & "/" & sName & ".xlsx"
    Application.ScreenUpdating = True
End Sub

Open in new window


This will:

-Set the Active Sheet's Name
-Set the the current path (where it is already stored)
-Copy the Sheet to a new workbook
-Save the new workbook as the Active Sheet's Name in Step 1 as "TabName.xlsx"
-Workbook remains open for viewing
0
Rgonzo1971Commented:
Hi,

Sub macro()
PthAndName = ActiveWorkbook.Path & "\" & ActiveSheet.Name

ActiveSheet.Copy
Set Wb = ActiveWorkbook
Application.DisplayAlerts = False
Wb.SaveAs PthAndName, FileFormat:=51, CreateBackup:=False
Wb.Close False
Application.DisplayAlerts = True
Set Wb = Nothing

End Sub

Open in new window

You won't have a warning for transforming a xlsm into xlsx

Regards
0
tesla764Author Commented:
When I ran the code provided I get the error that can be seen in the screen shots provided in the attached word document.
EE-When-I-step-into-the-line-tha.docx
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Steven HarrisPresidentCommented:
For my code, is your original workbook saved to a location on your PC?  This will error out if used on a new workbook that is not yet saved.
0
tesla764Author Commented:
To Rgonzo1971,
  When I ran the code you provided I get the results seen the the screen shots provided with this word attachment.
EE2-When-I-step-into-the-line-th.docx
0
tesla764Author Commented:
Seems like all the modules and macros carry over to the new Book that is created.
0
Rgonzo1971Commented:
Hi,

maybe this will do

Sub macro1()
PthAndName = ActiveWorkbook.Path & "\" & ActiveSheet.Name

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

ActiveSheet.Copy
Set wb = ActiveWorkbook
wb.SaveAs PthAndName, FileFormat:=51, CreateBackup:=False
wb.Close False

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Set wb = Nothing

End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steven HarrisPresidentCommented:
Try using the attached sheet and test the outcome.  If it works as intended, there is a conflicting action somewhere in your original workbook.
Q-28256982.xlsm
0
tesla764Author Commented:
Fantastic that worked great. You would think that a common practice like saving a work sheet wouldn't be such a problem.
Thanks Again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.