May 14 2021 02:27 PM
Hello,
I recorded a simple macro and Stored it in the Personal Macro Workbook. When I tried to run it on another new file, the macro was not present? Any help would be appreciated.
Rye
May 15 2021 03:30 AM
Press Alt+F11 to activate the Visual Basic Editor.
Expand VBAProject (PERSONAL.XLSB) in the navigation pane on the left, then expand Modules (if present).
Double-click each of the items under the Modules heading.
Do you see the macro that you recorded?
May 15 2021 09:02 AM
May 15 2021 09:52 AM
Since you want to be able to use the macro for multiple workbooks, you should store it in your personal macro workbook PERSONAL.XLSB.
See Copy your macros to a Personal Macro Workbook for instructions on how to do that.
May 15 2021 02:27 PM
May 15 2021 03:04 PM
Here is a macro; you can tweak it for your purposes of course. I have added comments above most lines.
Sub ProcessCSV()
Dim strFile As String
Dim wbk As Workbook
Dim wsh As Worksheet
' Prompt user for csv file
strFile = Application.GetOpenFilename(FileFilter:="CSV files (*.csv),*.csv")
' Get out if no file selected
If strFile = "False" Then
Beep
Exit Sub
End If
' Speed up execution by hiding what we're doing
Application.ScreenUpdating = False
' Open the csv file
Set wbk = Workbooks.Open(Filename:=strFile)
' Reference to the first (and only) sheet
Set wsh = wbk.Worksheets(1)
' Optional: rename tab
wsh.Name = "Raw Data"
' Sort on column A
wsh.UsedRange.Sort Key1:=wsh.Range("A1")
' Color columns A, F and H
Intersect(wsh.UsedRange, wsh.Range("A:A,F:F,H:H")).Interior.Color = vbYellow
' Make row 1 bold
Intersect(wsh.UsedRange, wsh.Range("1:1")).Font.Bold = True
' Save as a .xlsm workbook
wbk.SaveAs Filename:=wbk.Path & "\DATA.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
' Optional: close the workbook
wbk.Close
' Update the screen again
Application.ScreenUpdating = True
End Sub
May 15 2021 05:08 PM