Run Time Error

[southcali12]

Hello,
I keep getting this error: Run-Time error '1004': Method 'Range' of object '_Global'failed

Help? The error pops up on the line in red.

Dim DataRng As Range
Dim c As Range


Application.ScreenUpdating = False


'Delete out unneeded columns
Columns("A:F").Select
Selection.Delete shift:=xlToLeft
Columns("O:O").Select
Selection.Delete shift:=xlToLeft
Columns("T:AE").Select
Selection.Delete shift:=xlToLeft
Columns("AF:AX").Select
Selection.Delete shift:=xlToLeft
Range("AE19").Select


Set NAOP = ActiveSheet


For Each c In Range([A2], Cells(Rows.Count, "AE").End(xlUp))
With c
.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A2:AE"), Unique:=True
.SpecialCells(xlCellTypeVisible).Copy
Sheets.Add(After:=Sheets(Sheets.Count)).name = c.Value
ActiveSheet.Paste
End With
Next c


Application.ScreenUpdating = True


End Sub

 

[RickXL]

Hi,

I am not quite sure what you are trying to do.

The loop:

For Each c In Range([A2], Cells(Rows.Count, "AE").End(xlUp))

will run for every cell in the range from column A to column AE for each row. So you will be adding a new worksheet for every individual cell in the range.

Perhaps if you say what you are trying to do in words we might be able to suggest some options.

Regards,

 

[Norie]

This is definitely wrong as you have no row for AE.

Range("A2:AE")

PS Is your criteria range really 31 columns wide?

 

[southcali12]

Hi RickXL,

So sorry! Been very scatterbrained today.

What I am trying to do is:
1) Filter the data in column A.
2) And for every unique datapoint, I would want a new workbook created and pasted over into the new workbook and saved with that datapoint as the title.
-- For example, in column A, the datapoints would be Group ABC, Group DEF, etc. From there I would want a workbook created for each group. So one workbook would have all the rows that have Group ABC, and then would also be saved as "Group ABC Invoice".

Please let me know if you need additional information.

 

[Norie]

First you need to do a advanced filter on column A to get a uniqe list of groups.

Then you need to loop through that list and do and advanced filter on the entire range in columns A:AE with each value from the unique list.

 

[southcali12]

I do unfortunately have 31 columns and that's after I've deleted more columns. To give an idea of how big the dataset is - it is originally up to column BT and has over 6000 rows of data. With over 40+ groups that I need to break into and copy and paste each group's data into a separate workbook. >.<

 

[Norie]

You are only filtering on one column though, A.

 

[southcali12]

Ah gotcha, Norie! How would I be able to set up the advanced filter on the entire range?