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!
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