Replicating Rows

[AdamB1977]

I'm a Marine currently deployed and have the fortunate opportunity of adopting my predecessor's database. I'm very new to excel and I know just more than "nothing" about excel formulas outside of the norm Sum/count/average type. I was wondering if there is a basic formula that would allow me to replicate from one worksheet to another worksheet (inside the same workbook) based on a specific criteria? Lets say that if in Work Sheet "Main" that if any row has "PA" entered in Column "O" that the entire row is replicated into Worksheet "PA"? I dont know anything about ingesting code or any of that. I hope someone can assist me with this and I'm sorry if in the future I harrass this forum with a bunch of newbie questions.

 

[AdamB1977]

I just realized I probably should have been a little more specific. In my main database worksheet, I have three possible values that will appear in column "O", these are PA, CI or HS. Each of those (PA, CI or HS) have corresponding worksheets w/in the same workbook. If any of those values appear, what forumula do I need to add so that the entire row from the main worksheet is replicated into the corresponding worksheet. Again, i'm sorry if these are too basic.

 

[VoG]

See if this helps http://www.mrexcel.com/forum/showthread.php?t=396069

Note that both Excel Explosion and the code I posted will create the new sheets.

 

[AdamB1977]

I really appreciate the help, but the code stuff is WAY beyond my knowledge base. The extent of my Excel Formula knowledge is literally sum/count/average, you know the "very basic" stuff. I dont even know where to put that code let alone what it means. Thank you however for the advise.

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu then paste into the white space on the right

Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("O2"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For i = 2 To lastrow
        If .Range("O" & i).Value <> .Range("O" & i + 1).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Range("O" & iStart).Value
            On Error GoTo 0
            ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Press ALT + Q to close the code window, select your "master" sheet, press ALT + F8, click on Lapta then click the Run button.

 

[AdamB1977]

Run Time Error '1004'

The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.

I'm too simple for the code stuff. I was just hoping there was a formula already built into the software that said "if anything in this column equals one of these three things, copy and past it into one of these three different worksheets."

I probably need to get my hands on some training software of something like that.

 

[VoG]

A small correction (although the previous code worked when I tested it). Note that this code assumes that you have headings in row 1 and the data in rows 2 down. It will also probably fail if column O is merged with another column.

Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=.Range("O2"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For i = 2 To lastrow
        If .Range("O" & i).Value <> .Range("O" & i + 1).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Range("O" & iStart).Value
            On Error GoTo 0
            ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[AdamB1977]

Alright, so I figured out what I was doing wrong with your first code and it works very well with the exception of one thing. It creates the first three Sheets and are labeled correctly (CI, PA, HS), however the first value that it finds for PA goes into the PA worksheet, and all other values of PA go into Sheet5. Not a big deal as it does all the sorting for me and cuts down GREATLY on the amount of "Copy/Paste"