parsing data

disneyfreak

New Member
Joined
Oct 8, 2014
Messages
1
Have a macro that I just can't seem to get to work. I need to create seperate worksheets based on values in Column V and copy the data from each unique item to it's respective sheet. The macro below does create the new sheet but it does not copy the data and it errors out ShNew.Name = Item

Any assistance is greatly appreciated.


Sub parse()
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant
Dim ShNew As Worksheet
Application.ScreenUpdating = False
'
Set Sh = Worksheets("Staging")
Set Rng = Sh.Range("V2:V" & Sh.Range("V65536").End(xlUp).Row)
On Error Resume Next
For Each c In Rng
List.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
Set Rng = Sh.Range("a1:w" & Sh.Range("Dw65536").End(xlUp).Row)
For Each Item In List
Set ShNew = Worksheets.Add
ShNew.Name = Item
Rng.AutoFilter Field:=1, Criteria1:=Item
Rng.EntireRow.SpecialCells(xlCellTypeVisible).copy ShNew.Range("A1")
Rng.AutoFilter
Next Item
Sh.Activate
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Welcome to the Board!

There are some requirements for naming sheets. Namely:
- the name cannot be blank
- no sheet names can be duplicated
- most special characters are not allowed in sheet names

So, what is the value of Item when this error occurs?
If you hover over the word when debugging when the error occurs, it should show you the value.
 
Upvote 0
What error message do you get? What is the value of Item when the error occurs? Is there already a sheet with that name? Do any of your Items contain characters not permitted in a sheet name (e.g."/",...)?
 
Upvote 0
Set ShNew = Worksheets.Add
ShNew.Name = Item

You've just defined ShNew as the process of adding a new worksheet, so when vba tries to perform the next line's action, what you're actually typing is "Worksheets.Add.Name = Item", which is not correct.

Maybe try something like:

Code:
Sub test()

Dim shNew     As Worksheet

Worksheets.Add(After:=Worksheets(1)).Name = "Item"
Set shNew = ActiveSheet

End sub

Modify the After or Before as you see fit to add this to a particular spot in your workbook. Then once it's created, it will become the active worksheet, then you just define ShNew as the activesheet.
 
Upvote 0
I ran your macro: I copied your sub in, made a button, assigned the sub to it, put 4 short words in cells V2:V6, named sheet1 Staging and pushed the button. 4 sheets were created; one for each word. Each word had no numbers or special characters. There was nothing else on the sheet such as filters. I am using excel 2010.

Put an error catcher in your macro: near the start:

on error goto errcatch:

at the end:

exit sub
errcatch:
application.screenupdating=true
msgbox(err.number)
on error goto 0

Looking up the specific error numbers can be helpful.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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