Potential Macro Question

[hellokirsten]

Hi all! Very happy to find this resource
I will try to be as thorough/concise as I can be

- I have a CSV file that we download for a number of customers. I have created a macro to remove all of the unnecessary information from that CSV file
- Now, there is a field of audit notes that I am trying to divide into these different sections: Idle (notes that contain "idle"), Hardware (notes that contain "battery, heartbeat, transition, and transport"), and Install (notes that contain "initial" or "engine")
- For those three different sections, I am wanting to build something that will take that full file, and divide it into three different sheets

I've tried to record a macro - but the line items range from 20 - 700, so the recording of the macro did not work very well.
Any help would be greatly appreciated!

 

[Fluff]

In that case try

Sub FilterToNewSheets()

   Dim SrcWs As Worksheet
   Dim CritAry As Variant
   Dim ShtAry As Variant
   Dim Usdrws As Long
   Dim Cnt As Long
   
   CritAry = Array("*idle*", "*battery*", "*heartbeat*", "*transition*", "*transport*", "*initial*", "*engine*")
   ShtAry = Array("Idle", "Hardware", "Hardware", "Hardware", "Hardware", "Install", "Install")
   Set SrcWs = ActiveSheet
   
   With SrcWs
      Usdrws = .Range("A" & Rows.Count).End(xlUp).Row
      .Columns(6).Copy .Columns(7)
   
      If .AutoFilterMode Then .AutoFilterMode = False
      For Cnt = LBound(CritAry) To UBound(CritAry)
         If Not shtexists(CStr(ShtAry(Cnt))) Then Sheets.Add(, Sheets(Sheets.Count)).Name = ShtAry(Cnt)
         .Range("A1:G" & Usdrws).AutoFilter Field:=6, Criteria1:=CritAry(Cnt)
         On Error Resume Next
         .Range("A2:F" & Usdrws).SpecialCells(xlVisible).Copy Sheets(ShtAry(Cnt)).Range("A" & Rows.Count).End(xlUp).Offset(1)
         .Range("G2:G" & Usdrws).SpecialCells(xlVisible).ClearContents
         On Error GoTo 0
      Next Cnt
      .AutoFilterMode = False
      .Columns(7).Clear
   End With
End Sub

Public Function shtexists(ShtName As String) As Boolean
    On Error Resume Next
    shtexists = (LCase(Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function

 

[hellokirsten]

You are a miracle worker.
is there something I can add in to bring over the labels from sheet 1 over to the next 3?
thank you so so so much!

 

[Fluff]

Change this as shown in red

.Range("[COLOR=#ff0000]A1[/COLOR]:F" & Usdrws).SpecialCells(xlVisible).Copy Sheets(ShtAry(Cnt)).Range("A" & Rows.Count).End(xlUp).Offset(1)