Macro across all my worksheets

[mlov83]

I've i have 3 worksheets that have about 64k lines and i like to run my macro on all the worksheets. Here is my code those anyone see what im doing wrong?

Dim ws As Worksheet
For Each ws In Worksheets

Columns("A:A").Select

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(21, 1), Array(30, 1), Array(59, 1), Array(66, 1), _
Array(71, 1), Array(98, 1), Array(103, 1)), TrailingMinusNumbers:=True



'sorts cells by the column defined in the range

Cells.Sort key1:=Range("b1")



'counts the number of rows in the spreadsheet

totalrows = ActiveSheet.UsedRange.Rows.Count







' starts at the last row and compares each row to the one above it

For Row = totalrows To 2 Step -1



' checks to see if the value in a specific cell is the same as the value

' in the cell above it; if so, then it will add the two amounts and

' then delete the duplicate row

' Note: Cells(X,Y) where X is the row number and Y is the column number



If Cells(Row, 2).Value = Cells(Row - 1, 2).Value Then

Cells(Row - 1, 6).Value = Cells(Row - 1, 6).Value + Cells(Row, 6).Value

Rows(Row).Delete

End If





Next Row



Next ws





End Sub

 

[GlennUK]

Post VBA queries in the VBA Visual Basic for Applications (Microsoft) Forum:

forum707

As for you question ... you are not referencing ws, the actual sheet object that is being generated per loop. Either use the onject with a With clause and change your code accordingley, or do ws.Activate at the top of the loop.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[mlov83]

Thank you glenn thats exactly what i needed sometimes a small detail scapes me and i spend endless hours working on a project. Also my apologies on putting this post on the wrong part forum.

 

[firefytr]

or do ws.Activate at the top of the loop.

No! Please, no!

No need to activate your sheets in order to work on them. Just reference them so with each range object.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci