How to not set a range in VBA

C

Coco1

New Member
Joined
Oct 6, 2023
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I am trying to update my macro to where I do not have a set ranges for the data sheets because each week the about of rows will be different. Is there a way to do so?

I have tried using .Range (ss) and also Worksheets("new not in old").Range("A2:AK2", "A:AK" & Worksheets("new not in old").range('A" & rows.count).end(xlUp).Row).Select

Here is my original macro ( the issue lines in macros 12 to 15) What I am need to change is bolded but I have tried adding both codes but get syntax errors, compile errors, end sub errors.

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
"old not in new", "new not in old", "Blank DOB ")).Select
Sheets("Blank DOB ").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("New Raw Data").Select
Cells.Select
Range("U1").Activate
Selection.Copy
Sheets("Old Raw Data").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("New Raw Data").Select
Cells.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name

strFile = Application.GetOpenFilename

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh
End With
Selection.AutoFilter
End Sub




Sub Macro7()
'
' Macro7 Macro
'
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
Sheets("NPI < 10 digits").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
Sheets("Duplicate NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Blank DOB").Range("A1")
Sheets("Duplicate NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Blank NPI").Range("A1")
Sheets("Blank NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
Sheets("new not in old").Select
Range("A1").Select
Selection.AutoFilter
Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
Sheets("old not in new").Select
Range("A1").Select
Selection.AutoFilter
Sheets("old not in new").Select
Range("A1").Select
End Sub

Sub Macro8()

'Macro8 Macro
Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
"old not in new", "new not in old")).Select
Sheets("Blank NPI").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("New Raw Data").Select
Cells.Select
Range("U1").Activate
Selection.Copy
Sheets("Old Raw Data").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("New Raw Data").Select
Cells.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name

strFile = Application.GetOpenFilename

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh
End With
Selection.AutoFilter

Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
Sheets("NPI < 10 digits").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
Sheets("Duplicate NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Blank NPI").Range("A1")
Sheets("Blank NPI").Select
Range("A1").Select
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
Sheets("new not in old").Select
Range("A1").Select
Selection.AutoFilter
Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
Sheets("old not in new").Select
Range("A1").Select
Selection.AutoFilter
Sheets("old not in new").Select
Range("A1").Select


End Sub


Sub Macro12()
'
' Macro12 Macro
'

'
Sheets("new not in old").Select
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
"A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").Sort
.SetRange Range("A2:AK284")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A284"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("old not in new").Select
Selection.AutoFilter
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A284"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("new not in old").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
End Sub
Sub Macro13()
'
' Macro13 Macro
'

'
Sheets("old not in new").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B284")
Range("B2:B284").Select
Sheets("new not in old").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B284")
Range("B2:B284").Select
Range("B1").Select
End Sub
Sub Macro14()
' Macro14 Macro



'
Sheets("new not in old").Select
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
"A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").Sort
.SetRange Range("A2:AK284")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A284"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("old not in new").Select
Selection.AutoFilter
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A284"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("new not in old").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("old not in new").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B284")
Range("B2:B284").Select
Sheets("new not in old").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B284")
Range("B2:B284").Select
Range("B1").Select

End Sub
Sub Macro15()
Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
"old not in new", "new not in old")).Select
Sheets("Blank NPI").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("New Raw Data").Select
Cells.Select
Range("U1").Activate
Selection.Copy
Sheets("Old Raw Data").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("New Raw Data").Select
Cells.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name

