jacobrcotton
Board Regular
- Joined
- Jan 28, 2017
- Messages
- 51
Hello Everyone,
Again, calling in the big boys as I can't quite get this right.
I have a macro that essentially calls other subs that collectively make my files "client ready", which is to say it saves the workbook, copies & pastes values, runs through prompts to delete unnecessary tabs, clears contents/formats of all fields outside of the print area on each tab, and then finally "finds home" (ie, cell A1) on all sheets.
Its great. its just beautiful. But...i'm having two problems.
(1) The original workbook/template (where the macro is stored) is obviously .xlsm. The sub "SaveAsWithTimeStamp" is currently saving as an .xlsm. But i would like a final "save as" macro that saves the workbook as .xlsx (effectively removing the macros and macro capabilities). This sounds logically counter intuitive, as we are removing the macro capabilities in a workbook that is in the middle of a macro. But stranger things have happened, so i'm reaching out.
(a) Is it possible to have a workbook save itself as a ".xlsx" through a macro?
(b) Note that I'm also transferring this code to my PERSONAL workbook so that I can execute this code (or parts of it) on any workbook, so is there a way to save any excel file (that does not house the macro, regardless of extension) as a .xlsx?
(2) I'd like a final sub that finds the left-most worksheet in the workbook. This worksheet will usually be named "Experience", but not always. Easy enough to have a macro just find the worksheet "Experience", but I can't find a satisfactory code online that will find the left most worksheet regardless of name/code name (ie, Sheet18, etc).
(a) Does anyone know of a code that will always find the left most worksheet in a workbook, regardless of names?
My codes:
Again, calling in the big boys as I can't quite get this right.
I have a macro that essentially calls other subs that collectively make my files "client ready", which is to say it saves the workbook, copies & pastes values, runs through prompts to delete unnecessary tabs, clears contents/formats of all fields outside of the print area on each tab, and then finally "finds home" (ie, cell A1) on all sheets.
Its great. its just beautiful. But...i'm having two problems.
(1) The original workbook/template (where the macro is stored) is obviously .xlsm. The sub "SaveAsWithTimeStamp" is currently saving as an .xlsm. But i would like a final "save as" macro that saves the workbook as .xlsx (effectively removing the macros and macro capabilities). This sounds logically counter intuitive, as we are removing the macro capabilities in a workbook that is in the middle of a macro. But stranger things have happened, so i'm reaching out.
(a) Is it possible to have a workbook save itself as a ".xlsx" through a macro?
(b) Note that I'm also transferring this code to my PERSONAL workbook so that I can execute this code (or parts of it) on any workbook, so is there a way to save any excel file (that does not house the macro, regardless of extension) as a .xlsx?
(2) I'd like a final sub that finds the left-most worksheet in the workbook. This worksheet will usually be named "Experience", but not always. Easy enough to have a macro just find the worksheet "Experience", but I can't find a satisfactory code online that will find the left most worksheet regardless of name/code name (ie, Sheet18, etc).
(a) Does anyone know of a code that will always find the left most worksheet in a workbook, regardless of names?
My codes:
Code:
Sub MakeClientReady()
ActiveWorkbook.Save
Call SaveAsWithTimeStamp
Call CopyPasteValuesAllSheets
Call DeleteTabs
Call ClearOutsidePrintArea
Call FindHomeAllSheets
' Call WishListCodeThatSavesAs .xlsx
' Call WishListCodeFindsLeftMostSheet
End Sub
Code:
Sub SaveAsWithTimeStamp()
Dim FilePath As Variant
Dim FileName As Variant
Application.ScreenUpdating = False
ActiveWorkbook.CheckCompatibility = False
FilePath = ActiveWorkbook.Path
FileName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
ActiveWorkbook.SaveAs FileName:=FilePath & "\" & FileName & " " & Format(Now(), "yyyymmddhhmmss") & ".xlsm"
ActiveWorkbook.CheckCompatibility = True
Application.ScreenUpdating = True
End Sub
Last edited: