VBA Save itself as .xlsx then find leftmost tab

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:

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:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
jacobrcotton,

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

Code:
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...

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

Cheers,

tonyyy
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top