VBA for moving entire row based on cell value to another worksheet

[Texasd]

Hi,

It has been 8 years since I have dabbled in Excel macros. So I need help! I am using the Excel for Auditors 2007 Edition so maybe that is my issue.

I have a report that list Reps in column G. I need to loop through all rows on the sheet, create a worksheet based on that value and move the rows from that sheet to the newly created sheet.

It works but nothing I do prevents the code from grabbing other reps. I have been staring at this for days and I admit I need assistance.

Prior to this, I create the sheet for the rep and that works fine. But I need for this loop to STOP when it gets to the next Rep and then execute the prior code again. and so on and so on. I have tried everything with my limited knowledge and ended up here. Don't laugh please hahahahaha! I have no clue if nesting a For loop into a While loop is even a thang! Any help would be greatly appreciated!

Rep and Val both point to the value in cell G. That was my workaround to stop the loop once it reached another rep but it is not working.

While Rep = Val
For ThisRow = 2 To FinalRow Step 1
Rep = Cells(ThisRow, 7)
NextRow = Worksheets(Val).Cells(1048576, 1).End(xlUp).Row
Cells(ThisRow, 1).Resize(1, 13).Copy Destination:=Worksheets(Val).Cells(NextRow + 1, 1)
Next ThisRow
Wend

 

[Osvaldo Palmeiro]

Hi.
If I've correctly understood what you're looking for, then the code below may be a starting point.
I've considered that each "rep" is a valid value to be assigned as a worksheet name.

Sub CreateRepsSheets()
 Dim ws As Worksheet, rep As Range
  Set ws = ActiveSheet
  For Each rep In ws.Range("G2:G" & ws.Cells(Rows.Count, 7).End(3).Row)
   Sheets.Add(after:=Sheets(Sheets.Count)).Name = rep.Value
   ws.Cells(rep.Row, 1).Resize(, 13).Copy Destination:=ActiveSheet.[A1]
   'ws.Cells(rep.Row, 1).Resize(, 13).Value = ""
  Next rep
End Sub

 

[Texasd]

Thank you SO MUCH for the response.

I tried the code but it quits running after the first row is moved to the first sheet because it is trying to make a sheet for every single row. There are 300 rows and some Reps have 100 rows and the other 4 have different amount of rows.

Here is where it stops because a sheet with that name is already present.

Sheets.Add(after:=Sheets(Sheets.Count)).Name = Rep.Value

 

[Texasd]

I actually tried something similar earlier. Maybe it would be easier if I created all of the sheets first and then moved the data on a new Sub.

 

[Osvaldo Palmeiro]

If the repeated "reps" are grouped in consecutive cells on column G, then this code could works.

Sub CreateRepsSheets()
 Dim ws As Worksheet, rep As Range
  Set ws = ActiveSheet
  For Each rep In ws.Range("G2:G" & ws.Cells(Rows.Count, 7).End(3).Row)
   If rep.Value <> rep.Offset(1).Value Then
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = rep.Value
    ws.Cells(rep.Row, 1).Resize(, 13).Copy Destination:=ActiveSheet.[A1]
    'ws.Cells(rep.Row, 1).Resize(, 13).Value = ""
   End If
  Next rep
End Sub