Excel Task ideas

[Jemma Atkinson]

Hi, i have a workbook which has 3 worksheets("Cash", "Securities", Physical Securities")

This workbook which contains outstanding reconcilliation breaks gets sent to the client each month.

In Sheet "Cash" Col U is Status, which is either blank or has the string "CLEARED" for each row of data

In Sheet "Securities" it will be Col S and sheet Physical Securities it will Col R.

Question i only want to show the client the data which is not CLEARED. What is the best approach in doing this?

 

[markmzz]

I made some changes in the code. Try this:

Sub DataNotCleared()
    Dim mySheet As Worksheet
    Dim LastRow, LastCol As Long
    Dim i As Integer
 
    Application.ScreenUpdating = False
 
    For i = 1 To 3
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets("Data" & i).Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Data" & i
        Set mySheet = Sheets(Choose(i, "Cash", "Securities", "Physical Securities"))
        LastRow = mySheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = mySheet.Cells(11, Columns.Count).End(xlToLeft).Column
        Cells(1, LastCol + 2).Value = "Status"
        Cells(2, LastCol + 2).Formula = "=""<>CLEARED"""
        mySheet.Range(Cells(1, 1).Address, Cells(LastRow, LastCol).Address).AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=Range(Cells(1, LastCol + 2), Cells(2, LastCol + 2)), _
            CopyToRange:=Cells(1, 1)
        Range(Cells(1, LastCol + 2), Cells(2, LastCol + 2)).Clear
    Next i
 
    Sheets(Array("Data1", "Data2", "Data3")).Move
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\DATANOTCLEARED"
    ActiveWorkbook.Close
 
    Set mySheet = Nothing
    Application.ScreenUpdating = True
End Sub

 

[Jemma Atkinson]

Thanks for this.

I made some changes in the code. Try this:

Sub DataNotCleared()
    Dim mySheet As Worksheet
    Dim LastRow, LastCol As Long
    Dim i As Integer
 
    Application.ScreenUpdating = False
 
    For i = 1 To 3
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets("Data" & i).Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Data" & i
        Set mySheet = Sheets(Choose(i, "Cash", "Securities", "Physical Securities"))
        LastRow = mySheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = mySheet.Cells(11, Columns.Count).End(xlToLeft).Column
        Cells(1, LastCol + 2).Value = "Status"
        Cells(2, LastCol + 2).Formula = "=""<>CLEARED"""
        mySheet.Range(Cells(1, 1).Address, Cells(LastRow, LastCol).Address).AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=Range(Cells(1, LastCol + 2), Cells(2, LastCol + 2)), _
            CopyToRange:=Cells(1, 1)
        Range(Cells(1, LastCol + 2), Cells(2, LastCol + 2)).Clear
    Next i
 
    Sheets(Array("Data1", "Data2", "Data3")).Move
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\DATANOTCLEARED"
    ActiveWorkbook.Close
 
    Set mySheet = Nothing
    Application.ScreenUpdating = True
End Sub

 

[Jemma Atkinson]

Jemma,

I have to say: i would adopt Norie's approach - create a new worbook with only the relevant data to your clients.

Its a much better practice and safer than delete rows (or anything) of your original workbook.

M.

The orignal workbook stays intact, I have a code that saves the file as a different name and then deletes the rows.

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] NotCleared()

    [COLOR="Blue"]Dim[/COLOR] rng [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Dim[/COLOR] sh [COLOR="Blue"]As[/COLOR] Worksheet
    [COLOR="Blue"]Dim[/COLOR] arrSheets [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], arrCols [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    
    [COLOR="Blue"]Set[/COLOR] sh = Sheets.Add(After:=Sheets(Sheets.Count))
    [COLOR="Blue"]With[/COLOR] sh: .Name = "Consolidation": .Tab.ColorIndex = 6: [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
    arrSheets = VBA.Array("Cash", "Securities", "Physical Securities")
    arrCols = VBA.Array("U", "S", "R")
    
    [COLOR="Blue"]For[/COLOR] i = 0 [COLOR="Blue"]To[/COLOR] 2
        [COLOR="Blue"]With[/COLOR] Sheets(arrSheets(i))
            lastRow = .Cells(Rows.Count, arrCols(i)).Row
            [COLOR="Blue"]If[/COLOR] .AutoFilterMode [COLOR="Blue"]Then[/COLOR] .AutoFilter.Range.AutoFilter
            .Cells(1, arrCols(i)).AutoFilter Field:=1, Criteria1:="<>CLEARED"
            [COLOR="Blue"]Set[/COLOR] rng = .Range(.Cells(2, "A", .Cells(lastRow, arrCols(i)))).SpecialCells(xlCellTypeVisible)
            [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] rng [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
                rng.Copy
                sh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            [COLOR="Blue"]Next[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Norie]

Jemma


What code are you actually using?


There are quite a few posts with code that will delete/clear data from the original workbook.