Previous Topic Next Topic

How to add rows that meet specific conditions

Solved/Closed
SalinaSJames Posts 1 Registration date Sunday January 16, 2022 Status Member Last seen January 27, 2022 - Jan 27, 2022 at 11:31 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 28, 2022 at 04:15 AM
Hi

Every week at work we get data about new jobs that have been completed at our company, and I add that data to a raw data tab. In the summary tab I have a table that grabs keys data about specific jobs and summarizes it. Right now I have to input the job names into the summary tab and then use vlookups to pull the rest of the key data from the raw data tab. But I'd like the table on the summary page to just be scanning the raw data and every time a certain condition is met to simply add that job name to the table (essentially automating the step of adding new jobs to the summary tab).

Is there an easy way to do this?
Related:

1 response

Answer 1 / 1
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 28, 2022 at 04:15 AM
Hello Salina,

It would be best if you uploaded a sample workbook to a file sharing site such as WeTransfer or Drop Box then post the link to your file back here.
This will enable us to see exactly what you are trying to do otherwise we can only guess. It will allow us to test as well on a 'live' workbook.
Make sure that the sample workbook is an exact replica of your actual workbook and if your data is sensitive then please use dummy data.

As an insight as to how you may be able to do it, I've attached a sample file at the following link:-

https://wetransfer.com/downloads/b59d1477187c676fed32edca464dfd4b20220128090442/025dd8

The VBA code, which is in the Summary sheet module, is as follows:- 

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
            With Sheets("Raw Data").[A1].CurrentRegion
                    .AutoFilter 1, Target
                    .Offset(1).EntireRow.Copy Me.Range("A" & Rows.Count).End(3)(2)
                    .AutoFilter
            End With
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub


To access the code, right click on the Summary sheet tab and select "View Code" in the menu that appears. You'll see the code in the big white code field.

In the sample file, you'll see a Raw Data sheet and a Summary sheet. In cell A1 of the Summary sheet, you'll find a data validation drop down list with some job numbers from the Raw Data sheet listed. Select any job number and the relevant data from the Raw Data sheet will be instantly displayed in the Summary sheet.

I hope that this at least heads you in the right direction.

Cheerio,
vcoolio.
0