VBA Macro dosn't appear in new workbooks

Question

Hello,I recorded a simple macro and Stored it in the Personal Macro Workbook.&nbsp; When I tried to run it on another new file, the macro was not present? Any help would be appreciated.Rye&nbsp;

hansvogelaar · Answer

RyeTo&nbsp;
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?

ryeto · Answer

Hi Hans, hope you're well. Everyday I download .cvs file (each with a new random name), do some macro changes and save it as DATA.xlsm. This is what I've done:
download: xyz259712.cvs (everyday is a different name)
change tab name: Raw Data
save as: DATA.xlsm
record macro: name: DATA, Ctl+Shift+D, Store Macro in: Personal Macro Workbook
select entire sheet: sort A to Z
select columns A,F, and H, colour Yellow
select Row1, Bold
stop Recording
same file.
So now I wish to:
1. download and new random .cvs file
2. have the macro do the minor changes above
3. Update the DATA file.
Seems simple but have not been able to do it?
Thanks

hansvogelaar · Answer

RyeTo&nbsp;
Since you want to be able to use the macro for multiple workbooks, you should store it in your personal macro workbook PERSONAL.XLSB.
&nbsp;
See Copy your macros to a Personal Macro Workbook for instructions on how to do that.

ryeto · Answer

I finally got the macro to run.After downloading the .csv file I needed to rename the tab to "Raw Data" and save it as DATA.XLSM before running the macro.Any way to run macro with the .cvs file directly without renaming the tab and saving to DATA.XLSM manually?

hansvogelaar · Answer

RyeTo&nbsp;
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 &amp; "\DATA.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ' Optional: close the workbook
    wbk.Close
    ' Update the screen again
    Application.ScreenUpdating = True
End Sub

Forum Discussion

VBA Macro dosn't appear in new workbooks

6 Replies

Resources