200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello
I have a problem when try copy new items from sheet1 to sheet2 after matching based on column B when run the macro , highlight the new items for sheet2 , then will delete borders from column D and somtimes add borders for the whole row after last column , also delete formatting as in original in sheet1.
how I can fix this problem ?
Hi Leap
I think there's some problem with the file itself- Excel wouldn't let me save a modified version, saying something like "...,unable to fix errors...".
I copied the sheets to a new file (attached) and added a With/ End With portion to the first part of your Module 1 code (changes in bold):
Sub UpdateSheet21()
Dim LR1 As Long, LR2 As Long, n As Long
LR1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
' First find (and add) any Brands which do not exist on Sheet 2
For Each Cll In Sheets("Sheet1").Range("B2:B" & LR1)
If Not IsNumeric(Application.Match(Cll.Value, Sheets("Sheet2").Columns(2), 0)) Then 'if no Exact Match...
LR2 = LR2 + 1
Cll.Resize(1, 3).Copy Sheets("Sheet2").Range("B" & LR2)
With Sheets("Sheet2")
.Range("C" & LR2).Value = "" 'set to blank
.Range("A" & LR2).Resize(1, 6).Interior.Color = vbRed
.Range("A" & LR2) = LR2 - 1
'adjust column D formatting
.Range("D" & LR2).BorderAround xlContinuous, xlThin
.Range("D" & LR2).HorizontalAlignment = xlCenter
End With
End If
Next CllIt all seems to work okay (or how I think you intended it), without error.
Hope this helps.