-
Notifications
You must be signed in to change notification settings - Fork 0
/
公用模块1.bas
153 lines (130 loc) · 4.47 KB
/
公用模块1.bas
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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
Attribute VB_Name = "公用模块1"
Sub 取消所有工作表保护()
Application.ScreenUpdating = False '禁刷新
With ThisWorkbook
For i = 1 To .Worksheets.Count '遍历文件的工作表数
表名 = .Worksheets(i).Name
If 表名 <> "out" Then '判断不为out的表格 即所以表
.Sheets(表名).Unprotect
End If
Next i
End With
End Sub
Sub 保护所有工作表()
Application.ScreenUpdating = False '禁刷新
With ThisWorkbook
For i = 1 To .Worksheets.Count '遍历文件的工作表数
表名 = .Worksheets(i).Name
If 表名 <> "透视表" Then '判断不为out的表格 即所以表
.Sheets(表名).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
Next i
End With
End Sub
Sub 清空合表()
Sheets("合").Range("A2:P2000").ClearContents '清空原始数据,写死out表格,防止清错
Worksheets("合").Activate
mbx = 2000
Rows(2 & ":" & mbx).Delete
End Sub
Sub 清除填充批注()
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.ClearComments
End Sub
Sub 去除线()
Cells.Select
Selection.Borders.LineStyle = 0 '去除框线
End Sub
Sub 纯白填充()
'x = ActiveSheet.[A65535].End(xlUp).Row
x = 1000
Range("A2:AZ" & x).Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Sub 获取表名粘贴到A列()
For x = 1 To Sheets.Count
Cells(x, 1) = Sheets(x).Name
Next x
End Sub
Sub 筛选合表() '筛选合表
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
i = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Range("A1:P" & i).Select
Selection.AutoFilter
End Sub
Sub 筛选批次() '筛选合表
'If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
i = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Range("A2:P" & i).Select
Selection.AutoFilter
End Sub
Sub 筛选供货单() '筛选合表
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
i = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Range("A1:Q" & i).Select
Selection.AutoFilter
End Sub
Sub 筛选方案() '筛选方案
'If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
i = ActiveSheet.Range("J" & Rows.Count).End(xlUp).Row
Range("I1:S" & i).Select
Selection.AutoFilter
End Sub
Sub 筛选合同() '筛选方案
'If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
i = ActiveSheet.Range("T" & Rows.Count).End(xlUp).Row
Range("T1:Y" & i).Select
Selection.AutoFilter
End Sub
Sub 筛选合同汇总表() '筛选方案
Dim StartTime
StartTime = Timer
'If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
i = ActiveSheet.Range("AW" & Rows.Count).End(xlUp).Row
Range("AV1:BN" & i).Select
Selection.AutoFilter
'MsgBox Timer - StartTime
End Sub
Sub 筛选发票() '筛选方案
'If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
i = ActiveSheet.Range("AM" & Rows.Count).End(xlUp).Row
Range("AL1:AR" & i).Select
Selection.AutoFilter
End Sub
Sub 筛选付款() '筛选方案
'If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
i = ActiveSheet.Range("AM" & Rows.Count).End(xlUp).Row
Range("Z1:AJ" & i).Select
Selection.AutoFilter
End Sub
Sub 更改透视表数据源()
Dim oPT As PivotTable
Dim oPC As PivotCache
Dim oWK As Worksheet
Set oWK = Worksheets("透视表")
iRow = Worksheets("合").Range("B65536").End(xlUp).Row
With oWK
Set oPT = .PivotTables(1)
With oPT
'获取原来的数据透视表的数据源
sOrign = .SourceData
'直接将数据源更改为其它单元格区域
.SourceData = Worksheets("合").Range("A1:P" & iRow).Address(True, True, xlR1C1, True)
'获取最新的数据透视表的数据源
sNew = .SourceData
'刷新透视表
.RefreshTable
'刷新数据源
.Update
End With
End With
End Sub