0

I've taken a few programming classes in college but am a complete novice at excel (this is my very first excel program). My boss asked me to create an excel program to track pie orders.

The first sheet is for entry where all information for an order is entered and you put an "x" in the column of the day the customer wants to pick-up the order. Once the "x" is entered the row gets copied to the corresponding day sheet as well as a master sheet and is then deleted from the entry sheet. As the row gets copied to the other sheets all rows get sorted by last name(column b). All of that works fine.

The issue is I need to have 2 rows of headers for the sheets the row gets copied to. The first row contains the names of the pies and other pertinent information about what that column means to the order. The second row needs to be a total that will update it's self for how many of each individual pie. With only 1 row of header it works fine but after adding in that second row I can't seem to get excel to not sort my second header row when the sheet is populated.

The kicker is I had this working 2 years ago and my boss deleted it. So I know it's possible but I just can't figure it out this time around no matter how much searching I do on this issue. Any help/ideas would be greatly appreciated!

Screen shot of entry sheet:

http://imgur.com/BsnOsZ0

Screen shot of Tuesday(a destination sheet):

http://imgur.com/nIkfqoQ Macro code on entry sheet:

Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False
 If Target.Column = 21 Then
    If Target.Value = "x" Then
        Target.EntireRow.Copy Destination:=Sheets("Tuesday"). _
        Range("A" & Rows.Count).End(xlUp).Offset(1)
        Target.EntireRow.Copy Destination:=Sheets("Master"). _
        Range("A" & Rows.Count).End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
 ElseIf Target.Column = 22 Then
     If Target.Value = "x" Then
        Target.EntireRow.Copy Destination:=Sheets("Wednesday"). _
        Range("A" & Rows.Count).End(xlUp).Offset(1)
        Target.EntireRow.Copy Destination:=Sheets("Master"). _
        Range("A" & Rows.Count).End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
  ElseIf Target.Column = 23 Then
     If Target.Value = "x" Then
        Target.EntireRow.Copy Destination:=Sheets("Thursday"). _
        Range("A" & Rows.Count).End(xlUp).Offset(1)
        Target.EntireRow.Copy Destination:=Sheets("Master"). _
        Range("A" & Rows.Count).End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
 End If
 Application.EnableEvents = True

 With Sheets("Tuesday")
.Columns("A:W").Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes
End With

With Sheets("Wednesday")
.Columns("A:W").Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes
End With

With Sheets("Thursday")
.Columns("A:W").Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes
End With

With Sheets("Master")
.Columns("A:W").Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes
End With



End Sub
  • Did you change your sort key range to "B2"? – Scott Holtzman Nov 22 '15 at 5:42
  • Can you figure out the last row in the sheet that contains data?  Try changing Columns("A:W").Sort to Range("A3:W999").Sort, and letting Header default to xlNo. – Scott Nov 22 '15 at 7:05
0

As Scott said, do not use A:W. Try it more like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long, sht As Variant
  sht = Array("Master", "Tuesday", "Wednesday", "Thursday")

  If Target.Column > 20 And Target.Column < 24 Then
    If Target.Value = "x" Then

      Application.EnableEvents = False

      Target.EntireRow.Copy Sheets(sht(Target.Column - 20)).Range("A" & Rows.Count).End(xlUp).Offset(1)
      Target.EntireRow.Copy Sheets(sht(0)).Range("A" & Rows.Count).End(xlUp).Offset(1)

      Application.EnableEvents = True

      For i = 0 To 4
        With Sheets(sht(i))
          .Range("A3:W" & .Cells(Rows.Count, 2).End(xlUp).Row).Sort .Cells(2, 1), 1
        End With
      Next
    End If
  End If
End Sub
| improve this answer | |
  • Thank you so much Scott and Dirk for your help! I actually got it working by using a workaround. In order to keep my second row of header all I needed was a blank space in the column being sorted, not empty but a space. However that only works if I don't need anything in that column header. As I mentioned in my original post this is my first attempt at vba macros and pieced much of it together from scavenging code on various forums and altering it to "make it work". – Sebler19 Nov 24 '15 at 3:04
  • I am excited to try out your suggestions and code so that I can make better programs in the future where I may need something in that second header row column I am sorting by. Once again thank you so much for helping the newbie! – Sebler19 Nov 24 '15 at 3:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.