Updating headings based on dynamic list (example attached - exciting!)

[PipBoy808]

Hello all,

Attached you'll see a representation of a real-life problem I'm trying to sort out via VBA.

In the 'Delivery Planning' sheet we have a list of US state codes that a fictional company operates in. The data in this sheet corresponds to the daily planned deliveries for each of these states.

In the 'Codes' sheet is a master list of all the states for which deliveries will need to be planned for the near future.

In the 'For Reference' sheet is a list of 50 state codes, which we don't really have to worry about.

I'm trying to write a macro that will update the list of state headings in 'Delivery Planning' to reflect the master list in 'Codes'. Sounds simple, right? I thought so too. Except that the code needs to account for more than just the headings. The delivery data in B4:AO132 will need to move around also so that the correct planned deliveries stay underneath the correct state. So, the code needs to insert columns too.

To add to this, it's not always the case that the update will simply need to add new states to the 'delivery planning' sheet, oh no! Sometimes states are removed from the master list and need to be removed from the planning sheet (deactivated states). The reverse is also true, in that sometimes states will be added to the master list and therefore need to be added to the planning sheet (newly activated states).

So, the 'Delivery Planning' headings need to reflect the 'Codes' table while preserving the data that's already in there. I guess I need code that inserts/deletes columns in the planning sheet as necessary, but that is aware of when columns need to be added or deleted specifically.

Thanks for your help!

 

[crouchsw]

I think you're handling this wrong - if you start moving around columns in the Delivery Planning sheet, you're going to risk losing data. There will bee a lot of other complexities (one big one is that state postal abbreviations are not in the same alphabetical order as the states themselves). I think the better method would be to:

1: Make sure that all State Codes are on the Delivery Planning Page, and in Alphabetical order
2: Use VBA to hide the inactive states and show the active ones (see below - also in a module in the file I uploaded)

The only downside is that if the user expects the order of the Delivery Planning page to match their input rather than alphabetical order, they might get confused.

Sub sort()
Dim DPC As Range 'address of a cell in the Delivery planning tab
Dim CC As Range ' address of a cell in the Codes tab
Dim DP As Worksheet 'Delivery Planning Tab
Dim Co As Worksheet 'Codes tab
Dim CA 'array of active state codes
Dim CN As Integer 'Number of active state codes
Dim Match As Boolean
Set DP = Sheets("Delivery Planning")
Set Co = Sheets("Codes")

Co.Select
Co.Range("B2").Select
Set CC = ActiveCell
Selection.End(xlDown).Select
CN = ActiveCell.Row - CC.Row
CC.Select
ReDim CA(1 To CN)
For i = 1 To CN
    CA(i) = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
Next
DP.Select
DP.Range("B3").Select
For i = 1 To 51
    Match = False
    For j = 1 To CN
        If ActiveCell.Value = CA(j) Then Match = True
    Next
    If Match = True Then
        ActiveCell.EntireColumn.Hidden = False
    Else
        ActiveCell.EntireColumn.Hidden = True
    End If
    ActiveCell.Offset(0, 1).Select
Next
End Sub

 

[PipBoy808]

I think you're handling this wrong - if you start moving around columns in the Delivery Planning sheet, you're going to risk losing data. There will bee a lot of other complexities (one big one is that state postal abbreviations are not in the same alphabetical order as the states themselves). I think the better method would be to:

1: Make sure that all State Codes are on the Delivery Planning Page, and in Alphabetical order
2: Use VBA to hide the inactive states and show the active ones (see below - also in a module in the file I uploaded)

The only downside is that if the user expects the order of the Delivery Planning page to match their input rather than alphabetical order, they might get confused.

Sub sort()
Dim DPC As Range 'address of a cell in the Delivery planning tab
Dim CC As Range ' address of a cell in the Codes tab
Dim DP As Worksheet 'Delivery Planning Tab
Dim Co As Worksheet 'Codes tab
Dim CA 'array of active state codes
Dim CN As Integer 'Number of active state codes
Dim Match As Boolean
Set DP = Sheets("Delivery Planning")
Set Co = Sheets("Codes")
 
Co.Select
Co.Range("B2").Select
Set CC = ActiveCell
Selection.End(xlDown).Select
CN = ActiveCell.Row - CC.Row
CC.Select
ReDim CA(1 To CN)
For i = 1 To CN
    CA(i) = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
Next
DP.Select
DP.Range("B3").Select
For i = 1 To 51
    Match = False
    For j = 1 To CN
        If ActiveCell.Value = CA(j) Then Match = True
    Next
    If Match = True Then
        ActiveCell.EntireColumn.Hidden = False
    Else
        ActiveCell.EntireColumn.Hidden = True
    End If
    ActiveCell.Offset(0, 1).Select
Next
End Sub

That is quite a clever solution. My real-life example doesn't use US states, but rather ingredient codes. I was only using US states so as to have a well-known point of reference. I'll give your idea a try today. My only worry is that a brand new code might be created out of nothing for a brand new ingredient, and would need to be inserted into the planning sheet from the master list. Would that not involve inserting a column for each brand new code?

 

[SirJB7]

Hi, PipBoy808!
If you need to automatically add rows or columns maybe you find this useful:
http://chandoo.org/forum/threads/au...w-rows-or-columns-are-added.13499/#post-80155
Hope it helps.
Regards!

 

[crouchsw]

That is quite a clever solution. My real-life example doesn't use US states, but rather ingredient codes. I was only using US states so as to have a well-known point of reference. I'll give your idea a try today. My only worry is that a brand new code might be created out of nothing for a brand new ingredient, and would need to be inserted into the planning sheet from the master list. Would that not involve inserting a column for each brand new code?

Yes; you would have two options there; you could insert a blank column in the correct place, or write a second macro that would do that, which should be pretty easy. Thinking about it, the columns wouldn't actually have to be in alphabetical order; they could be in whatever order you would like - the code looks for a match from the entire list, not for the next match.

 

[crouchsw]

One thing to check - Since you had states in your example, I had the code only look 51 times (50 states + DC). You'd need to change the maximum number in the second for loop. The way that I would do that is to have =COUNT(B2:B1000) (or whatever the first code is through some future maximum number of ingredients) in the Codes worksheet, then have VBA grab that number:

dim codecount
codecount = sheets("Codes").Range("A1") 'or wherever you put the formula - I don't remember how that page was laid out.
...
for i = 1 to codecount
....