I am trying to extend data validation to multiple sheets but running into issues with the tables being cutoff.
will first explain what I am trying to do, step-by-step.
1 – I receive a master file from IT. The master file
contains customer information for multiple branches.
2 – I need employment information for each customer, which includes Industry Type and Occupation Type (columns I-J). Choices
are from a validation table only (columns DA-FC).
3 – Here is where it gets tricky. I am splitting the master file into multiple sheets according to branch number in column A
using the following code:
lngLastRow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lngLastRow
lngStoreID = Sheets("Data").Cells(i, 1).Value
Set MadeSheet = Sheets(CStr(lngStoreID))
If MadeSheet Is Nothing Then
ActiveSheet.Name = lngStoreID
For j = lngLastRow To 2 Step -1
Application.StatusBar = "Processing Main: " & i & " of " & lngLastRow &
". Processing sub " & j & " of " & lngLastRow
If Sheets(CStr(lngStoreID)).Cells(j, 1).Value <> lngStoreID And Sheets(CStr(lngStoreID)).Cells(j, 1).Value
<> "" Then
Set MadeSheet = Nothing
Creating the sheets works fine. However, the data validation only partially works for each new sheet created. I added this
bit of code to add the validation tables to each sheet but it appears that the name definitions are getting all messed up.
Dim ws As Worksheet
Dim lngIndex As Long
For lngIndex = .Sheets.Count To 5 Step -1
Application.DisplayAlerts = False
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Application.DisplayAlerts = True
There is a reason I am adding the table to each sheet and that is I am using an email application to send each individual
sheet to a different person for each branch.
I have spent hours trying to get this working, but no luck. Any
assistance is greatly appreciated. Thanks.