我将尽力简洁地解释这一点,如果有什么不对的地方,请插入。
因此,我有一个每月生成的服务器数据的清单电子表格(称为Book1),但是由于此信息会更改,因此有几列会手动更新(E,H,I,J,K)。我需要做的是,如果A列中的主机名相互找到(服务器始终被创建和删除),则在月底将更新的信息复制到下一个新生成的电子表格(称为Book2)。
我有一些完美的VBA,但仅适用于E和F列。我希望有人可以对其进行编辑以复制到E,H,I,J,K上。
Sub test()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Workbooks("Book1.xlsx").Sheets(1)
Set sh2 = Workbooks("Book2.xlsx").Sheets(1)
For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
If fn.Offset(, 4).Value <> c.Offset(, 4).Value Or fn.Offset(, 5).Value <> c.Offset(, 5).Value Then
c.Offset(, 4).Resize(, 2).Copy fn.Offset(, 4)
End If
End If
Set fn = Nothing
Next
End Sub
9月旧库存:
新的10月库存:
谢谢!
格雷格
为什么不只是添加c.Offset(, 7).Resize(, 4).Copy fn.Offset(, 7)
到您现有的代码中呢?
Sub test()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Workbooks("Book1.xlsx").Sheets(1)
Set sh2 = Workbooks("Book2.xlsx").Sheets(1)
For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
c.Offset(, 4).Resize(, 2).Copy fn.Offset(, 4)
c.Offset(, 7).Resize(, 4).Copy fn.Offset(, 7)
End If
Set fn = Nothing
Next
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句