strFile = Application.GetOpenFilename

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh
End With
Selection.AutoFilter
Range("A1").Select
Columns("AB:AB").Select
ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Add _
Key:=Range("AB:AB"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
Sheets("NPI < 10 digits").Select
Range("A:A").Select
Selection.AutoFilter
Range("A1").Select
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
Sheets("Duplicate NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("Blank NPI").Range("A1")
Sheets("Blank NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
Sheets("new not in old").Select
Range("A1").Select
Selection.AutoFilter
Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
Sheets("old not in new").Select
Range("A1").Select
Selection.AutoFilter
Sheets("old not in new").Select
Range("A1").Select
Sheets("new not in old").Select
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
"A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").Sort
.SetRange Range("A2:AK5000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("old not in new").Select
Selection.AutoFilter
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("new not in old").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("old not in new").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B5000")
Range("B2:B5000").Select
Sheets("new not in old").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B5000")
Range("B2:B5000").Select
Range("B1").Select

End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hi @Coco1
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.


Basically you must calculate the last row with data every time you select a sheet.

I added this line for this purpose: lr = Range("A" & Rows.Count).End(3).Row

The complete code:
VBA Code: 
Sub Macro1()
'
' Macro1 Macro
'

  '
  Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
  "old not in new", "new not in old", "Blank DOB ")).Select
  Sheets("Blank DOB ").Activate
  Cells.Select
  Selection.Delete Shift:=xlUp
  Sheets("New Raw Data").Select
  Cells.Select
  Range("U1").Activate
  Selection.Copy
  Sheets("Old Raw Data").Select
  Range("A1").Select
  ActiveSheet.Paste
  Sheets("New Raw Data").Select
  Cells.Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlUp
  Range("A1").Select
  Dim ws As Worksheet, strFile As String
  
  Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name
  
  strFile = Application.GetOpenFilename
  
  With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
  .TextFileParseType = xlDelimited
  .TextFileOtherDelimiter = "|"
  .Refresh
  End With
  Selection.AutoFilter
End Sub
  
  
Sub Macro7()
  '
  ' Macro7 Macro
  '
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
  Sheets("NPI < 10 digits").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
  Sheets("Duplicate NPI").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Blank DOB").Range("A1")
  Sheets("Duplicate NPI").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Blank NPI").Range("A1")
  Sheets("Blank NPI").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
  Sheets("new not in old").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
  Sheets("old not in new").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("old not in new").Select
  Range("A1").Select
End Sub
  
Sub Macro8()
  
  'Macro8 Macro
  Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
  "old not in new", "new not in old")).Select
  Sheets("Blank NPI").Activate
  Cells.Select
  Selection.Delete Shift:=xlUp
  Sheets("New Raw Data").Select
  Cells.Select
  Range("U1").Activate
  Selection.Copy
  Sheets("Old Raw Data").Select
  Range("A1").Select
  ActiveSheet.Paste
  Sheets("New Raw Data").Select
  Cells.Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlUp
  Range("A1").Select
  Dim ws As Worksheet, strFile As String
  
  Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name
  
  strFile = Application.GetOpenFilename
  
  With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
  .TextFileParseType = xlDelimited
  .TextFileOtherDelimiter = "|"
  .Refresh
  End With
  Selection.AutoFilter
  
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
  Sheets("NPI < 10 digits").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
  Sheets("Duplicate NPI").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Blank NPI").Range("A1")
  Sheets("Blank NPI").Select
  Range("A1").Select
  Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
  Sheets("new not in old").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
  Sheets("old not in new").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("old not in new").Select
  Range("A1").Select
  
  
End Sub
  
  
Sub Macro12()
  '
  ' Macro12 Macro
  '
  
  '
  Dim lr As Long
  
  Sheets("new not in old").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Columns("AB:AB").Select
  Selection.Cut
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight
  Range("A1").Select
  ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
  "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortNormal
  With ActiveWorkbook.Worksheets("new not in old").Sort
    .SetRange Range("A2:AK" & lr)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  Selection.AutoFilter
  Selection.AutoFilter
  ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
    :=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  
  Sheets("old not in new").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Selection.AutoFilter
  Columns("AB:AB").Select
  Selection.Cut
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight
  Range("A1").Select
  Selection.AutoFilter
  ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
  :=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("B1").Select
  Sheets("new not in old").Select
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("B1").Select
End Sub
  
Sub Macro13()
  '
  ' Macro13 Macro
  '
  Dim lr As Long
  '
  Sheets("old not in new").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Range("B2").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
  Range("B2").Select
  Selection.AutoFill Destination:=Range("B2:B" & lr)
  'Range("B2:B284").Select
  
  
  Sheets("new not in old").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Range("B2").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
  Range("B2").Select
  Selection.AutoFill Destination:=Range("B2:B" & lr)
  'Range("B2:B284").Select
  Range("B1").Select
End Sub
  
Sub Macro14()
  ' Macro14 Macro
  
  Dim lr As Long
  
  '
  Sheets("new not in old").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Columns("AB:AB").Select
  Selection.Cut
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight
  Range("A1").Select
  ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
  "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortNormal
  With ActiveWorkbook.Worksheets("new not in old").Sort
  .SetRange Range("A2:AK" & lr)
  .Header = xlNo
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Selection.AutoFilter
  Selection.AutoFilter
  ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
  :=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  
  Sheets("old not in new").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Selection.AutoFilter
  Columns("AB:AB").Select
  Selection.Cut
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight
  Range("A1").Select
  Selection.AutoFilter
  ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
  :=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("B1").Select
  Sheets("new not in old").Select
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("B1").Select
  
  
  Sheets("old not in new").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Range("B2").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
  Range("B2").Select
  Selection.AutoFill Destination:=Range("B2:B" & lr)
  'Range("B2:B284").Select
  
  
  Sheets("new not in old").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Range("B2").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
  Range("B2").Select
  Selection.AutoFill Destination:=Range("B2:B" & lr)
  'Range("B2:B284").Select
  Range("B1").Select
  
