Multi-step workbook/sheet automation with VBA, macros, INDEX MATCH combination

[BlondieC]

Hi and thank you for looking at my thread. I have a little VBA experience, familiar with macros and use VLOOKUP quite a bit. I think INDEX MATCH is the way to go with this workbook and was able to get a sample with all the data on the same worksheet working but was not able to go beyond that. I've offered to help someone with this to automate it as much as possible but it does venture into areas I am not familiar with.

Current State
Workbook that starts with 5 worksheets - there will be multiple workbooks that each have the same 5 worksheets but with info that varies by city/town. The structure of the 5 worksheets will be the same in each workbook. I am hoping that I can get one workbook set up and "recycle" that workbook for each new city/town.
Worksheets:

1. WorkingCopy: 8 columns (A:H and this will be the same in each workbook), 1,551 rows (this will change in each workbook), numeric and alpha data which at this time the user has not done any specific formatting - it's all General. This worksheet is in scope for what I am trying to do.
2. StreetRange: This worksheet starts out blank but will hold a list of unique street names with address number ranges displayed by odd and even numbers. This worksheet is in scope for what I am trying to do.
3. Template: I have created this worksheet as a piece of the automation. It has the same 8 column headings (A1:H1) as the Master List worksheet. Columns are A:H, Old Box#, Civic#, Unit, Street Name, Street Type, Direction, Last Name, First Name. It has has a formula in K1 that will display the name of the worksheet =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255). This worksheet is in scope for what I am trying to do.
4. To Be Checked: I believe the user is keeping "notes" of data that needs to be checked on this worksheet. Not in scope.
5. Master List: Original data not to be touched. Not in scope.

Desired State
Worksheets:

WorkingCopy:

1. ​First on the WorkingCopy, the street name and street type columns D and E will be manually vetted by the user using the filter button so the user can correct typos etc. Columns are A:H, Old Box#, Civic#, Unit, Street Name, Street Type, Direction, Last Name, First Name.
2. Next the vetted street name and street type data from columns D and E will be copied and pasted into available columns M and N.
   1. Then code (below), is run to remove all spaces and once that is done Data > Remove Duplicates will be used to obtain the final list of unique street names.
   2. After the code is run, the data in columns M and N will then be concatenated in column P.
   3. The concatenated data in column P will then be copied and pasted as Value only in in column K2 with a heading in K1 of Street Name so that there is now a vetted, clean list - ideally using a macro for the copying and pasting etc which I should be able to record.

Sub RemoveAllSpaces2()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Selection.SpecialCells(xlConstants).Replace What:=Chr(160), _
        Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
    Selection.SpecialCells(xlConstants).Replace What:=Chr(32), _
        Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True    
End Sub

​

3. Based on the Street Name list in column K, code (below), will be run to accomplish various tasks.​

Option Explicit
Sub CreateAndNameWorksheets()    
    Dim c As Range
    Application.ScreenUpdating = False
    Sheets("WorkingCopy").Select
    For Each c In Range("K2:K55")
        c.Select
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = c.Value
        Sheets("Template").Cells.Copy
        ActiveSheet.Paste
        Range("A1").Select
        Application.CutCopyMode = False
        Sheets("WorkingCopy").Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=c.Value & "!A1", TextToDisplay:=c.Value
    Next c
    Application.ScreenUpdating = True    
End Sub

​

1. 
   [*=1]A worksheet will be created for each street name in the list in the range in column K. Is it possible to change the range to stop at the last used cell with data since the number of rows in this range will change with each workbook? Should I set it up as a table to do this?
   [*=1]The worksheet that is created is based on the Template worksheet I've created so the headings are the same and the street name is displayed in K1.
   [*=1]The list is then set up with hyperlinks for each worksheet via the street name. The hyperlinked list is currently on the WorkingCopy worksheet and the user has informed me she would like it on the StreetRange worksheet which I should be able to do by changing the above code.

2. Individual street name worksheets:​

• For each specific worksheet, copy and paste all data from WorkingCopy worksheet, matched on street name, to that specific worksheet. i.e. Copy and paste all data for Anderson St from the WorkingCopy worksheet, to the Anderson St worksheet and sort by Column A smallest to largest - all 8 columns of data would be copied from and pasted to. Columns are A:H, Old Box#, Civic#, Unit, Street Name, Street Type, Direction, Last Name, First Name.
• It would also be ideal if when a change is made to the WorkingCopy worksheet, the change is reflected in the specific worksheet for that street name.
• I am really stuck on this piece as I can't use VLOOKUP as the street name is not the leftmost column. I think INDEX MATCH is the way to go but I cannot get it to work when referencing a different worksheet. I also don't know what to use or how to get the data to automatically update when a change is made to the WorkingCopy. I use a cell link to do this but in this project the scope is much larger.

3. StreetRange:​

• The desired outcome for this worksheet is to the right of the list of street names that will be in column A, indicate the street number range for that street, by odd and even i.e, Anderson Street Odd 105 - 235 Even 102 - 230. My thoughts are to grab this info from the individual street name worksheets since it will be split out and sorted. With this being new to me it could be more efficient to grab it from the WorkingCopy - I'm not sure.