Moving Rows to Other Sheets Based on Values in the First Column?

[Mmmmq]

I'm new to Excel Macros but not to programming. I have some training in C, C++, IDL and HTML. However, I'm still muddling about trying to get a handle to the syntax and capabilities of VBA.

As far as my question is concerned, I have a master sheet with 22 columns of data. Time, a categorical integer value of 0, 2, 8, 24, 48, 72, or 120 is in column A (starting at A2). I would like to sort data from this master list into sheets, one for each time set, copying the entire row of data for each entry. For example, A2 = 0 so all of row A (or at least the first 22 cells of row A) would be copied into the sheet "0 Hours" without altering the data in the master sheet.

I'm not sure if I should go for a series of Do While statements or Nested IF statements in a Do While Loop? Suggestions?

 

[Jeffrey Mahoney]

Here we go...
Writing code...

 

[Jeffrey Mahoney]

This is a good start. I didn't do any error checking. You get the drift. Questions?

Sub MoveRows()  
Dim ThisSht As Worksheets
  Dim Rng As Range
  Dim Cel As Range
  Dim Sht0 As Worksheet
  Dim Sht2 As Worksheet
  Dim Sht8 As Worksheet
  Dim Sht24 As Worksheet
  Dim Sht48 As Worksheet
  Dim Sht72 As Worksheet
  Dim Sht120 As Worksheet
  Dim CopySht As Worksheet
  
  Set ThisSht = ActiveSheet
  Set Sht0 = Sheets("SheetZero")
  Set Sht2 = Sheets("SheetTwo")
  Set Sht8 = Sheets("Sheeteight")
  Set Sht24 = Sheets("SheetTwoFour")
  Set Sht48 = Sheets("SheetFourEight")
  Set Sht72 = Sheets("SheetSevenTwo")
  Set Sht120 = Sheets("SheetOneTwoZero")
  
  Set R = ThisSht.Range(Range("A2"), Range("A1000000").End(xlUp))
  For Each Cel In R
    Select Case Cel.Value
      Case 0
        Set CopySht = Sht0
      Case 2
        Set CopySht = Sht2
      Case 8
        Set CopySht = Sht8
      Case 24
        Set CopySht = Sht24
      Case 48
        Set CopySht = Sht48
      Case 72
        Set CopySht = Sht72
      Case 120
        Set CopySht = Sht120
    End Select
    Cel.EntireRow.Copy CopySht.Range("A1000000").End(xlUp).Offset(1, 0)
  Next Cel
  
End Sub

 

[Mmmmq]

Cases! I like it. Thank you!

 

[Smitty]

Welcome to the Board!

Why not create a pivot table? If you put Time in the report Filter field, you can use the Show Report Filter pages option, and Excel will automatically create a pivot table sheet for each item in the list. The nice thing about this is that as you add data you just refresh the pivot tables to display the most recent info.