我正在处理一张从远程工作表中获取数据的工作表,将它们复制到一个工作表中,然后将它们合并到一个表中(所有传入的数据都使用来自excel文件的连接,并将数据放入表中)
我需要做的是能够在远程数据刷新时更新单个表,而不删除旧行,而只是插入新行 . 每个表中都有一个数字列,并且具有唯一值,有些单元格使用工作表公式(countifs)来确定介于这些范围之间的行数(例如,sheet1中的table1具有始终具有值的列A在10002和20001之间,工作表2的值介于20002和30001之间,等等)复制这些工作表及其数据时,如何仅复制新行?
作为参考,远程工作表是其他用户每天使用的工作表,因此将定期使用更新和插入宏,并且它们合并到的单个表也是具有更新数据的东西,因此不需要删除旧行,但只追加 - 我对VBA很熟练,遇到这种麻烦,但我不是专家 . 这是我在这里的第一个问题,尽管我很高兴地使用该网站获得答案很长一段时间
示例代码如下:
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With
ShtIndex = ActiveWorkbook.Sheets("Ann Q1").Index
With ActiveWorkbook
Asheet = .Sheets(ShtIndex)
Bsheet = .Sheets(ShtIndex + 1)
Csheet = .Sheets(ShtIndex + 2)
Xsheet = .Sheets(ShtIndex + 3)
Ysheet = .Sheets(ShtIndex + 4)
End With
Set Dsheet = ActiveWorkbook.Sheets("Jim_ALL")
Set Dtable = Dsheet.ListObjects("Audit_Final")
Set Atable = Asheet.ListObjects(1)
Set Btable = Bsheet.ListObjects(1)
Set Ctable = Csheet.ListObjects(1)
Set Xtable = Xsheet.ListObjects(1)
Set Ytable = Ysheet.ListObjects(1)
ANN = Atable.ListRows.Count
DNA = Btable.ListRows.Count
KTHY = Ctable.ListRows.Count
NRMA = Xtable.ListRows.Count
SHMKA = Ytable.ListRows.Count
On Error Resume Next
J = ShtIndex
Do While J < ShtIndex + 5
Set Ssheet = Worksheets(J)
Set Stable = Ssheet.ListObjects(1)
LastR = Stable.ListRows.Count
LastC = Stable.ListColumns.Count
' Stable.DataBodyRange.Copy Destination:=Ssheet.Range("A65536").End(xlUp)
If J = ShtIndex Then
ANN1 = LastR
J1 = Sheets("Macros").Range.Cells("G1").Value
R1 = ANN - J1
ElseIf J = ShtIndex + 1 Then
DNA1 = LastR
J2 = Sheets("Macros").Range.Cells("H1").Value
R1 = DNA - J2
ElseIf J = ShtIndex + 2 Then
KTHY1 = LastR
J3 = Sheets("Macros").Range.Cells("I1").Value
R1 = KTHY - J3
ElseIf J = ShtIndex + 3 Then
NRMA1 = LastR
J4 = Sheets("Macros").Range.Cells("J1").Value
R1 = NRMA - J4
ElseIf J = ShtIndex + 4 Then
SHMKA1 = LastR
J5 = Sheets("Macros").Range.Cells("K1").Value
R1 = SHMKA - J5
End If
J = J + 1
Loop
On Error GoTo 0
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With
End Sub