OzGrid

How to use VBA code to generate report based on criteria

Requirement:

The user has an excel sheet with sheets "Checklist" & "CAR", in which the user needs a VBA code in the checklist sheer in "Generate CAR" button and if click that button CAR report to be generated in "CAR" sheet as provided in the attached excel sheet.

1. VBA code should filter the field "I5" if I6 to I127=CAR, then the filtered data from C6:H127 has to be copied and pasted to "CAR" sheet as given in the attached excel.
2. Further if any data changed in "Checklist" and I click "Generate CAP" again then the previously copied/pasted data in "CAR" has to be removed and fresh data has to be copied & pasted in "CAR" sheet.
3. After copy & paste to "CAR", checklist list show full list without filter.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1218005-vba-code-to-generate-report-based-on-criteria

Solution:

Try the following code assigned to your button:-

Sub Test()

        Dim ws As Worksheet: Set ws = Sheets("Checklist")
        Dim ws1 As Worksheet: Set ws1 = Sheets("CAR")

Application.ScreenUpdating = False

        ws1.UsedRange.Offset(4).ClearContents
        
        ws.Range("I5", ws.Range("I" & ws.Rows.Count).End(xlUp)).AutoFilter 1, "CAR"
        ws.Range("C6", ws.Range("H" & ws.Rows.Count).End(xlUp)).Copy
        ws1.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlValues
        ws.[I5].AutoFilter

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Obtained from the OzGrid Help Forum.

Solution provided by vcoolio.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery