VBA that loops through validation and copies and pastes updated values

Nordmane

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

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

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,132
Messages
5,628,892
Members
416,352
Latest member
Lunox01

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