excel

In the sheet check book balance, I would like to take all records that have op or mbr or whatever in remarks and and have those records (rows) automatically copied to different sheet to summarize that spending.
Is that possible with just a formula or do I need a macro.  HELP

Thanks,
Henry
MY-MCL-for-experts.xlsx
hchiava1Asked:
Who is Participating?
 
Anne TroyEast Coast ManagerCommented:
hchiava1, you should accept Rodney's answer so that he is recognized for helping you.
Thanks!
0
 
Rodney EndrigaData AnalystCommented:
You can use a FILTER on Row3 and summarize your data in the existing sheet.
 in the 'Sort & Filter' section, then click 'Filter'.

No need for formula or macro.

Once the FILTER is applied, you can select whichever REMARK you would like to see (either cg, mbr, op, etc.) and get a quick sum of that specific Remark.

Let me know if this makes sense.
0
 
hchiava1Author Commented:
It makes sense but its not what I want to do.
I would like to select all rows that have qm in the remarks column and copy/paste into another sheet.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
hchiava1Author Commented:
I appreciate your help.
0
 
Rodney EndrigaData AnalystCommented:
No problem, hchiava1. You can accomplish this through a Macro.

Would you like to separate ALL Remarks into their own separate Sheets or just the Remark='qm' into its own sheet?

You can try this code for the REMARKS='qm':
Sub ee_CheckBookSummary()
Dim ws As Worksheet, rng As Range, Remark As String, ws2 As Worksheet

Set ws = Sheets("Check book balance")
Remark = UCase("qm")
Set rng = ws.Range(Cells(3, 1), Cells(Rows.Count, Columns.Count).End(xlToLeft))
Sheets.Add(Sheets(Sheets.Count)).Name = Remark
Set ws2 = Sheets(Remark)

ws.Range("A3").AutoFilter      'NO BLANK ROWS; needs contiguous row data!
rng.AutoFilter Field:=9, Criteria1:=Remark
With ws
    .UsedRange.SpecialCells(xlCellTypeVisible).Copy ws2.Range("A1")
    .ShowAllData
End With
ws2.Cells.EntireColumn.AutoFit
End Sub

Open in new window

0
 
hchiava1Author Commented:
Works great initially.  When I add data and run it again it errors.
Remark = UCase("qm")
Set rng = ws.Range(Cells(3, 1), Cells(Rows.Count, Columns.Count).End(xlToLeft))

If I delete the sheet it created, it will run again without the error.
And yes, I would like to do it for all remarks.
Is that just a matter of copying the code and editing qm and maybe something else??
Just show me where and I can do that.

Thanks again - its good to be going in the right direction.
0
 
Rodney EndrigaData AnalystCommented:
hchiava1, I'll work on the update to capture all the REMARKS and create separate sheets.
0
 
hchiava1Author Commented:
I don't need it to create the sheets - just copy the data into them.
I did this by recording what I was doing and it seems to work.
Probably can use a little cleanup - LOL

Range("B:B,G:G").Select
    Range("G1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveSheet.Range("$A$1:$J$346").AutoFilter Field:=9, Criteria1:="cg"
    Range("A1:J360").Select
    Selection.Copy
    Sheets("cg").Select
    Range("A1").Select
    ActiveSheet.Paste Link:=True
    Range("G27").Select
    Sheets("Check book balance").Select
    Range("D365").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$J$346").AutoFilter Field:=9, Criteria1:="hh"
    Range("A1:J360").Select
    Selection.Copy
    Sheets("hh").Select
    Range("A1").Select
    ActiveSheet.Paste Link:=True
    Range("C30").Select
    Sheets("Check book balance").Select
    Range("K220").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$J$346").AutoFilter Field:=9, Criteria1:="la"
    Range("A1:J360").Select
    Selection.Copy
    Sheets("la").Select
    Range("A1").Select
    ActiveSheet.Paste Link:=True
    Range("C30").Select
    Sheets("Check book balance").Select
    Range("K220").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$J$346").AutoFilter Field:=9
    Columns("B:B").Select
    Selection.EntireColumn.Hidden = False
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = False
End Sub
0
 
Rodney EndrigaData AnalystCommented:
hchiava1, this should work for you. It will create a sheet for each of the REMARKS. If you add more data to the "Check Book Balance" sheet, you will be able to re-run the macro and it will add the newly added data to each sheet.

Sub ee_CheckBookSummary()
Application.DisplayAlerts = False
Dim ws As Worksheet, rng As Range, Remark As String, ws2 As Worksheet, X, Y
Dim objDict As Object, lngRow As Long

Set objDict = CreateObject("Scripting.Dictionary")
Set ws = Sheets("Check book balance")   ' Sheet with ALL REMARKS entered
X = Application.Transpose(Range([I4], Cells(Rows.Count, "I").End(xlUp)))    ' REMARK column ref
Set rng = ws.Range(Cells(3, 1), Cells(Rows.Count, Columns.Count).End(xlToLeft))
If ws.AutoFilterMode = False Then
    ws.Range("A3").AutoFilter
End If
For lngRow = 1 To UBound(X, 1)
    objDict(UCase(X(lngRow))) = 1
Next
For Each V In objDict.Keys()
    On Error Resume Next
        Sheets(V).Delete
    On Error GoTo 0
Next V
For Each V In objDict.Keys()
    If Not IsEmpty(V) And Trim(V) <> vbNullString Then
        Sheets.Add(after:=Sheets(Sheets.Count)).Name = V
        Set ws2 = Sheets(V)
        rng.AutoFilter Field:=9, Criteria1:=V
        With ws
            .UsedRange.SpecialCells(xlCellTypeVisible).Copy ws2.Range("A1")
            .ShowAllData
        End With
        ws2.Cells.EntireColumn.AutoFit
    End If
Next V
ws.Select
Application.DisplayAlerts = True
End Sub

Open in new window

0
 
hchiava1Author Commented:
I get an error here
Set ws = Sheets("Check book balance")   ' Sheet with ALL REMARKS entered
0
 
Rodney EndrigaData AnalystCommented:
Is that the name of  your sheet or has it changed/updated?

I used your attached workbook as my template.

Try using this file, I have included the VBA code in 'ThisWorkbook' module.
MY-MCL-for-experts.xlsb
0
 
hchiava1Author Commented:
nope - hasn't changed
I'll try it - thanks
0
 
hchiava1Author Commented:
I think I've got it - thanks to all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.