-
Notifications
You must be signed in to change notification settings - Fork 0
/
filtroGeral_office2010.txt
134 lines (119 loc) · 3.63 KB
/
filtroGeral_office2010.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
Sub filtroGeral()
'
' abrirTitulosPagos Macro
'
ChDir "C:\Users\Jonatas\Documents\COMPARTILHAR"
Workbooks.OpenXML Filename:= _
"C:\Users\Jonatas\Documents\COMPARTILHAR\TitulosPagos.csv"
'
' filtroGeral Macro
'
ultimaLinha = WorksheetFunction.CountA(Columns("C"))
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("G:H").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Cut
Selection.End(xlToLeft).Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
Selection.FillRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""VE"",RC[-1])"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & ultimaLinha)
Range("B1").Select
Workbooks.Open Filename:="C:\Users\Jonatas\Documents\Report.xls"
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("D:E").Select
Selection.Delete Shift:=xlToLeft
Columns("E:H").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Selection.Cut
Selection.End(xlToLeft).Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveWindow.ActivateNext
Range("B1:F1").Select
Selection.Copy
ActiveWindow.ActivateNext
ActiveWindow.ActivateNext
ActiveSheet.Paste
ultimaLinhaReport = WorksheetFunction.CountA(Columns("C"))
Range("A1:E" & ultimaLinhaReport).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
Range("B2").Select
Sheets.Add
ActiveSheet.Paste
Sheets("TitulosPagos").Name = "tpagos"
Sheets("Plan1").Select
Range("B1").Select
Selection.End(xlToRight).Select
Range("E2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],tpagos!C[-3]:C[1],5,0)"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.FillDown
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Font.Italic = True
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Bold = True
Range("E1").Select
' Filtro números em #N/D
Selection.AutoFilter
ActiveSheet.Range("E2").AutoFilter Field:=5, Criteria1:=">1"
'
' Copiando para planilha2
'
'
ultimaLinhaFormt = WorksheetFunction.CountA(Columns("C"))
Sheets.Add
Selection.Copy
Sheets("Plan1").Select
Range("A1:E" & ultimaLinhaFormt).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Plan2").Select
ActiveSheet.Paste
' Fechando report
Workbooks("Report.xls").Close SaveChanges:=False
'
' Formatando
'
Range("E1").Select
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A2:D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Size = 8
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
'
' Salvando a planilha renomeando-a em formato de data
'
DirPath = "C:\Users\Jonatas\Documents\COMPARTILHAR\"
DateStr = Format(Date, "yyyy-mm-d")
ActiveWorkbook.SaveAs Filename:= _
DirPath & DateStr, FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
End Sub