Добрый день!
Подскажите пожалуйста, как в макросе сделать корректную выборку с автофильтра?
Дело в том, что с макросами особо не дружу, делаю их только в записи Excel своих действий (записать макрос и потом остановить).
Сделал автофильтр по условию и в конце сумма промежуточных итогов по выбранному фильтру.
Однако делая макросом следующий автофильтр по другому условию он делает промежуточные итоги по старому автофильтру.
Помогите пожалуйста!
Код |
---|
Range("AU1").Select Selection.AutoFilter
Range("AV4").SelectActiveSheet.Range("$A$1:$AU$221").AutoFilter Field:=46, Criteria1:="Алмата"[/P]Range("AU19").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 99
ActiveWindow.SmallScroll Down:=222
Range("AU400").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-381]C:R[-1]C)"
Range("AU400").Select
Selection.Copy
Range("AS400").Select
ActiveSheet.Paste
Range("AQ400").Select
ActiveSheet.Paste
Range("AI400").Select
ActiveSheet.Paste
Range("AC400").Select
ActiveSheet.Paste
ActiveWindow.ScrollRow = 378
ActiveWindow.ScrollRow = 376
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 372
ActiveWindow.ScrollRow = 369
ActiveWindow.ScrollRow = 367
ActiveWindow.ScrollRow = 365
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 361
ActiveWindow.ScrollRow = 359
ActiveWindow.ScrollRow = 357
ActiveWindow.ScrollRow = 355
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 351
ActiveWindow.ScrollRow = 347
ActiveWindow.ScrollRow = 345
ActiveWindow.ScrollRow = 343
ActiveWindow.ScrollRow = 341
ActiveWindow.ScrollRow = 339
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 336
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 332
ActiveWindow.ScrollRow = 330
ActiveWindow.ScrollRow = 329
ActiveWindow.ScrollRow = 326
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 318
ActiveWindow.ScrollRow = 312
ActiveWindow.ScrollRow = 308
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 297
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 277
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 220
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 207
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 190
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 157
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 204
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 278
ActiveWindow.ScrollRow = 289
ActiveWindow.ScrollRow = 298
ActiveWindow.ScrollRow = 307
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 328
ActiveWindow.ScrollRow = 335
ActiveWindow.ScrollRow = 339
ActiveWindow.ScrollRow = 345
ActiveWindow.ScrollRow = 351
ActiveWindow.ScrollRow = 356
ActiveWindow.ScrollRow = 360
ActiveWindow.ScrollRow = 365
ActiveWindow.ScrollRow = 369
Sheets("Лист2").Select
Range("B1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Алматы"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Выплаченные"
Range("B4").Select
ActiveCell.FormulaR1C1 = "комиссия"
Range("B7").Select
ActiveCell.FormulaR1C1 = "Отправленные"
Range("B8").Select
ActiveCell.FormulaR1C1 = "комиссия"
Range("B9").Select
Columns("B:B").ColumnWidth = 14.29
Range("B3:C4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("B7:C8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("D8").Select
Columns("C:C").ColumnWidth = 13.71
Range("C1").Select
Selection.Font.Bold = True
Sheets("Лист1").Select
Range("AU400").Select
Selection.Copy
Sheets("Лист2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Лист1").Select
ActiveWindow.ScrollRow = 368
ActiveWindow.ScrollRow = 365
ActiveWindow.ScrollRow = 360
ActiveWindow.ScrollRow = 356
ActiveWindow.ScrollRow = 348
ActiveWindow.ScrollRow = 339
ActiveWindow.ScrollRow = 329
ActiveWindow.ScrollRow = 319
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 280
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 232
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 1
ActiveSheet.Range("$A$1:$AU$221").AutoFilter Field:=8, Criteria1:="P"
ActiveWindow.SmallScroll Down:=240
Range("AC400").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Лист2").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Лист1").Select
Range("AI400").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Лист2").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Лист1").Select
ActiveWindow.ScrollRow = 387
ActiveWindow.ScrollRow = 385
ActiveWindow.ScrollRow = 379
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 367
ActiveWindow.ScrollRow = 359
ActiveWindow.ScrollRow = 351
ActiveWindow.ScrollRow = 342
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 313
ActiveWindow.ScrollRow = 304
ActiveWindow.ScrollRow = 294
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 1
ActiveSheet.Range("$A$1:$AU$221").AutoFilter Field:=8, Criteria1:="S"
ActiveWindow.SmallScroll Down:=204
Range("AS400").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Лист2").Select
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Лист1").Select
Range("AI400").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Лист2").Select
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Style = "Comma"
Range("G6").Select
ActiveCell.FormulaR1C1 = "Контроль отправленных"
Range("E8").Select
Sheets("Лист1").Select
Range("AQ400").Select
Selection.Copy
Sheets("Лист2").Select
Range("F7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E7:F7").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Columns("E:E").ColumnWidth = 10.14
Range("F7").Select
Selection.Style = "Comma"
End Sub
|