• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro not working for sorting multiple worksheets

F

Foxy17

New Member
Hi, as you will see by the sample I've provided, the pages sort but column D get all mixed up . How can I ensure that all the data is sorted correctly. there is formula in Column D , which I belive may be causing the problem. Any advise would be appreciated Eccel 2007-10


Sub Comments()

Dim wsSheet As Worksheet


For Each wsSheet In Worksheets

Select Case wsSheet.CodeName

Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13"

With wsSheet.Range("A4:G151")

.Sort Key1:=.Range("G4"), Order1:=xlAscending, Header:=xlYes

End With

Case Else

'Nothing

End Select

Next wsSheet

End Sub
 
Foxy17


The code looks reasonable


Maybe you'd like to share the formula in Column D so we can check it out?
 
To


D E

Due On Completed

=IF('Voyage A'!E5=0,"",'Voyage A'!E5+30) 40945

=IF('Voyage A'!E6=0,"",'Voyage A'!E6+30) 40945

=IF('Voyage A'!E7=0,"",'Voyage A'!E7+30) 40946

=EDATE('Voyage A'!D8,6) =IF(D8>TODAY(),"Not Due","Due")

=IF('Voyage A'!E9=0,"",'Voyage A'!E9+30) 40948

=IF('Voyage A'!E10=0,"",'Voyage A'!E10+30) 40948

=IF('Voyage A'!E11=0,"",'Voyage A'!E11+30) 40948

=IF('Voyage A'!E12=0,"",'Voyage A'!E12+30) 40948

=IF('Voyage A'!E13=0,"",'Voyage A'!E13+30) 40948

=IF('Voyage A'!E14=0,"",'Voyage A'!E14+30) 40948

=IF('Voyage A'!E15=0,"",'Voyage A'!E15+30) 40948

=IF('Voyage A'!E16=0,"",'Voyage A'!E16+30) 40948

=IF('Voyage A'!E17=0,"",'Voyage A'!E17+30) 40955

=IF('Voyage A'!E18=0,"",'Voyage A'!E18+30) 40962

=IF('Voyage A'!E19=0,"",'Voyage A'!E19+30) 40969

=IF('Voyage A'!E20=0,"",'Voyage A'!E20+30) 40948

=IF('Voyage A'!E21=0,"",'Voyage A'!E21+30) 40948

=IF('Voyage A'!E22=0,"",'Voyage A'!E22+30) 40955

=IF('Voyage A'!E23=0,"",'Voyage A'!E23+30) 40962

=IF('Voyage A'!E24=0,"",'Voyage A'!E24+30) 40969

=IF('Voyage A'!E25=0,"",'Voyage A'!E25+30) 40948

=IF('Voyage A'!E26=0,"",'Voyage A'!E26+30) 40948

=IF('Voyage A'!E27=0,"",'Voyage A'!E27+30) 40948

=EDATE('Voyage A'!D28,3) =IF(D28>TODAY(),"Not Due","Due")

=IF('Voyage A'!E29=0,"",'Voyage A'!E29+30) 40948

=IF('Voyage A'!E30=0,"",'Voyage A'!E30+30) 40948

=IF('Voyage A'!E31=0,"",'Voyage A'!E31+30) 40948

=EDATE('Voyage A'!D32,3) =IF(D32>TODAY(),"Not Due","Due")

=IF('Voyage A'!E33=0,"",'Voyage A'!E33+30) 40948

=IF('Voyage A'!E34=0,"",'Voyage A'!E34+30) 40948

=IF('Voyage A'!E35=0,"",'Voyage A'!E35+30) 40948

=IF('Voyage A'!E36=0,"",'Voyage A'!E36+30) 40955

=IF('Voyage A'!E37=0,"",'Voyage A'!E37+30) 40962

=IF('Voyage A'!E38=0,"",'Voyage A'!E38+30) 40969

=IF('Voyage A'!E39=0,"",'Voyage A'!E39+30) 40949

=IF('Voyage A'!E40=0,"",'Voyage A'!E40+30) 40949

=IF('Voyage A'!E41=0,"",'Voyage A'!E41+30) 40949

=IF('Voyage A'!E42=0,"",'Voyage A'!E42+30) 40949

=IF('Voyage A'!E43=0,"",'Voyage A'!E43+30) 40949


I though you may see it with the example I've uploaded. I take it I've up loaded it correctly.


thanks for looking
 
Hi, Foxy17!


In this piece of code from your first post:

-----

With wsSheet.Range("A4:G151")

.Sort Key1:=.Range("G4"), Order1:=xlAscending, Header:=xlYes

End With

-----

you're specifing column G as sort criteria, assuming that you actually want to sort by column G, it'd be necessary to check both columns, D and G, so as to evaluate what's happening.


Consider uploading the file. Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!
 
Ive add 3 tags , I thought you could download the file from there, There ia a set of macros at the top of r=each page and they should sort the column the are above and everything else. ThaNKS AGAIN FOR LOOKING
 
http://www.2shared.com/fadmin/30553290/43f8f6dd/Copy_of_Deck_Planned_Maintenan.xlsm.html

http://speedy.sh/gRnpE/Copy-of-Deck-Planned-Maintenance
 
Hi, if you got to voyage A and sort by Job, then go to Voyage B check out the dates in row 7 both are Due On 7th and Completed on the 7th. If you click on Done By and scroll down to row 42 you will see that the dates have changed to Due On 9th and Completed on the 7th, thanks
 
Hi


I've shortened the file to show that it may not be the macro thats the problem , but more the formula. If you check sheets Voyage B and C you will see the highlighted date change, as you run through the macros for the sort. Hope you see what I'm speaking about


http://speedy.sh/TXqdS/Small-File.xlsm
 
You must log in or register to reply here.
Back
Top