VBA Save itself as .xlsx then find leftmost tab

[jacobrcotton]

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:

Sub MakeClientReady()


    ActiveWorkbook.Save
    
    Call SaveAsWithTimeStamp
    Call CopyPasteValuesAllSheets
    Call DeleteTabs
    Call ClearOutsidePrintArea
    Call FindHomeAllSheets


'   Call WishListCodeThatSavesAs .xlsx
'   Call WishListCodeFindsLeftMostSheet


End Sub

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

 

[tonyyy]

jacobrcotton,

One approach to save an xlsm as an xlsx is to Copy all the sheets and save the resulting workbook as an xlsx...

Sub SaveAs_1061438()
Dim fPath As String, fName As String
Dim ws As Worksheet
Dim i As Long
Dim arr() As String

fPath = ThisWorkbook.Path
fName = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
ReDim arr(1 To (ThisWorkbook.Sheets.Count))
i = 1

For Each ws In ThisWorkbook.Worksheets
    arr(i) = ws.Name
    i = i + 1
Next ws

Worksheets(arr).Copy
ActiveWorkbook.SaveAs FileName:=fPath & "\" & fName & " " & Format(Now(), "yyyymmddhhmmss") & ".xlsx"
End Sub

Sheets in a workbook can be referred to by position rather than by name - Sheets(1), Sheets(2), etc. For example...

Sub LeftMostSheet()
Sheets(1).Activate
MsgBox Sheets(1).Name
End Sub

Cheers,

tonyyy

 

[jacobrcotton]

Hi tonyyy,

Both codes work perfectly! Thanks so much!

 

[tonyyy]

You're very welcome...