Macro Not Working Right... Can someone help?

B

belly0fdesire

New Member
Joined
Sep 6, 2005
Messages
39
I created this beautiful spreadsheet to manage time for my office, for which someone on here helped me make a macro. It's in the code for the sheet entitled "Summary". The user is supposed to make changes in the name or department on the Summary sheet and the row is supposed to change color according to the department. Also, the sheet is supposed to re-sort by department and then by name. Also, the sheet for every month is supposed to sort accordingly.

I thought the sheet was sorting okay, but now that I have data entered (time) in the individual month sheets, when I make a change on Summary and the name moves on the list when it is automatically sorted, the time that goes with that name remains in the same place and is not sorted. I have uploaded a copy of the spreadsheet here... if anyone could just take a look at it and let me know what's wrong with it, that would be great. Thank you.


If link above does not work try this one
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Maybe ppl are just overly cautious on these boards, which is more than understandable... I have included the code below, which may help me get a response, but I'm not sure my question can be answered without seeing the workbook... but maybe this will help. Thank you again.


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iClr As Integer

On Error Resume Next

If Target.Count = 1 And (Target.Column = 2 Or Target.Column = 6) Then
    Range("A6:O120").Sort Key1:=Range("F6"), Order1:=xlAscending, Key2:=Range("B6") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
       
    'All months
Sheets("Jan").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Jan!B7,Summary!B:B,0))"
Sheets("Jan").Range("a7").AutoFill Destination:=Sheets("Jan").Range("a7:a121")
Sheets("Jan").Range("a7:a121").Value = Sheets("Jan").Range("a7:a121").Value
Sheets("Jan").Rows("7:121").Sort Key1:=Sheets("Jan").Range("a7")
Sheets("Feb").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Feb!B7,Summary!B:B,0))"
Sheets("Feb").Range("a7").AutoFill Destination:=Sheets("Feb").Range("a7:a121")
Sheets("Feb").Range("a7:a121").Value = Sheets("Feb").Range("a7:a121").Value
Sheets("Feb").Rows("7:121").Sort Key1:=Sheets("Feb").Range("a7")
Sheets("Mar").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Mar!B7,Summary!B:B,0))"
Sheets("Mar").Range("a7").AutoFill Destination:=Sheets("Mar").Range("a7:a121")
Sheets("Mar").Range("a7:a121").Value = Sheets("Mar").Range("a7:a121").Value
Sheets("Mar").Rows("7:121").Sort Key1:=Sheets("Mar").Range("a7")
Sheets("Apr").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Apr!B7,Summary!B:B,0))"
Sheets("Apr").Range("a7").AutoFill Destination:=Sheets("Apr").Range("a7:a121")
Sheets("Apr").Range("a7:a121").Value = Sheets("Apr").Range("a7:a121").Value
Sheets("Apr").Rows("7:121").Sort Key1:=Sheets("Apr").Range("a7")
Sheets("May").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(May!B7,Summary!B:B,0))"
Sheets("May").Range("a7").AutoFill Destination:=Sheets("May").Range("a7:a121")
Sheets("May").Range("a7:a121").Value = Sheets("May").Range("a7:a121").Value
Sheets("May").Rows("7:121").Sort Key1:=Sheets("May").Range("a7")
Sheets("Jun").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Jun!B7,Summary!B:B,0))"
Sheets("Jun").Range("a7").AutoFill Destination:=Sheets("Jun").Range("a7:a121")
Sheets("Jun").Range("a7:a121").Value = Sheets("Jun").Range("a7:a121").Value
Sheets("Jun").Rows("7:121").Sort Key1:=Sheets("Jun").Range("a7")
Sheets("Jul").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Jul!B7,Summary!B:B,0))"
Sheets("Jul").Range("a7").AutoFill Destination:=Sheets("Jul").Range("a7:a121")
Sheets("Jul").Range("a7:a121").Value = Sheets("Jul").Range("a7:a121").Value
Sheets("Jul").Rows("7:121").Sort Key1:=Sheets("Jul").Range("a7")
Sheets("Aug").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Aug!B7,Summary!B:B,0))"
Sheets("Aug").Range("a7").AutoFill Destination:=Sheets("Aug").Range("a7:a121")
Sheets("Aug").Range("a7:a121").Value = Sheets("Aug").Range("a7:a121").Value
Sheets("Aug").Rows("7:121").Sort Key1:=Sheets("Aug").Range("a7")
Sheets("Sep").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Sep!B7,Summary!B:B,0))"
Sheets("Sep").Range("a7").AutoFill Destination:=Sheets("Sep").Range("a7:a121")
Sheets("Sep").Range("a7:a121").Value = Sheets("Sep").Range("a7:a121").Value
Sheets("Sep").Rows("7:121").Sort Key1:=Sheets("Sep").Range("a7")
Sheets("Oct").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Oct!B7,Summary!B:B,0))"
Sheets("Oct").Range("a7").AutoFill Destination:=Sheets("Oct").Range("a7:a121")
Sheets("Oct").Range("a7:a121").Value = Sheets("Oct").Range("a7:a121").Value
Sheets("Oct").Rows("7:121").Sort Key1:=Sheets("Oct").Range("a7")
Sheets("Nov").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Nov!B7,Summary!B:B,0))"
Sheets("Nov").Range("a7").AutoFill Destination:=Sheets("Nov").Range("a7:a121")
Sheets("Nov").Range("a7:a121").Value = Sheets("Nov").Range("a7:a121").Value
Sheets("Nov").Rows("7:121").Sort Key1:=Sheets("Nov").Range("a7")
Sheets("Dec").Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(Dec!B7,Summary!B:B,0))"
Sheets("Dec").Range("a7").AutoFill Destination:=Sheets("Dec").Range("a7:a121")
Sheets("Dec").Range("a7:a121").Value = Sheets("Dec").Range("a7:a121").Value
Sheets("Dec").Rows("7:121").Sort Key1:=Sheets("Dec").Range("a7")
End If

