VB Script to filter data by keywords and copy the data to a sheet based on the Keywords.

**hayter_123** · 03-05-2020, 09:51 AM

Hi Everyone,

We have an ICT Helpdesk platform and we have ran a report to show us all help desk tickets from the past 18 months. This has resulted in a good 10k of entries.

Our next goal would be trying to filter out these entries by topic. Eg, SharePoint, Teams, Word, Excel. Our end goal is to spot trends in tickets so we can issue training and prevent these tickets coming back in.

So, I have created a sheet called Topic, which has a list of all the subjects we would like to filter out. Then a report sheet, which has all of the tickets. Inside that sheet we have two Columns i need to be able to search. The first one is "Summary" which is column "C" and the second is "Description" Column "D". If one of the search criteria is match i would like to copy that entire row which consists of Creation Date, Created by, Summary and Description, into a corresponding sheet for that particular issue.

I understand this is probably going to be some VB code required to run this but this a little bit above my skill set in coding.

**PCI** · 03-05-2020, 03:55 PM

Please attach a short excel example

**hayter_123** · 03-06-2020, 06:29 AM

Please see attached.

**Marc L** · 03-06-2020, 10:25 AM

Originally Posted by hayter_123

I understand this is probably going to be some VB code required to run this but this a little bit above my skill set in coding.

Hi, as it could be easily done just operating manually an Excel basics : an advanced filter …
Of course an easy VBA procedure may use an advanced filter.

**hayter_123** · 03-06-2020, 10:26 AM

Would an advanced filter be able to move the entries into a corresponding sheet?

I was hoping to automate the whole process, which is why i thought a VBA Script would be best.

**Marc L** · 03-06-2020, 10:51 AM

According to your initial post the advanced filter can directly copy the data to another place
like you can see in Excel help or in VBA help …

As PCI is yet working on this thread, you can wait for his solution.

**PCI** · 03-06-2020, 05:04 PM

Is it what you need ??

Option Explicit

Sub Treat()
Dim WkDic   As Object
Set WkDic = CreateObject("Scripting.Dictionary")
Const WsRefN = "Data Keywords"
Const WsRpN = "Report"
Dim WsRef As Worksheet, WsRp As Worksheet
Dim Rg  As Range
Dim K
Dim IsWs As Boolean

    Set WsRef = Sheets(WsRefN)
    Set WsRp = Sheets(WsRpN)
    With WsRef
        For Each Rg In Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(3))
            WkDic.Item(Rg.Value) = Empty
        Next Rg
    End With
    With WsRp
        For Each K In WkDic
            IsWs = Evaluate("isref('" & K & "'!A1)")
            If (IsWs) Then
                Sheets(K).Cells.Delete
                .AutoFilterMode = False
                With .Cells(1, 1).CurrentRegion
                    .AutoFilter Field:=3, Criteria1:="*" & K & "*"
                    .Cells.Copy Sheets(K).Cells(1, 1)
                End With
            Else
                MsgBox ("Sheet " & vbCrLf & K & vbCrLf & "do not exist")
            End If
        Next K
        .AutoFilterMode = False
    End With
    MsgBox (" Job Done ")
End Sub

**hayter_123** · 03-09-2020, 06:24 AM

So sorry for the late reply PCI. I have been away for a couple of days. This looks good. I will test this later today.

Thank you so much for spending time taking a look and coming up with this.

**hayter_123** · 03-11-2020, 07:41 AM

PCI - Thank you so much for looking into this for me. It works perfectly. Again thank you for spending your time on this.

VB Script to filter data by keywords and copy the data to a sheet based on the Keywords.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VB Script to filter data by keywords and copy the data to a sheet based on the Keywords.

Re: VB Script to filter data by keywords and copy the data to a sheet based on the Keyword

Re: VB Script to filter data by keywords and copy the data to a sheet based on the Keyword

Re: VB Script to filter data by keywords and copy the data to a sheet based on the Keyword

Re: VB Script to filter data by keywords and copy the data to a sheet based on the Keyword

Re: VB Script to filter data by keywords and copy the data to a sheet based on the Keyword

Re: VB Script to filter data by keywords and copy the data to a sheet based on the Keyword

Thread Information

Users Browsing this Thread

Similar Threads

copying data between keywords to another sheet

Using VBA to search for keywords in data set and return results in another sheet

[SOLVED] Compile Numerical data based on Keywords in a Sentence

[SOLVED] VBA looking for multiple keywords and return concatenated keywords, that were found

[SOLVED] Search for keywords and copy rows containing keywords to new sheet

[SOLVED] HDI - Return multiple keywords in a text column based on a set of available keywords

Copy entire row to new sheet based on keywords

Bookmarks

Bookmarks

Posting Permissions