Need help with Macro

D

DKRbella0814

Board Regular
Joined
Aug 10, 2008
Messages
155
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
 
Sort by date Sort by votes
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Advice for Could Not Find The Specific Object message
2
Replies
12
Views
242
ipbr21054
I
M
  • Question Question
Macro/Automating Edits and Calculations for Large Report (40K rows)
Replies
6
Views
217
kevin9999
K
L
  • Question Question
VBA macro problem
Replies
2
Views
175
Joe4
Joe4
R
  • Question Question
Required Transposed Values instead of Row Items of Duplicate Invoice no with Each GSTIN which is displaying the SUM of 12+18+28
Replies
1
Views
114
rk_rk
R
gtd526
  • Solved
Eliminate duplicate values for 2nd and 3rd highest value
Replies
3
Views
139
gtd526
gtd526

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