End Sub
  
Sub Macro15()
  Dim lr As Long
  
  Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
  "old not in new", "new not in old")).Select
  Sheets("Blank NPI").Activate
  Cells.Select
  Selection.Delete Shift:=xlUp
  Sheets("New Raw Data").Select
  Cells.Select
  Range("U1").Activate
  Selection.Copy
  Sheets("Old Raw Data").Select
  Range("A1").Select
  ActiveSheet.Paste
  Sheets("New Raw Data").Select
  Cells.Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlUp
  Range("A1").Select
  Dim ws As Worksheet, strFile As String
  
  Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name
  
  strFile = Application.GetOpenFilename
  
  With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
  .TextFileParseType = xlDelimited
  .TextFileOtherDelimiter = "|"
  .Refresh
  End With
  Selection.AutoFilter
  Range("A1").Select
  Columns("AB:AB").Select
  ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Add _
  Key:=Range("AB:AB"), SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
  Sheets("NPI < 10 digits").Select
  Range("A:A").Select
  Selection.AutoFilter
  Range("A1").Select
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
  Sheets("Duplicate NPI").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("Blank NPI").Range("A1")
  Sheets("Blank NPI").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
  Sheets("new not in old").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
  Sheets("old not in new").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("old not in new").Select
  Range("A1").Select
  
  Sheets("new not in old").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Columns("AB:AB").Select
  Selection.Cut
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight
  Range("A1").Select
  ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
  "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortNormal
  
  With ActiveWorkbook.Worksheets("new not in old").Sort
  .SetRange Range("A2:AK" & lr)
  .Header = xlNo
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Selection.AutoFilter
  Selection.AutoFilter
  ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
  :=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  
  Sheets("old not in new").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Selection.AutoFilter
  Columns("AB:AB").Select
  Selection.Cut
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight
  Range("A1").Select
  Selection.AutoFilter
  ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
  :=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("B1").Select
  Sheets("new not in old").Select
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("B1").Select
  
  
  Sheets("old not in new").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Range("B2").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
  Range("B2").Select
  Selection.AutoFill Destination:=Range("B2:B" & lr)
  'Range("B2:B5000").Select
  
  
  Sheets("new not in old").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Range("B2").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
  Range("B2").Select
  Selection.AutoFill Destination:=Range("B2:B" & lr)
  'Range("B2:B5000").Select
  Range("B1").Select

End Sub

Note Code Tag
In future please use code tags when posting code.​
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.​


----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
 
Upvote 0
Hi Dante Amor,

Thanks for the warm welcome! This did help with my process but I do have one more question. Is possible to not have the ranges set to B5000 or 284 because each week the row count is different so I do not want to have to go in a manual update the set range to account for additional rows?
 
Upvote 0
Hi DanteAmor,

When I run the code for Macro(15). I get an error: run-time error '1004': Autofilter method of range class failed. I have it bolded below.

Sub Macro15()
Dim lr As Long

Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
"old not in new", "new not in old")).Select
Sheets("Blank NPI").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("New Raw Data").Select
Cells.Select
Range("U1").Activate
Selection.Copy
Sheets("Old Raw Data").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("New Raw Data").Select
Cells.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name

strFile = Application.GetOpenFilename

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh
End With
Selection.AutoFilter
Range("A1").Select
Columns("AB:AB").Select
ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Add _
Key:=Range("AB:AB"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
Sheets("NPI < 10 digits").Select
Range("A:A").Select
Selection.AutoFilter
Range("A1").Select
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
Sheets("Duplicate NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("Blank NPI").Range("A1")
Sheets("Blank NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
Sheets("new not in old").Select
Range("A1").Select
Selection.AutoFilter
Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
Sheets("old not in new").Select
Range("A1").Select
Selection.AutoFilter - here is where I get run-time error '1004': Autofilter method of range class failed.
Sheets("old not in new").Select
Range("A1").Select

Sheets("new not in old").Select

lr = Range("A" & Rows.Count).End(3).Row

Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
"A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

With ActiveWorkbook.Worksheets("new not in old").Sort
.SetRange Range("A2:AK" & lr)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Sheets("old not in new").Select

lr = Range("A" & Rows.Count).End(3).Row

Selection.AutoFilter
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("new not in old").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select


Sheets("old not in new").Select

lr = Range("A" & Rows.Count).End(3).Row

Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & lr)
'Range("B2:B5000").Select


Sheets("new not in old").Select

lr = Range("A" & Rows.Count).End(3).Row

Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & lr)
'Range("B2:B5000").Select
Range("B1").Select

End Sub
 
Upvote 0
I actually removed the duplicate code but then I get another error: run-time error'91': object variable or with block variable not set for Macro (15) as well.
Selection.AutoFilter
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("new not in old").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select


Sheets("old not in new").Select

lr = Range("A" & Rows.Count).End(3).Row

Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & lr)
'Range("B2:B5000").Select


