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]

1. Which line of your code produces this error?
2. Is "Object Required" the only thing displayed when you get the error, or is there other information it displayed which you did not include in your post?

 

[Nordmane]

Hi! Thanks for the quick response. Only Object Required displays. When I run it line by line the Object Required appears at the beginning of the For Loop but does not specify a line.

 

[rlv01]

Ok, but "beginning of the For Loop" is not quite specific enough. Exactly which statement produces the error? This one"

For Each c In inputRange

or another one?

 

[Nordmane]

When I'm running through it line by line the error occurs after I hit F8 while the highlighter is over the statement "Set inputRange = Evaluate(r.Validation.Formula1)". So I believe it starts at the line you are referring to. However, when I close the error message box, no specific statement is highlighted for debugging.

 

[rlv01]

When I'm running through it line by line the error occurs after I hit F8 while the highlighter is over the statement "Set inputRange = Evaluate(r.Validation.Formula1)". So I believe it starts at the line you are referring to. However, when I close the error message box, no specific statement is highlighted for debugging.

So then this would be the statement producing the error:

Set inputRange = Evaluate(r.Validation.Formula1)

inputRange is a range object, which means that this part:

Evaluate(r.Validation.Formula1)

must evaluate to a range object or you will get an error. Clearly that is not happening. The statement suggest that you are attempting to use the data validation formula for cell Worksheets("Formatted Template").Range("A1") to somehow evaluate to a range. You will need to look at the data validation formula1 property to understand what's wrong.

 

[Nordmane]

Hi,

Sorry for the late reply. I now have the code getting to the point where it is copying and pasting the range I want paste on the validation list. However, it is breaking at the .SaveAs line with this error: "Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed". I'm unsure why this is an issue? Do you see anything glaring that could be causing this?

Thanks so much for all of your help so far!

 

[Nordmane]

This is my current code:

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.Range("A:O").Select
Selection.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:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False

        'Save in CSV
        Application.DisplayAlerts = False
        .SaveAs Filename:=FolderName & fName, FileFormat:=52
        Application.DisplayAlerts = True
    End With

   

Next c
Application.ScreenUpdating = True

End Sub

 

[rlv01]

I suspect you have a bad value for fname or foldername. Add some debug print statements just before .SaveAs. Run the macro and after it crashes, inspect the debugger "immediate" window to see what debug.print reports.

        Debug.Print "Folder Name: '" & FolderName & "'"
        Debug.Print "File Name: '" & fName & "'"
        Debug.Print "Full Path Name: '" & FolderName & fName & "'"
        
        .SaveAs Filename:=FolderName & fName, FileFormat:=52

 

[Nordmane]

Hi,

Thanks for all of your help so far. I have the code working more or less but I have another question.

I need to have the saved file named based on the value in the validation list, but pulled from another sheet in the workbook.

So, for example if the validation list value (in the code c.Value) equals Bobcat, I want fName in the SaveAs statement to equal Cats, which would be a value stored in another sheet in the work book.

Again this is my current code for reference:

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.Range("A:O").Select
Selection.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:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False

        'Save in CSV
        Application.DisplayAlerts = False
        .SaveAs Filename:=FolderName & fName, FileFormat:=52
        Application.DisplayAlerts = True
    End With

   

Next c
Application.ScreenUpdating = True

End Sub