MACRO - Moving certain rows into new sheets!!

February 9, 2011 at 15:05:21
Specs: Windows XP

I am hopeless and not familiar with macro at all. I've been online for hours trying to figure this out but I need a lot of help. I have an excel spreadsheet named Master with the following information.
1 Last name First name Email Phone Rating Interests Date Pro Follow Up
2 Hill Jack ... ... M 4.0 ... ... ... ...
3 Hill Jill ... ... W 2.5 ... ... ... ...

and so on. I have about 200 rows and will be adding more constantly. The column I care about is E (Rating). I want to make a new spreadsheet based on every rating which will be ...
W 2.5, W 3.0, W 3.5, W 4.0, W 4.5, W 5.0+ and then 6 more but with M instead of W (M 2.5 ... M 5.0+). I want the new spreadsheets in the same workbook and named after the Rating. So all the W 2.5 would be in the 'W 2.5' sheet along with all the information in their row.

How can I do this? I am a total noob, don't even understand how to set up a macro and enter code or anything. Is anyone willing to help? I know it's simple and I've tried twice with other code and just don't get it. It's for my new job and they need it ASAP.

Also, if you have enough time, is there a way to actually exclude some of the columns of information in the new spreadsheets? I would only want columns A, B, C, D, E, and F to be copied over. But if that's super complicated it's fine the original way.

And I'll ask before case I type in W 2.3 (an invalid rating) can whatever code provided STILL make a new spreadsheet based on that rating? I'd like this to happen so I can see spreadsheets with accidentally incorrectly typed ratings and correct them vs it being kept in the Master sheet and never sorted. Sorry if this is of course going to be done. I know nothing about coding this stuff.

Thanks for saving my life/job in advance!! and could I see comments by the code? I want to learn! Just don't have time right this second.

See More: MACRO - Moving certain rows into new sheets!!

Report •

February 9, 2011 at 17:07:41
re: I am hopeless

If you were hopeless, you wouldn't have come here looking for help. You would have just given up. You came here hopeful that you would get some help.

re: in case I type in W 2.3 (an invalid rating)

Create a Data Validation Drop Down list that only contains valid ratings so that you don't have to type anything in Column E. Just use the Drop Down to select the rating and eliminate the chance of errors.

This code should do what you are looking for:

Option Explicit
Sub RateTabs()
Dim wsSheet As Worksheet
Dim lastRate, rTab, nxtRrw As Integer
Dim rateName As String
'Determine Last Row in Rates List
lastRate = Sheets(1).Range("E" & Rows.Count).End(xlUp).Row
'Loop Through Rates List, Create Sheet and
'Copy Row 1 (Labels) If Sheet Doesn't Exist
For rTab = 2 To lastRate
Set wsSheet = Nothing
rateName = Sheets(1).Range("E" & rTab).Value
On Error Resume Next
Set wsSheet = Sheets(rateName)
On Error GoTo 0
If wsSheet Is Nothing Then
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = rateName
Sheets(1).Rows("1:1").Copy _
End If
'Copy Rows To Next Open Row On Corresponding Rate Tab
For rTab = 2 To lastRate
rateName = Sheets(1).Range("E" & rTab).Value
nxtRrw = Sheets(rateName).Range("E" & Rows.Count).End(xlUp).Row + 1
Range("A" & rTab & ":F" & rTab).Copy _
Destination:=Sheets(rateName).Range("A" & nxtRrw)
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 10, 2011 at 13:46:34
Thanks so much for the validation suggestion. That'll help a lot!

So I've done that and copied the code and tried to run it but I keep getting and error saying...

Runtime '1004'
application-defined or object-defined error

It's for the line that reads ActiveSheet.Name = rateName

What should I do?

Also, I have a lot of blank ratings because I have to fill them in as I go and call people back or create new rows for new people. How will this affect anything? Will it make a new sheet with all the people with blank ratings? That would be awesome.

Report •

February 10, 2011 at 18:43:49
If Column E has any blank cells, then the code will fail when it tries to create a sheet with "no name".

Since you asked for the sheets to be named based on the values in Column E, that what the code was written to do. Since a sheet name can't be blank, the code fails and throws up an error.

Since I can't see your spreadsheet from where I'm sitting, I can only assume that that is the reason for the error.

re: "Will it make a new sheet with all the people with blank ratings?"

For the reason stated above, no.

However, if you put something in all of the blank cells that VBA can use as a sheet name (e.g. Not Rated) then the code should produce a sheet named Not Rated and copy all of the corresponding ranges to that sheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

February 23, 2011 at 11:34:06
I'm doing a very similar project and this code would be perfect. The only problem is that I keep getting the same error that OP had.

"Runtime '1004'
application-defined or object-defined error

It's for the line that reads ActiveSheet.Name = rateName"

It wont let me set the sheet name = to the the variable. If I replace rateName with "test", it renames the sheet "test", but further code stops working.

I also posted my full problem here:

Report •

August 14, 2011 at 03:15:10
This macro is great!!! Thank you so much for sharing this!!!!!!

Is there any possibility that the macro does not overwrite existing sheets, but creates new ones?

For example my excel file has already 5 sheets. when I run the code for sheet 1, sheet 2, 3 etc are overwritten with the new results, is it possible that the create new sheets beginning from sheet 6...

Thank you!

Report •

September 9, 2011 at 00:43:00
I am kind of in the same situation I am a total Newby and I need a more complex macro to run my daily reports that usually take FOREVER!! Basically, I have about 200 columns and my rows go from A-M. I have 7 spread sheets and I need to get the information cut from spread sheet 1 onto the designated spreadsheet I create. For example. I need all rows with Codes: SBO, AWR and KR from column G to be cut and pasted into Sheet 2. I need All rows with code KR from column G into sheet 3, I need all rows with code 850 in row J in sheet 4 and so on and so on until sheet 1 is completely empty. My reports are different each day so I need a macro that can read the codes and manually place them in their corresponding sheet. PLEASE HELP :(

Report •

Ask Question