Sheets("new not in old").Select

lr = Range("A" & Rows.Count).End(3).Row

Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & lr)
'Range("B2:B5000").Select
Range("B1").Select

End Sub
 
Upvote 0
Try this Macro15

Make sure you have data on your sheets and in the file you are going to read.

VBA Code: 
Sub Macro15()
  Dim lr As Long
  Application.ScreenUpdating = False

  Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
    "old not in new", "new not in old")).Select
  Sheets("Blank NPI").Activate
  Cells.Select
  Selection.Delete Shift:=xlUp
  Sheets("New Raw Data").Select
  Cells.Select
  Range("U1").Activate
  Selection.Copy
  Sheets("Old Raw Data").Select
  Range("A1").Select
  ActiveSheet.Paste
  Sheets("New Raw Data").Select
  Cells.Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlUp
  Range("A1").Select
  Dim ws As Worksheet, strFile As String
  
  Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name
  
  strFile = Application.GetOpenFilename
  
  With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
  .TextFileParseType = xlDelimited
  .TextFileOtherDelimiter = "|"
  .Refresh
  End With
  Selection.AutoFilter
  Range("A1").Select
  Columns("AB:AB").Select
  ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Add _
  Key:=Range("AB:AB"), SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
  Sheets("NPI < 10 digits").Select
  Range("A:A").Select
  Selection.AutoFilter
  Range("A1").Select
  Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
  Sheets("Duplicate NPI").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("Blank NPI").Range("A1")
  Sheets("Blank NPI").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
  Sheets("new not in old").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
  
  Sheets("old not in new").Select
  Range("A1").Select
  Selection.AutoFilter
  Sheets("old not in new").Select
  Range("A1").Select
  
  Sheets("new not in old").Select
  lr = Sheets("new not in old").Range("A" & Rows.Count).End(3).Row
  
  Columns("AB:AB").Select
  Selection.Cut
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight
  Range("A1").Select
  ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
  "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortNormal
  
  With ActiveWorkbook.Worksheets("new not in old").Sort
  .SetRange Range("A2:AK" & lr)
  .Header = xlNo
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Selection.AutoFilter
  Selection.AutoFilter
  ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
  :=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  
  Sheets("old not in new").Select
  lr = Range("A" & Rows.Count).End(3).Row
  
  Selection.AutoFilter
  Columns("AB:AB").Select
  Selection.Cut
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight
  Range("A1").Select
  Selection.AutoFilter
  
  ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
  :=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("B1").Select
  
  
  Sheets("new not in old").Select
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("B1").Select
  
  Sheets("old not in new").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Range("B2").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
  Range("B2").Select
  Selection.AutoFill Destination:=Range("B2:B" & lr)
  Range("B2:B" & lr).Select
  
  Sheets("new not in old").Select
  
  lr = Range("A" & Rows.Count).End(3).Row
  
  Range("B2").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
  Range("B2").Select
  Selection.AutoFill Destination:=Range("B2:B" & lr)
  Range("B2:B" & lr).Select
  Range("B1").Select

  Application.ScreenUpdating = True
End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

🫡
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
211
Coco1
C
J
  • Question
Rename a Sheet
Replies
2
Views
112
Peter_SSs
Peter_SSs
C
  • Question
Daily Run data (copy data in loop by daily in worksheet)
Replies
3
Views
165
dermie_72
D
B
  • Question
How to Create a Loop to ignore BLANK CELLS
Replies
3
Views
239
Alex Blakenburg
A
D
  • Solved
Use the same macro on a weekly file which contains date info
Replies
1
Views
259
John_w
J

Forum statistics

Threads
1,215,069
Messages
6,122,958
Members
449,096
Latest member
Anshu121

Share this page

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
Back
Top