Excel Spreadsheet colume to tab

I have a report that I'm saving as an excel spreadsheet that reports on buses that have had the software updated. The report incorporates all vehicles but I would like to have it list the vehicles by vehicle number in a tab rather than in a column and include only the associated rows in that tab. I've attached the spreadsheet for your review. All suggestions are greatly appreciated.

Brian
8e379eeb.xls
bjbrownAsked:
Who is Participating?
 
FlysterCommented:
See if this works for you:

Sub NewSheets()
Dim i, l, r As Integer
Dim sn As String

Range("Sheet1!A2").Select
i = Range("D" & Rows.Count).End(xlUp).Row 'Finds Last row
s = 2

For l = 2 To i
  sn = Range("Sheet1!A" & l).Value
    If sn <> "" Then 'If value appears in column A, will create new sheet
      Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Select
          Sheets(Sheets.Count).Name = sn
            Range("A1").Value = "Vehicle"
              Range("D1").Value = "Activity Date"
            Range("F1").Value = "Activity Time"
          Range("G1").Value = "Activity"
        Range("A2").Value = sn
      r = 3
    End If
    
    If sn = "" Then 'Populates values in new sheet
      Range("D" & r).Value = Range("Sheet1!D" & l).Value
        Range("F" & r).Value = Range("Sheet1!F" & l).Value
      Range("G" & r).Value = Range("Sheet1!G" & l).Value
      r = r + 1
    End If
    
  Next l

End Sub

Open in new window


Flyster
0
 
Rodney EndrigaData AnalystCommented:
bjbrown, is this the usual format of data you will be working with?

It is not a standardized format, but a macro would probably do what you are looking for.
When I mention standardized, I refer to NO BLANK ROWS, EACH cell is populated with valid data, etc.
0
 
bjbrownAuthor Commented:
Yes, this is directly from a report that is generated by the application. This is one of the options to save the report as, other options are pdf, html, rtf and word.
0
 
bjbrownAuthor Commented:
This is perfect, thank you Flyster !!!!
0
 
FlysterCommented:
Thanks. Glad I could help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.