Thread: Beginner Macro Question - Selecting all cells?

I have a macro that is designed to work in a particular report .csv and simply reformat it. However the report changes each time it is run and the macro is only formatting the number of rows etc that existed on the version that it was recorded on originally. Obviously the scrolling etc was the recorder tacking on my checking of the report after it was run and is not necessary.

What I need is for the macro to select and reformat ALL active cells in the csv each time, regardless of how many or how few there are in the given instance?

As you may have guessed I am not a programmer in the slightest and have zero knowledge of VBA, I am simply trying to save myself the headache of a boss who keeps asking me to do the exactly the same reformat job every day!

Thanks for any help anyone can give me on this!

S

Sub Format_1015()
'
' Format_1015 Macro
'

'
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I1631")
    Range("I2:I1631").Select
    Columns("G:I").Select
    Selection.Style = "Currency"
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
    Range("J2").Select
    Selection.NumberFormat = "0.00%"
    Selection.AutoFill Destination:=Range("J2:J1631")
    Range("J2:J1631").Select
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "%"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1:O1").Select
    Selection.Style = "Heading 3"
    Cells.Select
    ActiveWorkbook.Worksheets("1015JGSM").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("1015JGSM").Sort.SortFields.Add Key:=Range( _
        "O2:O1631"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("1015JGSM").Sort.SortFields.Add Key:=Range( _
        "E2:E1631"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("1015JGSM").Sort.SortFields.Add Key:=Range( _
        "K2:K1631"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("1015JGSM").Sort
        .SetRange Range("A1:P1631")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Cells.Select
    Selection.Subtotal GroupBy:=15, Function:=xlCount, TotalList:=Array(15), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Range("D10").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.LargeScroll ToRight:=-1
End Sub

Wow that was quick!
I have attached 2 files, one is the raw CSV saved as .xlsx and the other is the finished reformat

S

wow, thank you so much Richard, I will definitely have a play around with the worksheets you have provided.

So amazingly helpful and so quick too!!

Stephen