尝试更新宏中的范围以不设置范围

Question

我正在尝试更新我的宏，使宏中的设置范围不再硬编码，因为当运行新数据集的数据时，数据范围可能会更小或更大。我该如何更新我的宏来显示这一点。

我以为使用 .range(ss) 或使用工作表公式会起作用，但一直出现语法错误

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
        Worksheets("new not in old").Range("A2:AK2", "A:AK" & Worksheets("new not in old").Range("A:AK" & Rows.Count).End(xlUp).Row).Select
        .Range (ss)
        .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 _
        Worksheets("new not in old").Range("A1", "A" & Worksheets("new not in old").Range("A" & Rows.Count).End(xlUp).Row).Select, 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 _
       Worksheets ("old not in new") .Range  ("A1", "A" & worksheets ("old not in new") .range('A" & rows .Count) .end(xlup) .row ) .Select, 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(ss)
    Range(ss).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(ss)
    Range(ss).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
    Worksheets ("new not in old").Range("A2:AK2", "A:AK" & worksheets ("new not in old").range("A" & rows.Count).end(xlup).row).Select,
        .Range (ss)
        .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 _
        Worksheets ("new not in old").Range("A1", "A" & worksheets ("new not in old").range("A" & rows.Count).end(xlup).row).Select, 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 _
        Worksheets ("old not in new").Range("A1", "A" & worksheets ("old not in new").range("A" & rows.Count).end(xlup).row).Select, 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(ss)
    Range(ss).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(ss)
    Range(ss).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
        Worksheets("new not in old").Range("A2:AK2", "A:AK" & Worksheets("new not in old").Range("A:AK" & Rows.Count).End(xlUp).Row).Select
        .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 _
    Worksheets("new not in old").Range("A1", "A" & Worksheets("new not in old").Range("A" & Rows.Count).End(xlUp).Row).Select, 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(ss)
    Range(ss).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(ss)
    Range(ss).Select
    Range("B1").Select

End Sub