Hi! Thanks for the responses, AussieBear I was trying to avoid making a dummy file because it would take me an hour+ to replicate but it looks like the issue is indeed the validation lists. I tested making the files this morning step by step and the one that broke the files was indeed step 5 - the files work fine until I run the final macro to refresh the data validation lists. I make the val list data by firstly by creating a dictionary of unique items and then the last step I have is joining the keys from the dictionary to a list using "," as the data behind the validation lists, which as suggested by Aflatoon seems to be more than the 255 character limit allowed.
What's the best way to build these val lists generally to avoid that 255 character limit error? Should I have the dictionaries print to a hidden tab on the files and set the val list from the range?
Thanks so much!!
I pasted the code I've been using below:
Sub refresh_voucher_page()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws1 As Worksheet
Dim Voucher As Worksheet
Dim lastlinews As Long
Dim lastcolumnws As Long
Set Voucher = wb.Sheets("4005 Voucher")
shname = InputBox("What's the data sheet name?")
Set ws1 = wb.Sheets(shname)
lastlinews = ws1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
'find last column
lastcolumnws = ws1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Dim D_Client As Object
Dim D_Date As Object
Set D_Client = CreateObject("scripting.dictionary")
Set D_Date = CreateObject("scripting.dictionary")
Set D_TimeStamp = CreateObject("scripting.dictionary")
For i = 2 To lastlinews
D_Client(ws1.Range("A" & i).Value) = 1
D_Date(ws1.Range("B" & i).Value) = 1
D_TimeStamp(ws1.Range("K" & i).Value) = 1
Next
Dim val_list_client As String
Dim val_list_dates As String
Dim val_list_timestamp As String
val_list_client = Join(D_Client.keys, ",")
val_list_dates = Join(D_Date.keys, ",")
val_list_timestamp = Join(D_TimeStamp.keys, ",")
Voucher.Range("F1").Validation.Delete
Voucher.Range("F1").Validation.Add Type:=xlValidateList, Formula1:=val_list_client
Voucher.Range("J2").Validation.Delete
Voucher.Range("J2").Validation.Add Type:=xlValidateList, Formula1:=val_list_dates
Voucher.Range("F4").Validation.Delete
Voucher.Range("F4").Validation.Add Type:=xlValidateList, Formula1:=val_list_timestamp
End Sub