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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
hchiava1Author Commented:
I appreciate your help.
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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
Anne TroyEast Coast ManagerCommented:
hchiava1, you should accept Rodney's answer so that he is recognized for helping you.
Thanks!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.