VBA Code that Loops Through Validation List and Copies and Pastes Sheet into New Workbooks, dynamically named

[Nordmane]

Hello,

I have the following code and I keep receiving "Object Required" every time I run it. Can anyone tell me where I am going wrong? I am relatively new to VBA. Thanks in advance!

Sub DashboardToPDF()
Dim FolderName As String, fName As String
Dim inputRange As Range, r As Range, c As Range
Dim newWB As Workbook, currentWB As Workbook
Dim newS As Worksheet, currentS As Worksheet
    

Application.ScreenUpdating = False
'''' Open file dialog and choose folder
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = True Then
        FolderName = .SelectedItems(1) & "\"
    Else
        Exit Sub
    End If
End With

'''' Location of DataValidation cell
Set r = Worksheets("Formatted Template").Range("A1")
'''' Get DataValidation values
Set inputRange = Evaluate(r.Validation.Formula1)



        
'''' Loop through DataValidation list
For Each c In inputRange
    r.Value = c.Value
    fName = c.Value
    
''''Copy the data you need


ActiveSheet.Copy

''''Create a new file that will receive the data
Set newWB = Workbooks.Add
    With newWB
        Set newS = newWB.Sheets("Sheet1")
        newS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        ''''Save in 52
        Application.DisplayAlerts = False
        .SaveAs Filename:=FolderName & fName, FileFormat:=52
        Application.DisplayAlerts = True
    End With

   

Next c

Application.ScreenUpdating = True

End Sub

 

[rlv01]

In general, you would need to do something like this to pull the data from a different (but already open) workbook

'''' Location of External DataValidation cell
Set r = Application.Workbooks("SomeRandomWorkbook.xlsm").Worksheets("SomeRandomWorksheet").Range("A1")
'''' Get DataValidation values
Set externalinputRange = Evaluate(r.Validation.Formula1)

 

[Nordmane]

Hi, thanks for your response! I don' think I explained my problem very well. I'm sorry!

This following line of code is what I need changed, but I'm not sure how to do it:

For Each c In inputRange
r.Value = c.Value
fName = c.Value

Basically what I want is something like this (I know this line of code won't work but maybe it will intuitively show what I am going for):

For Each c In inputRange
r.Value = c.Value
fName = c.Value = VLOOKUP(A1,List!A:B,2,FALSE)

I want to reference the Validation list value and pull a separate "file name" value from a different worksheet in the same workbook. So, something like where fName = [Validation Value] which is equal to the VLOOKUP value based on the validation value.

I hope this is a better explanation. I'm sorry I've had a million questions on this and I really appreciate all of your help!