VBA: Copy Updates only

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.

TAGREPORTEDSTATUSDEBREEDGAgeCOMMENTS
1234567896/25/2020OPENtexttextPITBULLTEXT2Copy 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

ABCDETAGGHREPORTEDJKLMNOPQRSCOMMENTSUVWStatus
texttexttexttexttext234567899texttext6/2/2020texttexttexttexttexttexttexttexttexttextThese are comments to be copied to FILES sheet, column "I".texttexttextOPEN



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"
Thank you! Happy Friday.

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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hi. I am still struggling with this. Anybody? Thank you.
 

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
How do I modify this to suit my needs? Will it work?
It was from dramqueenuk's post. VBA Code: Update Row

VBA Code:
Sub Offline_VNHO_Form()
   Dim Fnd As Range
      
   Set Fnd = Sheets("Dashboard").Range("A:A").Find(Range("User_ID_Offline_VNHO").Value, , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      MsgBox Range("User_ID_Offline_VNHO").Value & " not found"
      Exit Sub
   End If
   Fnd.Offset(, 10).Value = Range("Offline_VNHO_Date").Value
   Fnd.Offset(, 22).Value = Range("Offline_VNHO_Reason").Value
            
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,600
Messages
5,597,113
Members
414,125
Latest member
iQQ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top