'colors start here
If Target.Column <> 6 Or Target.Count > 1 Then Exit Sub
Select Case Target.Value
    Case "Admin"
        iClr = 2
    Case "C&I - Dual Seat"
        iClr = 3
    Case "C&I - Typing"
        iClr = 3
    Case "Engineering"
        iClr = 46
    Case "Packaging"
        iClr = 6
    Case "Plant"
        iClr = 4
    Case "Policy"
        iClr = 5
    Case "Resale - Exam"
        iClr = 8
    Case "Resale - Search"
        iClr = 8
    Case "Resale - Type"
        iClr = 8
    Case "Single Seat - SL"
        iClr = 40
    Case "SD - Dual Seat"
        iClr = 15
    Case "SD - Type"
        iClr = 15
    Case "SD - Other"
            iClr = 15
    Case "Order Needs"
        iClr = 7
    Case Else
        iClr = -4142 'no color
End Select

Target.Offset(0, -4).Interior.ColorIndex = iClr

End Sub
 
Upvote 0
Hi,

I don't have much time today, but will try and look at it tonight or tomorrow morning, unless somebody else gets to it first. Don't want you to have to go unanswered.. :)
 
Upvote 0
Zack, it is about 10 clicks north of Sphincter Lake.

I may have a chance to sort this for you this evening, but I have to be unavailable for about 2 hours....will post back if I nail it down when I get home....unless again, someone else gets there first...

no worries, someone will get you sorted...
 
Upvote 0
In the interim, where the hell is Craphole Island? :eek:
Click to expand...

heheh... Craphole Island is a Lost reference. It's what Shannon (now dead) called her new home, the island. I should change it. I'm in Southern California.
 
Upvote 0
Okay... in looking at my macros and formulas, I can understand why I am not accomplishing my goal.

I'm continuing to try to figure out the code I need in order to make this workbook sort the way I want it to, but I'm sure I'm going to need some help. When a change occurs in column B of the "Summary" sheet, I want the same change to occur on Jan:Dec of the months. And then I want all the sheets to sort accordingly. The way I have the spreadsheet set up as it is, this will not happen. Please if anyone has time to look at this and figure out the code I need, it would be extremely appreciated.

Until then, I will continue trying to figure this out on my own and will post my findings if ever I am successful....... but I doubt I will be on my own.

Thanks for anyone who is helping me.
 
Upvote 0
You need to unprotect your sheets.

