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

Texasd

New Member
Joined
Mar 7, 2005
Messages
34
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
If the repeated "reps" are grouped in consecutive cells on column G, then this code could works.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top