PDA

View Full Version : Multi-step workbook/sheet automation with VBA, macros, INDEX MATCH combination



BlondieC
02-11-2016, 09:46 AM
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:


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.
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.
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.
To Be Checked: I believe the user is keeping "notes" of data that needs to be checked on this worksheet. Not in scope.
Master List: Original data not to be touched. Not in scope.

Desired State
Worksheets:
WorkingCopy:


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.
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.
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.
The data in columns M and N will then be concatenated in column P.
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


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


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?
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.
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.

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.

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.

SamT
02-11-2016, 06:28 PM
I think pure code is the way to go. But then, my Hammer is VBA and all problems look like nails to me.


Desired State
Worksheets:
WorkingCopy:



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.
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.
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.
The data in columns M and N will then be concatenated in column P.



I recommend that you first remove double spaces, sort alphabetically, remove duplicates, then manually vette and continue from there.




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.

See 15394 for ideas


Individual street name worksheets:



For each specific worksheet, copy and paste all data from WorkingCopy worksheet, matched on street name, to that specific worksheet.


Can't do that! The reference list of Street names is now Normalized and no longer includes typos and extra spaces. There are two options, the one I recommend is Using the VBA Event "SelectionChange" to record the Value of the current cell, then use the VBA Event "WorksheetChange" to run a procedure that, in the Master Sheet, or a working copy of it, (and in the Working sheet,) replaces all instances of the recorded original value to the new corrected value. Finally, after the User has double checked the correction, use the VBA Event, "DoubleClick" to Remove Duplicates again. [coder Note: The Variable "OriginalValue" must reflect the replacement value]

Everything else is just normal requirements for VBA Projects

BlondieC
02-11-2016, 07:46 PM
Sam thank you so much for reading this through and helping me out - I really mean that! You make it sound quite straight-forward and simple and I'll tackle it one piece at a time. I had some time to work on it today and made a few changes and cleaned up a couple of glitches. I should have time to work on it tomorrow following your direction and test, test, test as I go. When I saw someone in a different department doing everything manually I asked them to send me the workbook to see what automation I could put in place and it's different from anything I've previously attempted but that's the fun part. I've asked her to also push her Manager to get a raw data extract as she is currently manually entering all 8 columns and 1,551 rows of data. I just about died when I saw that. Thank you again!

SamT
02-11-2016, 08:18 PM
see this for ideas using Raw CSV files Process All CSV Files In SubFolders (http://www.vbaexpress.com/forum/showthread.php?52649-Process-All-CSV-Files-In-SubFolders)