Looping across worksheets

[Ramthebuffs]

I've run the macro recorder and seem to have found a way to remove duplicate rows pretty easily. What I would like to do is loop it through all the sheets in the workbook since the workbook is basically full. Anybody know how to loop this code?

Sub deletedupes()

'
Range("A1:N10000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AA1"), Unique:=True

Range("A:Z").Select
Selection.Delete Shift:=xlToLeft

End Sub

[anilsolipuram]

backup your workbook before trying this macro




Sub deletedupes()


For Each w In Worksheets
MsgBox w.Name
On Error Resume Next
w.Range("A1:N10000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AA1"), Unique:=True
If Err.Description <> "" Then
Err.Clear
GoTo a:
End If
w.Range("A:Z").Select
Selection.Delete Shift:=xlToLeft
a:

Next
End Sub

[Jim Thomlinson]

dim wks as worksheet

for each wks in worksheets
msgbox wks.name
next wks
--
HTH...

Jim Thomlinson


"Ramthebuffs" wrote:

>
> I've run the macro recorder and seem to have found a way to remove
> duplicate rows pretty easily. What I would like to do is loop it
> through all the sheets in the workbook since the workbook is basically
> full. Anybody know how to loop this code?
>
> Sub deletedupes()
>
> '
> Range("A1:N10000").AdvancedFilter Action:=xlFilterCopy,
> CopyToRange:=Range( _
> "AA1"), Unique:=True
>
> Range("A:Z").Select
> Selection.Delete Shift:=xlToLeft
>
> End Sub
>
>
> --
> Ramthebuffs
> ------------------------------------------------------------------------
> Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429
> View this thread: http://www.excelforum.com/showthread...hreadid=380354
>
>

[Ramthebuffs]

anilsolipuram,

That doesn't seem to work, it doesn't cycle through the sheets. It does end up deleting most of the info on whatever sheet I'm on. I'm using excel 2003, I'm not sure if that matters or not.

[anilsolipuram]

what message is it popping up, It should popup all the worksheet name.

[Ramthebuffs]

yes, each worksheet appears in the messagebox and I have to click ok.

If I happen to be in cell A1 then I can see data moving from left to right across the row 1 from the other sheets. Or if I'm in g10 the same thing happens.
I just want to run those couple of lines on each worksheet.

[Ramthebuffs]

sorry, meant right to left

[anilsolipuram]

Backup our workbook before trying this macro

Sub deletedupes()


For Each w In Worksheets
MsgBox w.Name
On Error Resume Next
w.Select
w.Range("A1:N10000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AA1:AN1"), Unique:=True
If Err.Description <> "" Then
Err.Clear
GoTo a:
End If
w.Range("A:Z").Select
Selection.Delete Shift:=xlToLeft
a:

Next
End Sub

[Ramthebuffs]

Worked great, thanks.