Can someone recommend the best way to copy updates from another Sheet? I am a newbie so not sure how to modify the VBA properly.
This is the FILES sheet I want to update.
This is the UPDATES sheet
I tried to modify this code that DanteAmor was kind enough to provide for another post.
This code copies to new rows and does not replace contents in current cells under Column "I".
1. How to change it to copy entire row for new entries from the UPDATES Sheet to a new sheet?
2. How to modify the code to copy the updates from UPDATES sheet to FILES sheet?
Thank you! Happy Friday.
This is the FILES sheet I want to update.
TAG | REPORTED | STATUS | D | E | BREED | G | Age | COMMENTS |
123456789 | 6/25/2020 | OPEN | text | text | PITBULL | TEXT | 2 | Copy Comments from sheet called "UPDATES" COLUMN "T", to replace contents for this Column I, based on TAG from Column "A", but Status must be OPEN. |
This is the UPDATES sheet
A | B | C | D | E | TAG | G | H | REPORTED | J | K | L | M | N | O | P | Q | R | S | COMMENTS | U | V | W | Status |
text | text | text | text | text | 234567899 | text | text | 6/2/2020 | text | text | text | text | text | text | text | text | text | text | These are comments to be copied to FILES sheet, column "I". | text | text | text | OPEN |
I tried to modify this code that DanteAmor was kind enough to provide for another post.
This code copies to new rows and does not replace contents in current cells under Column "I".
1. How to change it to copy entire row for new entries from the UPDATES Sheet to a new sheet?
2. How to modify the code to copy the updates from UPDATES sheet to FILES sheet?
- Based on the TAG number: UPDATES sheet column "T" copied to FILES sheet replacing contents in column "I"
- Status must be "OPEN"
VBA Code:
Copy row to sheet
Sub Tracking_Update()
Dim a As Variant, b As Variant, c As Variant 'any data type'
Dim dic As Object, sh1 As Worksheet 'Object=any object reference'
Dim i As Long, j As Long, k As Long 'Long=(4 bytes) integer'
Set sh1 = Sheets("Sheet1")
Set dic = CreateObject("Scripting.Dictionary")
a = sh1.Range("A2:D" & sh1.Range("A" & Rows.Count).End(3).Row).Value2
b = Sheets("Sheet2").Range("F2", Sheets("Sheet2").Cells.SpecialCells(xlCellTypeLastCell)).Value2
ReDim c(1 To UBound(b, 1), 1 To UBound(b, 2))
For i = 1 To UBound(a, 1)
dic(a(i, 1)) = i
Next
For i = 1 To UBound(b, 1)
If Not dic.exists(b(i, 1)) Then
k = k + 1 'k becomes (=) k and add 1 to it'
c(k, 1) = b(i, 1)
c(k, 4) = b(i, 5)
c(k, 3) = b(i, 3)
Else
a(dic(b(i, 1)), 4) = b(i, 5)
End If
Next
sh1.Range("D2").Resize(UBound(a, 1), 1).Value = Application.Index(a, , 4)
sh1.Range("A" & Rows.Count).End(3)(2).Resize(k, UBound(b, 2)).Value = c
End Sub