Private Sub cbAddCIS_Click ()
Dim pvtTable As the PivotTable
Dim oListObj As ListObject, oLO As ListObject
Dim f_rng As Range
Dim oNewRow As ListRow
Dim f_rowno As Long, f_colno As Long, t_colno As Long, r_pos As Long,
C_pos As Long
Dim lookup_value As String, lookup_col_name As String, result_col_name As
The String
Dim found As Boolean
On the Error Resume Next
The Set pvtTable=ActiveSheet. PivotTables (" customer assets summary table ")
The Set f_rng=pvtTable. RowRange
The Set oListObj=Worksheets (" customer assets summary "). The ListObjects (" additional customer information ")
The Set oLO=Worksheets (" customer information "). The ListObjects (" customer information form ")
Call cbDelCIS_Click 'first remove additional customer information
For f_rowno=2 To f_rng. Rows. The Count - 1 'processing each row (remove header row and summary
Line)
'insert empty lines
The Set oNewRow=oListObj. ListRows. Add
'with non-empty locate elements (customer number, id or name) to find the customer information
Found=False
For f_colno=1 To f_rng. Columns. The Count
Lookup_value=https://bbs.csdn.net/topics/f_rng.Cells (f_rowno f_colno)
Lookup_col_name=f_rng. Cells (1, f_colno)
If (lookup_value & lt;> "" And lookup_value & lt;> "(blank)") Then
R_pos=0
R_pos=WorksheetFunction. Match (lookup_value, oLO ListColumns
(lookup_col_name). DataBodyRange, 0)
If r_pos & gt; 0 Then
Found=True
The Exit For
End the If
End the If
Next
If found Then 'find them, one by one to add the elements of the customer information
For t_colno=1 To oListObj. ListColumns. Count
Result_col_name=oListObj. ListColumns (t_colno). The Name
C_pos=0
C_pos=WorksheetFunction. Match (result_col_name,
OLO. HeaderRowRange, 0)
If c_pos & gt; 0 Then
ONewRow. Range. Cells (1, t_colno)=oLO. DataBodyRange. Cells (r_pos,
C_pos)
End the If
Next
End the If
Next
End Sub
Private Sub cbDelCIS_Click ()
Dim RNG As Range
The Set of RNG=Worksheets (" customer assets summary "). ListObjects (" additional customer information
"). DataBodyRange
If RNG Is Nothing Then
The Exit Sub
End the If
RNG. Delete
End Sub
Private Sub cbrefresh_Click ()
Dim pvtTable As the PivotTable
The Set pvtTable=ActiveSheet. PivotTables (" customer assets summary table ")
PvtTable. RefreshTable
If Worksheets (" customer assets summary "). The ListObjects (" additional customer information "). DataBodyRange
Is Nothing Then
The Exit Sub
End the If
'Call cbAddCIS_Click
End Sub
CodePudding user response:
ScreenUpdating attributePlease refer to the application in the sample feature
If the screen update feature is turned on, the value is True, Boolean type, read/write,
Description
Close the screen update can accelerate the speed of execution of the macro, so will can't see the macro execution, but the execution speed of macro,
When the macro ends after the operation, please remember to ScreenUpdating attribute set to True,
Example
This example demonstrates the screen update after closing, the system on how to accelerate the speed of execution of a code, the example of every column hidden Sheet1 columns, and maintain its execution time, for the first time, the sample hidden columns, screen update is open; Second executes, update the screen is turned off, run this example, comparable information box shows two execution time,
Dim elapsedTime. (2)
Application. ScreenUpdating=True
For I=1 To 2
If I=2 Then Application. ScreenUpdating=False
StartTime=Time
Worksheets (" Sheet1 "). Activate
For Each c In ActiveSheet. Columns
If Arthur c. olumn Mod 2=0 Then
C. idden=True
End the If
Next c
StopTime=Time
ElapsedTime (I)=(stopTime - startTime) * 24 * 60 * 60
Next I
Application. ScreenUpdating=True
MsgBox "Elapsed time, screen updating on:" & amp; ElapsedTime (1) & amp; _
"The SEC." & amp; CRH (13) & amp; _
"Elapsed time, screen updating off:" & amp; ElapsedTime. (2) & amp; _
"The SEC."