I'm trying to figure out what you want to do here. You have a list that you use as a data source for a combo box and you want to use a macro to update that list?
If that's the case then I would just have the macro paste all the data from the OwnerLog sheet to the Menus1 sheet and onMenus1 sheet make a named dynamic range as your combo box data source. If it is dynamic then it doesn't matter how far the data goes it will fill your combo box with only the cells that have names.
You could also run this update macro anytime data on the OwnerLog sheet changes, that way it is always up to date.
The code below will update your Menus1 sheet.
If you like it you can remove all your old code that I commented out.
Sub UpDateCOListOL()
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim rngSource As Range, rngTarget As Range
Dim lrSource As Long, lrTarget As Long
Application.ScreenUpdating = False
Set wsSource = ThisWorkbook.Sheets("OwnerLog")
Set wsTarget = ThisWorkbook.Sheets("Menus1")
lrSource = wsSource.Cells(Rows.Count, "A").End(xlUp).Row
lrTarget = wsTarget.Cells(Rows.Count, "AE").End(xlUp).Row
Set rngSource = wsSource.Range("A5:A" & lrSource)
Set rngTarget = wsTarget.Range("AE5")
If lrTarget > 4 Then wsTarget.Range("AE5:AG" & lrTarget).ClearContents
rngSource.Copy rngTarget.Offset(, 1)
rngSource.Offset(, 5).Copy rngTarget
'Copies OwnerId's in Non sort column AF
'rngTarget.Value = rngSource.Value 'on to next Column
'Set rngSource = wsSource.Range("rngsorLName")
'Set rngTarget = wsTarget.Range("af5:af50")
'Copies OwnerLN in to sort column AE
'rngTarget.Value = rngSource.Value
'Call SortOwnerLN
Application.ScreenUpdating = True
'ActiveWorkbook.Save
End Sub
Put the code below in the code for the OwnerLog sheet. Right click on the OwnerLog tab, choose view code and paste this code into the window.
After you paste the code look at the very top of the window and make sure it says OwnerLog Code to verify it's in the right place.
Private Sub Worksheet_Change(ByVal Target As Range)
UpDateCOListOL
End Sub
Bookmarks