Change your Summary sheet code to this ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> iClr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Count = 1 And (Target.Column = 2 <SPAN style="color:#00007F">Or</SPAN> Target.Column = 6) <SPAN style="color:#00007F">Then</SPAN>
        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
        Application.Calculation = xlCalculationManual
        <SPAN style="color:#00007F">Call</SPAN> UnprotectSheets
        Me.Range("A5:O120").Sort Key1:=Range("F6"), _
            Key2:=Me.Range("B6"), Header:=xlYes
        Sheets("Jan").Rows("7:121").Sort Key1:=Sheets("Jan").Range("a7"), Header:=xlNo
        Sheets("Feb").Rows("7:121").Sort Key1:=Sheets("Feb").Range("a7"), Header:=xlNo
        Sheets("Mar").Rows("7:121").Sort Key1:=Sheets("Mar").Range("a7"), Header:=xlNo
        Sheets("Apr").Rows("7:121").Sort Key1:=Sheets("Apr").Range("a7"), Header:=xlNo
        Sheets("May").Rows("7:121").Sort Key1:=Sheets("May").Range("a7"), Header:=xlNo
        Sheets("Jun").Rows("7:121").Sort Key1:=Sheets("Jun").Range("a7"), Header:=xlNo
        Sheets("Jul").Rows("7:121").Sort Key1:=Sheets("Jul").Range("a7"), Header:=xlNo
        Sheets("Aug").Rows("7:121").Sort Key1:=Sheets("Aug").Range("a7"), Header:=xlNo
        Sheets("Sep").Rows("7:121").Sort Key1:=Sheets("Sep").Range("a7"), Header:=xlNo
        Sheets("Oct").Rows("7:121").Sort Key1:=Sheets("Oct").Range("a7"), Header:=xlNo
        Sheets("Nov").Rows("7:121").Sort Key1:=Sheets("Nov").Range("a7"), Header:=xlNo
        Sheets("Dec").Rows("7:121").Sort Key1:=Sheets("Dec").Range("a7"), Header:=xlNo
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Column <> 6 <SPAN style="color:#00007F">Or</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Value
    <SPAN style="color:#00007F">Case</SPAN> "Admin": iClr = 2
    <SPAN style="color:#00007F">Case</SPAN> "C&I - Dual Seat": iClr = 3
    <SPAN style="color:#00007F">Case</SPAN> "C&I - Typing": iClr = 3
    <SPAN style="color:#00007F">Case</SPAN> "Engineering": iClr = 46
    <SPAN style="color:#00007F">Case</SPAN> "Packaging": iClr = 6
    <SPAN style="color:#00007F">Case</SPAN> "Plant": iClr = 4
    <SPAN style="color:#00007F">Case</SPAN> "Policy": iClr = 5
    <SPAN style="color:#00007F">Case</SPAN> "Resale - Exam": iClr = 8
    <SPAN style="color:#00007F">Case</SPAN> "Resale - Search": iClr = 8
    <SPAN style="color:#00007F">Case</SPAN> "Resale - Type": iClr = 8
    <SPAN style="color:#00007F">Case</SPAN> "Single Seat - SL": iClr = 40
    <SPAN style="color:#00007F">Case</SPAN> "SD - Dual Seat": iClr = 15
    <SPAN style="color:#00007F">Case</SPAN> "SD - Type": iClr = 15
    <SPAN style="color:#00007F">Case</SPAN> "SD - Other": iClr = 15
    <SPAN style="color:#00007F">Case</SPAN> "Order Needs": iClr = 7
    <SPAN style="color:#00007F">Case</SPAN> Else: iClr = -4142 <SPAN style="color:#007F00">'no color</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    Target.Offset(0, -4).Interior.ColorIndex = iClr
ExitSub:
    <SPAN style="color:#00007F">Call</SPAN> ProtectSheets
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    Application.Calculation = xlCalculationAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Then get rid of all your Standard modules except Module1 and replace all code in there with this ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> UnprotectSheets()
    ThisWorkbook.Sheets("Summary").Unprotect
    ThisWorkbook.Sheets("Jan").Unprotect
    ThisWorkbook.Sheets("Feb").Unprotect
    ThisWorkbook.Sheets("Mar").Unprotect
    ThisWorkbook.Sheets("Apr").Unprotect
    ThisWorkbook.Sheets("May").Unprotect
    ThisWorkbook.Sheets("Jun").Unprotect
    ThisWorkbook.Sheets("Jul").Unprotect
    ThisWorkbook.Sheets("Aug").Unprotect
    ThisWorkbook.Sheets("Sep").Unprotect
    ThisWorkbook.Sheets("Oct").Unprotect
    ThisWorkbook.Sheets("Nov").Unprotect
    ThisWorkbook.Sheets("Dec").Unprotect
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ProtectSheets()
    ThisWorkbook.Sheets("Summary").Protect
    ThisWorkbook.Sheets("Jan").Protect
    ThisWorkbook.Sheets("Feb").Protect
    ThisWorkbook.Sheets("Mar").Protect
    ThisWorkbook.Sheets("Apr").Protect
    ThisWorkbook.Sheets("May").Protect
    ThisWorkbook.Sheets("Jun").Protect
    ThisWorkbook.Sheets("Jul").Protect
    ThisWorkbook.Sheets("Aug").Protect
    ThisWorkbook.Sheets("Sep").Protect
    ThisWorkbook.Sheets("Oct").Protect
    ThisWorkbook.Sheets("Nov").Protect
    ThisWorkbook.Sheets("Dec").Protect
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> Macro1()
    Columns("F:I").EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN>
    Columns("B").EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> Macro2()
    Columns("F:I").EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


HTH
 
Upvote 0
You must log in or register to reply here.

Similar threads

Data123
  • Solved
Symbol list in Spreadsheet
Replies
7
Views
274
Peter_SSs
Peter_SSs
O
  • Solved
unique, sort, top 5
Replies
6
Views
170
Fluff
Fluff
S
  • Solved
VBL loop help
Replies
3
Views
150
mse330
M
H
  • Solved
How to copy data from one file and paste it into a dynamically named workbook
Replies
3
Views
244
dermie_72
D
spacecaptainsuperguy
  • Question
Advice for linked files on network drive
Replies
0
Views
362
spacecaptainsuperguy
spacecaptainsuperguy

Forum statistics

Threads
1,215,660
Messages
6,126,089
Members
449,288
Latest member
DjentChicken

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top