Need help with Macro

[DKRbella0814]

I have designed a workbook with two worksheets as follows:

Sheet 1 (PO)
Sheet 2 (Form)

In Sheet 1, specific data elements are captured in rows and the same data elements repeat every 9 columns starting at column N or column 14.

I have written a macro, which is below, to pull the data elements from Sheet 1 and then automatically print on Sheet 2 (Form).

I want to include code to automatically sort the data on Sheet2 (Form) beginning in the range A24 to J48. The header rows are in A22: J22 (quantity, title, genre, unit price, total price, account no). Please review the macro below...I have bolded the lines of code that I added to sort the data, but it is not returning all of the correct information. For some reason, the macro is returning data in cells A23:J23, which it should not..this entire row should be blank.

Please help!

Sub POLog()
'
' POLog Macro
' Keyboard Shortcut: Ctrl+r
' Dim X As Integer
Dim N As Integer

Dim PO As Worksheet
Dim FORM As Worksheet

Set PO = Worksheets("PO")
Set FORM = Worksheets("FORM")

PO.Activate

N = ActiveCell.Row

For X = 1 To 239
Select Case X
Case 1
FORM.Cells(1, 19) = PO.Cells(N, 1) 'PO
Case 2
FORM.Cells(1, 20) = PO.Cells(N, 2) 'PO
Case 3
FORM.Cells(15, 9) = PO.Cells(N, 3) 'DATE
Case 4
FORM.Cells(12, 3) = PO.Cells(N, 4) 'VENDOR
Case 5
FORM.Cells(13, 3) = PO.Cells(N, 5) 'ADDRESS
Case 6
FORM.Cells(14, 3) = PO.Cells(N, 6) 'CITY
Case 7
FORM.Cells(14, 4) = PO.Cells(N, 7) 'STATE
Case 8
FORM.Cells(14, 5) = PO.Cells(N, 8) 'ZIP
Case 9
FORM.Cells(4, 2) = PO.Cells(N, 9) 'PHONE NO.
Case 10
FORM.Cells(16, 4) = PO.Cells(N, 10) 'ATTN:
Case 11
FORM.Cells(21, 5) = PO.Cells(N, 11) 'SHIP VIA
Case 12
FORM.Cells(21, 2) = PO.Cells(N, 12) 'DATE REQ.
Case 13
FORM.Cells(52, 3) = PO.Cells(N, 13) 'REQ. BY.
Case 14
FORM.Cells(58, 1) = PO.Cells(N, 14) 'line item 1
Case 15
FORM.Cells(24, 4) = PO.Cells(N, 15) 'Title
Case 16
FORM.Cells(24, 2) = PO.Cells(N, 16) 'Genre
Case 17
FORM.Cells(24, 1) = PO.Cells(N, 18) 'QTY
Case 18
FORM.Cells(24, 8) = PO.Cells(N, 19) 'UNIT$
Case 19
FORM.Cells(24, 9) = PO.Cells(N, 20) 'Total Cost
Case 20
FORM.Cells(24, 10) = PO.Cells(N, 21) 'ACCOUNT NO.
Case 21
FORM.Cells(58, 2) = PO.Cells(N, 22) 'COMMENT 1
Case 22
FORM.Cells(59, 1) = PO.Cells(N, 23) 'line item 2
Case 23
FORM.Cells(25, 4) = PO.Cells(N, 24) 'Title
Case 24
FORM.Cells(25, 2) = PO.Cells(N, 25) 'Genre
Case 25
FORM.Cells(25, 1) = PO.Cells(N, 27) 'QTY
Case 26
FORM.Cells(25, 8) = PO.Cells(N, 28) 'UNIT$
Case 27
FORM.Cells(25, 9) = PO.Cells(N, 29) 'Total Cost
Case 28
FORM.Cells(25, 10) = PO.Cells(N, 30) 'ACCOUNT NO.
Case 29
FORM.Cells(59, 2) = PO.Cells(N, 31) 'COMMENT 2
Case 30
FORM.Cells(60, 1) = PO.Cells(N, 32) 'line item 3
Case 31
FORM.Cells(26, 4) = PO.Cells(N, 33) 'Title
Case 32
FORM.Cells(26, 2) = PO.Cells(N, 34) 'Genre
Case 33
FORM.Cells(26, 1) = PO.Cells(N, 36) 'QTY
Case 34
FORM.Cells(26, 8) = PO.Cells(N, 37) 'UNIT$
Case 35
FORM.Cells(26, 9) = PO.Cells(N, 38) 'Total Cost
Case 36
FORM.Cells(26, 10) = PO.Cells(N, 39) 'ACCOUNT NO.
Case 37
FORM.Cells(60, 2) = PO.Cells(N, 40) 'COMMENT 3
Case 38
FORM.Cells(61, 1) = PO.Cells(N, 41) 'line item 4
Case 39
FORM.Cells(27, 4) = PO.Cells(N, 42) 'Title
Case 40
FORM.Cells(27, 2) = PO.Cells(N, 43) 'Genre
Case 41
FORM.Cells(27, 1) = PO.Cells(N, 45) 'QTY
Case 42
FORM.Cells(27, 8) = PO.Cells(N, 46) 'UNIT$
Case 43
FORM.Cells(27, 9) = PO.Cells(N, 47) 'Total Cost
Case 44
FORM.Cells(27, 10) = PO.Cells(N, 48) 'ACCOUNT NO.
Case 45
FORM.Cells(61, 2) = PO.Cells(N, 49) 'COMMENT 4
Case 46
FORM.Cells(62, 1) = PO.Cells(N, 50) 'line item 5
Case 47
FORM.Cells(28, 4) = PO.Cells(N, 51) 'Title
Case 48
FORM.Cells(28, 2) = PO.Cells(N, 52) 'Genre
Case 49
FORM.Cells(28, 1) = PO.Cells(N, 54) 'QTY
Case 50
FORM.Cells(28, 8) = PO.Cells(N, 55) 'UNIT$
Case 51
FORM.Cells(28, 9) = PO.Cells(N, 56) 'Total Cost
Case 52
FORM.Cells(28, 10) = PO.Cells(N, 57) 'ACCOUNT NO.
Case 53
FORM.Cells(62, 2) = PO.Cells(N, 58) 'COMMENT 5
Case 54
FORM.Cells(63, 1) = PO.Cells(N, 59) 'line item 6
Case 55
FORM.Cells(29, 4) = PO.Cells(N, 60) 'Title
Case 56
FORM.Cells(29, 2) = PO.Cells(N, 61) 'Genre
Case 57
FORM.Cells(29, 1) = PO.Cells(N, 63) 'QTY
Case 58
FORM.Cells(29, 8) = PO.Cells(N, 64) 'UNIT$
Case 59
FORM.Cells(29, 9) = PO.Cells(N, 65) 'Total Cost
Case 60
FORM.Cells(29, 10) = PO.Cells(N, 66) 'ACCOUNT NO.
Case 61
FORM.Cells(63, 2) = PO.Cells(N, 67) 'COMMENT 6
Case 62
FORM.Cells(64, 1) = PO.Cells(N, 68) 'line item 7
Case 63
FORM.Cells(30, 4) = PO.Cells(N, 69) 'Title
Case 64
FORM.Cells(30, 2) = PO.Cells(N, 70) 'Genre
Case 65
FORM.Cells(30, 1) = PO.Cells(N, 72) 'QTY
Case 66
FORM.Cells(30, 8) = PO.Cells(N, 73) 'UNIT$
Case 67
FORM.Cells(30, 9) = PO.Cells(N, 74) 'Total Cost
Case 68
FORM.Cells(30, 10) = PO.Cells(N, 75) 'ACCOUNT NO.
Case 69
FORM.Cells(64, 2) = PO.Cells(N, 76) 'COMMENT 7
Case 70
FORM.Cells(65, 1) = PO.Cells(N, 77) 'line item 8
Case 71
FORM.Cells(31, 4) = PO.Cells(N, 78) 'Title
Case 72
FORM.Cells(31, 2) = PO.Cells(N, 79) 'Genre
Case 73
FORM.Cells(31, 1) = PO.Cells(N, 81) 'QTY
Case 74
FORM.Cells(31, 8) = PO.Cells(N, 82) 'UNIT$
Case 75
FORM.Cells(31, 9) = PO.Cells(N, 83) 'Total Cost
Case 76
FORM.Cells(31, 10) = PO.Cells(N, 84) 'ACCOUNT NO.
Case 77
FORM.Cells(65, 2) = PO.Cells(N, 85) 'COMMENT 8
Case 78
FORM.Cells(66, 1) = PO.Cells(N, 86) 'line item 9
Case 79
FORM.Cells(32, 4) = PO.Cells(N, 87) 'Title
Case 80
FORM.Cells(32, 2) = PO.Cells(N, 88) 'Genre
Case 81
FORM.Cells(32, 1) = PO.Cells(N, 90) 'QTY
Case 82
FORM.Cells(32, 8) = PO.Cells(N, 91) 'UNIT$
Case 83
FORM.Cells(32, 9) = PO.Cells(N, 92) 'Total Cost
Case 84
FORM.Cells(32, 10) = PO.Cells(N, 93) 'ACCOUNT NO.
Case 85
FORM.Cells(66, 2) = PO.Cells(N, 94) 'COMMENT 9
Case 86
FORM.Cells(67, 1) = PO.Cells(N, 95) 'line item 10
Case 87
FORM.Cells(33, 4) = PO.Cells(N, 96) 'Title
Case 88
FORM.Cells(33, 2) = PO.Cells(N, 97) 'Genre
Case 89
FORM.Cells(33, 1) = PO.Cells(N, 99) 'QTY
Case 90
FORM.Cells(33, 8) = PO.Cells(N, 100) 'UNIT$
Case 91
FORM.Cells(33, 9) = PO.Cells(N, 101) 'Total Cost
Case 92
FORM.Cells(33, 10) = PO.Cells(N, 102) 'ACCOUNT NO.
Case 93
FORM.Cells(67, 2) = PO.Cells(N, 103) 'COMMENT 10
Case 94
FORM.Cells(68, 1) = PO.Cells(N, 104) 'line item 11
Case 95
FORM.Cells(34, 4) = PO.Cells(N, 105) 'Title
Case 96
FORM.Cells(34, 2) = PO.Cells(N, 106) 'Genre
Case 97
FORM.Cells(34, 1) = PO.Cells(N, 108) 'QTY
Case 98
FORM.Cells(34, 8) = PO.Cells(N, 109) 'UNIT$
Case 99
FORM.Cells(34, 9) = PO.Cells(N, 110) 'Total Cost
Case 100
FORM.Cells(34, 10) = PO.Cells(N, 111) 'ACCOUNT NO.
Case 101
FORM.Cells(68, 2) = PO.Cells(N, 112) 'COMMENT 11
Case 102
FORM.Cells(69, 1) = PO.Cells(N, 113) 'line item 12
Case 103
FORM.Cells(35, 4) = PO.Cells(N, 114) 'Title
Case 104
FORM.Cells(35, 2) = PO.Cells(N, 115) 'Genre
Case 105
FORM.Cells(35, 1) = PO.Cells(N, 117) 'QTY
Case 106
FORM.Cells(35, 8) = PO.Cells(N, 118) 'UNIT$
Case 107
FORM.Cells(35, 9) = PO.Cells(N, 119) 'Total Cost
Case 108
FORM.Cells(35, 10) = PO.Cells(N, 120) 'ACCOUNT NO.
Case 109
FORM.Cells(69, 2) = PO.Cells(N, 121) 'COMMENT 12
Case 110
FORM.Cells(70, 1) = PO.Cells(N, 122) 'line item 13
Case 111
FORM.Cells(36, 4) = PO.Cells(N, 123) 'Title
Case 112
FORM.Cells(36, 2) = PO.Cells(N, 124) 'Genre
Case 113
FORM.Cells(36, 1) = PO.Cells(N, 126) 'QTY
Case 114
FORM.Cells(36, 8) = PO.Cells(N, 127) 'UNIT$
Case 115
FORM.Cells(36, 9) = PO.Cells(N, 128) 'Total Cost
Case 116
FORM.Cells(36, 10) = PO.Cells(N, 129) 'ACCOUNT NO.
Case 117
FORM.Cells(70, 2) = PO.Cells(N, 130) 'COMMENT 13
Case 118
FORM.Cells(71, 1) = PO.Cells(N, 131) 'line item 14
Case 119
FORM.Cells(37, 4) = PO.Cells(N, 132) 'Title
Case 120
FORM.Cells(37, 2) = PO.Cells(N, 133) 'Genre
Case 121
FORM.Cells(37, 1) = PO.Cells(N, 135) 'QTY
Case 122
FORM.Cells(37, 8) = PO.Cells(N, 136) 'UNIT$
Case 123
FORM.Cells(37, 9) = PO.Cells(N, 137) 'Total Cost
Case 124
FORM.Cells(37, 10) = PO.Cells(N, 138) 'ACCOUNT NO.
Case 125
FORM.Cells(71, 2) = PO.Cells(N, 139) 'COMMENT 14
Case 126
FORM.Cells(72, 1) = PO.Cells(N, 140) 'line item 15
Case 127
FORM.Cells(38, 4) = PO.Cells(N, 141) 'Title
Case 128
FORM.Cells(38, 2) = PO.Cells(N, 142) 'Genre
Case 129
FORM.Cells(38, 1) = PO.Cells(N, 144) 'QTY
Case 130
FORM.Cells(38, 8) = PO.Cells(N, 145) 'UNIT$
Case 131
FORM.Cells(38, 9) = PO.Cells(N, 146) 'Total Cost
Case 132
FORM.Cells(38, 10) = PO.Cells(N, 147) 'ACCOUNT NO.
Case 133
FORM.Cells(72, 2) = PO.Cells(N, 148) 'COMMENT 15
Case 134
FORM.Cells(73, 1) = PO.Cells(N, 149) 'line item 16
Case 135
FORM.Cells(39, 4) = PO.Cells(N, 150) 'Title
Case 136
FORM.Cells(39, 2) = PO.Cells(N, 151) 'Genre
Case 137
FORM.Cells(39, 1) = PO.Cells(N, 153) 'QTY
Case 138
FORM.Cells(39, 8) = PO.Cells(N, 154) 'UNIT$
Case 139
FORM.Cells(39, 9) = PO.Cells(N, 155) 'Total Cost
Case 140
FORM.Cells(39, 10) = PO.Cells(N, 156) 'ACCOUNT NO.
Case 141
FORM.Cells(73, 2) = PO.Cells(N, 157) 'COMMENT 16
Case 142
FORM.Cells(74, 1) = PO.Cells(N, 158) 'line item 17
Case 143
FORM.Cells(40, 4) = PO.Cells(N, 159) 'Title
Case 144
FORM.Cells(40, 2) = PO.Cells(N, 160) 'Genre
Case 145
FORM.Cells(40, 1) = PO.Cells(N, 162) 'QTY
Case 146
FORM.Cells(40, 8) = PO.Cells(N, 163) 'UNIT$
Case 147
FORM.Cells(40, 9) = PO.Cells(N, 164) 'Total Cost
Case 148
FORM.Cells(40, 10) = PO.Cells(N, 165) 'ACCOUNT NO.
Case 149
FORM.Cells(74, 2) = PO.Cells(N, 166) 'COMMENT 17
Case 150
FORM.Cells(75, 1) = PO.Cells(N, 167) 'line item 18
Case 151
FORM.Cells(41, 4) = PO.Cells(N, 168) 'Title
Case 152
FORM.Cells(41, 2) = PO.Cells(N, 169) 'Genre
Case 153
FORM.Cells(41, 1) = PO.Cells(N, 171) 'QTY
Case 154
FORM.Cells(41, 8) = PO.Cells(N, 172) 'UNIT$
Case 155
FORM.Cells(41, 9) = PO.Cells(N, 173) 'Total Cost
Case 156
FORM.Cells(41, 10) = PO.Cells(N, 174) 'ACCOUNT NO.
Case 157
FORM.Cells(75, 2) = PO.Cells(N, 175) 'COMMENT 18
Case 158
FORM.Cells(76, 1) = PO.Cells(N, 176) 'line item 19
Case 159
FORM.Cells(42, 4) = PO.Cells(N, 177) 'Title
Case 160
FORM.Cells(42, 2) = PO.Cells(N, 178) 'Genre
Case 161
FORM.Cells(42, 1) = PO.Cells(N, 180) 'QTY
Case 162
FORM.Cells(42, 8) = PO.Cells(N, 181) 'UNIT$
Case 163
FORM.Cells(42, 9) = PO.Cells(N, 182) 'Total Cost
Case 164
FORM.Cells(42, 10) = PO.Cells(N, 183) 'ACCOUNT NO.
Case 165
FORM.Cells(76, 2) = PO.Cells(N, 184) 'COMMENT 19
Case 166
FORM.Cells(77, 1) = PO.Cells(N, 185) 'line item 20
Case 167
FORM.Cells(43, 4) = PO.Cells(N, 186) 'Title
Case 168
FORM.Cells(43, 2) = PO.Cells(N, 187) 'Genre
Case 169
FORM.Cells(43, 1) = PO.Cells(N, 189) 'QTY
Case 170
FORM.Cells(43, 8) = PO.Cells(N, 190) 'UNIT$
Case 171
FORM.Cells(43, 9) = PO.Cells(N, 191) 'Total Cost
Case 172
FORM.Cells(43, 10) = PO.Cells(N, 192) 'ACCOUNT NO.
Case 173
FORM.Cells(77, 2) = PO.Cells(N, 193) 'COMMENT 20
Case 174
FORM.Cells(78, 1) = PO.Cells(N, 194) 'line item 21
Case 175
FORM.Cells(44, 4) = PO.Cells(N, 195) 'Title
Case 176
FORM.Cells(44, 2) = PO.Cells(N, 196) 'Genre
Case 177
FORM.Cells(44, 1) = PO.Cells(N, 198) 'QTY
Case 178
FORM.Cells(44, 8) = PO.Cells(N, 199) 'UNIT$
Case 179
FORM.Cells(44, 9) = PO.Cells(N, 200) 'Total Cost
Case 180
FORM.Cells(44, 10) = PO.Cells(N, 201) 'ACCOUNT NO.
Case 181
FORM.Cells(78, 2) = PO.Cells(N, 202) 'COMMENT 21
Case 182
FORM.Cells(79, 1) = PO.Cells(N, 203) 'line item 22
Case 183
FORM.Cells(45, 4) = PO.Cells(N, 204) 'Title
Case 184
FORM.Cells(45, 2) = PO.Cells(N, 205) 'Genre
Case 185
FORM.Cells(45, 1) = PO.Cells(N, 207) 'QTY
Case 186
FORM.Cells(45, 8) = PO.Cells(N, 208) 'UNIT$
Case 187
FORM.Cells(45, 9) = PO.Cells(N, 209) 'Total Cost
Case 188
FORM.Cells(45, 10) = PO.Cells(N, 210) 'ACCOUNT NO.
Case 189
FORM.Cells(79, 2) = PO.Cells(N, 211) 'COMMENT 22
Case 190
FORM.Cells(80, 1) = PO.Cells(N, 212) 'line item 23
Case 191
FORM.Cells(46, 4) = PO.Cells(N, 213) 'Title
Case 192
FORM.Cells(46, 2) = PO.Cells(N, 214) 'Genre
Case 193
FORM.Cells(46, 1) = PO.Cells(N, 216) 'QTY
Case 194
FORM.Cells(46, 8) = PO.Cells(N, 217) 'UNIT$
Case 195
FORM.Cells(46, 9) = PO.Cells(N, 218) 'Total Cost
Case 196
FORM.Cells(46, 10) = PO.Cells(N, 219) 'ACCOUNT NO.
Case 197
FORM.Cells(80, 2) = PO.Cells(N, 220) 'COMMENT 23
Case 198
FORM.Cells(81, 1) = PO.Cells(N, 221) 'line item 24
Case 199
FORM.Cells(47, 4) = PO.Cells(N, 222) 'Title
Case 200
FORM.Cells(47, 2) = PO.Cells(N, 223) 'Genre
Case 201
FORM.Cells(47, 1) = PO.Cells(N, 225) 'QTY
Case 202
FORM.Cells(47, 8) = PO.Cells(N, 226) 'UNIT$
Case 203
FORM.Cells(47, 9) = PO.Cells(N, 227) 'Total Cost
Case 204
FORM.Cells(47, 10) = PO.Cells(N, 228) 'ACCOUNT NO.
Case 205
FORM.Cells(81, 2) = PO.Cells(N, 229) 'COMMENT 24
Case 206
FORM.Cells(82, 1) = PO.Cells(N, 230) 'line item 25
Case 207
FORM.Cells(48, 4) = PO.Cells(N, 231) 'Title
Case 208
FORM.Cells(48, 2) = PO.Cells(N, 232) 'Genre
Case 209
FORM.Cells(48, 1) = PO.Cells(N, 234) 'QTY
Case 210
FORM.Cells(48, 8) = PO.Cells(N, 235) 'UNIT$
Case 211
FORM.Cells(48, 9) = PO.Cells(N, 236) 'Total Cost
Case 212
FORM.Cells(48, 10) = PO.Cells(N, 237) 'ACCOUNT NO.
Case 213
FORM.Cells(82, 2) = PO.Cells(N, 238) 'COMMENT 25
Case 214
FORM.Cells(49, 1) = PO.Cells(N, 239) 'Total # Copies
Case 215
FORM.Cells(49, 9) = PO.Cells(N, 241) 'Tax
Case 216
FORM.Cells(50, 9) = PO.Cells(N, 242) 'Total Cost ($)

End Select
Next X

'sort the data
Worksheets("FORM").Range("A22:J48").Sort _
Key1:=Worksheets("FORM").Range("D24"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
FORM.Activate
FORM.PrintOut
PO.Activate


End Sub

 

[Smitty]

You're telling it to include row 23:

Range("A22:J48").

Unfortunately, you have 23 as part of the sort range. With your headers in row 22, I don't see any natural way to exclude it.