This is my first post. I can usually create the simple macros I need or research to find help, but I can't get this one to work. It is mostly a copied macro from someone else that I was trying to customize.
I need it to copy the entire row (not just the value of one cell) from one sheet to another in the same wb. Currently it looks for a value that is in a column on sheet 1 and not in a column on sheet 2, then pastes the value in the column on sheet 2, but I need it to pasted the entire row. Any help would be much appreciated!
This is what I have so far...
Sub P_and_ID_Archive_Macro()
Dim Rng, Dn As Range, n As Long, RngA As Range, RngB As Range
Dim SubRng, Q
Set RngA = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
With Sheets("P&ID Archival Status")
Set RngB = .Range(.Range("C1"), .Range("C" & Rows.Count).End(xlUp))
End With
Rng = Array(RngA, RngB)
ReDim Ray(1 To RngA.Count + RngB.Count)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For SubRng = 0 To UBound(Rng)
For Each Dn In Rng(SubRng)
If Not .Exists(Dn.Value) Then
n = n + 1
.Add Dn.Value, Array(n, SubRng)
Ray(n) = Dn.Value
Else
Q = .Item(Dn.Value)
If Not Q(1) = SubRng Then
Ray(Q(0)) = vbNullString
End If
End If
Next Dn
Next SubRng
End With
With Sheets("P&ID Archival Status").Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(n)
.Value = Application.Transpose(Ray)
.SpecialCells(xlCellTypeBlanks).Delete
End With
Bookmarks