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
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
776
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
In general, you would need to do something like this to pull the data from a different (but already open) workbook
VBA Code:
'''' Location of External DataValidation cell
Set r = Application.Workbooks("SomeRandomWorkbook.xlsm").Worksheets("SomeRandomWorksheet").Range("A1")
'''' Get DataValidation values
Set externalinputRange = Evaluate(r.Validation.Formula1)
 

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.

Nordmane

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

Watch MrExcel Video

Forum statistics

Threads
1,129,795
Messages
5,638,367
Members
417,023
Latest member
Zimbo38

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