Excel VBA Macro

[rburg86]

Hello! I have a report that we download from the web every morning, that is really messy and needs tidying up. I wrote a macro that does a pretty nice job, but isn't perfect. I have attached the macro below:

Sub CaseAgingMacro()

        Workbooks.Open Filename:= _
        "C:\Mirixa\#Macro\CaseAgingReport.xlsx"
    Sheets("CaseAgingReport").Select
    Sheets("CaseAgingReport").Move Before:=Workbooks( _
        "Case Aging Macro 2016 V2.xlsm").Sheets(1)

    Sheets("CaseAgingReport").Select
    Rows("1:8").Select
    Selection.Delete Shift:=xlUp
    Columns("A:D").Select
    Range("D2001").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("F:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:N2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("B:E").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("J:J").Select
    Selection.NumberFormat = "m/d/yyyy"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(LEFT(RIGHT(RC[-1],5),12),4)"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B2000")
    Range("B2:B2000").Select
    Selection.Copy
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Store"
    Range("A2").Select
    Columns("A:A").EntireColumn.AutoFit
    Rows("1:1").Select
    Selection.AutoFilter

End Sub

Essentially the macro deletes some rows and columns and tidies up some data. The problem that I have is that the file can have a different amount of records (or rows of data) in it. The current macro allows it to select ranges for up to row 2,000. I am wondering if there is a way that I can have Excel automatically detect the amount of records and incorporate that into the macro. Any help with this would be appreciated!

 

[p45cal]

Untested. No selecting so if you want to follow it step by step you may have to select sheets manually while stepping through the code.

Sub CaseAgingMacro2()
Workbooks.Open fileName:="C:\Mirixa\#Macro\CaseAgingReport.xlsx"
With Sheets("CaseAgingReport")
  .Move Before:=Workbooks("Case Aging Macro 2016 V2.xlsm").Sheets(1)
  .Rows("1:8").Delete
  .Range("A:D, J:P, T:T").Delete
  Set RngToCopy = Intersect(.UsedRange, .Range("A:N"))
End With
With Sheets.Add(After:=Sheets("CaseAgingReport"))
  .Cells(1).Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count).Value = RngToCopy.Value
  .Columns("B:E").Delete
  .Range("G:G, J:J").NumberFormat = "m/d/yyyy"
  .Columns("B:B").Insert
  With .Range("B2:B" & RngToCopy.Rows.Count - 1)
    .FormulaR1C1 = "=LEFT(LEFT(RIGHT(RC[-1],5),12),4)"
    .Value = .Value
  End With
  .Columns("A:A").Delete
  .Range("A1").Value = "Store"
  .Cells.EntireColumn.AutoFit
  .Rows("1:1").AutoFilter
End With
End Sub

 

[p45cal]

I wonder if this helped rburg86…