VBA that loops through validation and copies and pastes updated values

[Nordmane]

Hi,

I am almost to my goal of completing this VBA code. There is just one thing that I cannot figure out. When I run this 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
Dim sNm As String

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
    sNm = c.Value
    fName = Application.VLookup(sNm, Sheet2.Range("A1:B50"), 2, 0)

'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 & ".xlsx", _
 FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        Application.DisplayAlerts = True
    End With

Next c
Application.ScreenUpdating = True

End Sub

I generate all of the files based on the correct naming convention. However, each file has the same information in it. Basically, the loop is going through the validation list and naming the files based on the values in the list. But the loop is NOT updating the formulas that update the values on the dashboard and copying and pasting those values to each new workbook that I am saving.

Can anyone see what I need to tweak to get this loop to copy and paste the worksheet after the validation list updates the values?

Thanks in advance for any help! (Sorry if the answer is obvious, I'm fairly new to VBA)

 

[Rijnsent]

Hi Nordmane,
so if I understand you correctly the code should update a cell, calculate the sheet and copy-paste that info to a new file with that name. Some general debugging tips:

• don't run your code pressing F5 or play, that's too quick/too fast
• use F8 -> it goes step by step through your code
• and/or use F9 / breakpoints plus F5 (click left of the line of code, it should color dark red and get a circle in front of it)
• use the direct window (VBE menu: View or use CTRL+G) to output variables for debugging with debug.print variable_name
• with breakpoints/step-by-step progressing, use the local variables window (again: menu View) to check the values of your local variables on the fly
• With some screens: Debugging in Excel VBA

So step 1 is to check whether that cell is updated. That's what should happen at:

r.Value = c.Value

Stop the code after that line and see if that's happening. If that passes, it could be that the sheet is not calculated properly (e.g. switched to manual), you could add a sheet.calculate statement to make sure it calculates. And the next culprit could be the fact that you copy-paste everything including formulas, that might mean that some formulas are linked back to your A1 cell in your tool instead of to the individual exports. In that case you'd need to copy-paste values.
Hope that helps,
Cheers,
Koen