Rubin Shrestha
Rubin Shrestha
Blog

Trying to update ranges in macro to not have set range

by |Published

I am trying to update my macro to where the set ranges in the macro are not hard coded because when the data for the new data set is ran the data range may be smaller or larger. How do I update my macro to show this.

I thought using .range(ss) or using a worksheet formula would work but keep getting synax error

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

You may also like