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

Nordmane

New Member
Joined
Mar 26, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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!

VBA 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.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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
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

New Member
Joined
Mar 26, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 16, 2017
Messages
771
Ok, but "beginning of the For Loop" is not quite specific enough. Exactly which statement produces the error? This one"
VBA Code:
For Each c In inputRange
or another one?
 

Nordmane

New Member
Joined
Mar 26, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 16, 2017
Messages
771
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:

VBA Code:
Set inputRange = Evaluate(r.Validation.Formula1)

inputRange is a range object, which means that this part:
VBA Code:
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

New Member
Joined
Mar 26, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 26, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
This is my current code:

VBA 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

Well-known Member
Joined
May 16, 2017
Messages
771
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.
VBA Code:
        Debug.Print "Folder Name: '" & FolderName & "'"
        Debug.Print "File Name: '" & fName & "'"
        Debug.Print "Full Path Name: '" & FolderName & fName & "'"
        
        .SaveAs Filename:=FolderName & fName, FileFormat:=52
 

Nordmane

New Member
Joined
Mar 26, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA 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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,497
Messages
5,636,662
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top