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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,209
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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."/",...)?
 

lordterrin

Board Regular
Joined
Mar 21, 2012
Messages
155
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.
 

krausr79

Board Regular
Joined
Feb 12, 2012
Messages
209
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,203
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top