Categories
Excel

My Activities on Excel Forums

Feel free to follow my activities on Excel forums.

Categories
Excel Tutorial

How to Make an XLSX File Macro Enabled in MS Excel 2015?

Please follow the steps below to Macro Enable an Excel file.

    • Open your XLSX Excel file.
    • Click on the “File” on the menu
      Excel Menu, File
    • Click “Save As”
      MS Excel, Save As
    • Choose your folder and select Excel Macro-Enabled Workbook (.xlsm) from the dropdown.
      Excel Macro-Enabled Workbook XLSM
    • Click “Save” button
    • Re-open you file
    • Click “Enable Content” on the yellow bar
      Enable Content on MS Excel
    • Now your file is Macro Enabled.
Categories
Excel Tutorial

Example of Looping through Worksheets, Deleting Rows & Converting to CSV

This VBA is an example of that loops through selected worksheets in a workbook, deletes the top ten rows and exports it as a CSV.

[vb]
Sub Test_loop()

ActiveWorkbook.Save

Dim ws As Worksheet
Dim path As String

path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") – 1)

‘ Loop through all selected sheets.
For Each ws In ActiveWindow.SelectedSheets

IF ws.name <> "Monday" then
ws.Rows("1:10").Delete ‘ Delete 10 rows at top of each sheet.
ws.Copy

Range("A1").Interior.Color = 1 ‘ Format A1 so the top rows are included in the used range and saved
ActiveWorkbook.SaveAs Filename:=path & "_" & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges = False

End IF
Next ws

End Sub

[/vb]