[VBA] Loop through Data Validation and Print Output

Seba Robles

New Member
Joined
May 16, 2018
Messages
33
I have the following code with which I want to loop through all the data validation options available in the drop down list and copy/paste that output of the data validation option into MS Word.

The code "works"... However, when it pastes the report into MS Word, it pastes the last list option selected by the user instead of looping through the list and changing the report responses.

Say, the data validation has "Option1" and "Option2".. For each of the Options there is a template/report that has different values depending on the data validation selection. When I run the code, it will copy and paste the report for Option1 or Option2 (regarding on which was last selected by the user) TWICE (because there are only two options in the dropdown)....

Any idea how I could fix this?

Code:
Sub GenerateReports()

Dim dvCell, inputRange, c As Range
Dim i As Long
Dim objWord, objDoc As Object

Set dvCell = Worksheets("Reports").Range("D3")

Set inputRange = Evaluate(dvCell.Validation.Formula1) 'My data validation is not a formula but a dynamic range instead

Set objWord = CreateObject("Word.Application")

Set objDoc = objWord.Documents.Add

i = 1

For Each c In inputRange

    dvCell = c.Value
 
    ActiveWindow.View = xlNormalView
    Range("A9:G48").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    objWord.Visible = True
    objWord.Selection.Paste
    objWord.Selection.TypeParagraph
    
i = I + 1

Next c

End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,205
The problem lies with the way you're declaring your variables. For example, in the following declaration...

Code:
Dim dvCell, inputRange, c As Range
...since a type has not been specified for dvCell and inputRange, the variables are typed as a Variant. So, in your code, first you assign a range object to dvCell, but then you assign it a value. In doing so, dvCell changes from an object variable assigned a range object to a regular variable assigned a value. So now this means that dvCell no longer refers to the range object, which in this case is cell D3. Therefore, try declaring your variables this way...

Code:
Dim dvCell [COLOR=#ff0000]As Range[/COLOR], inputRange [COLOR=#ff0000]As Range[/COLOR], c As Range
Dim i As Long
Dim objWord [COLOR=#ff0000]As Object[/COLOR], objDoc As Object
However, your code can be re-written as follows...

Code:
Sub GenerateReports()

    Dim objWord As Object
    Dim objDoc As Object
    Dim dvCell As Range
    Dim inputRange As Range
    Dim c As Range
    Dim i As Long
    
    ActiveWindow.View = xlNormalView
    
    Set dvCell = Range("D3")
    
    Set inputRange = Evaluate(dvCell.Validation.Formula1) 'My data validation is not a formula but a dynamic range instead
    
    Set objWord = CreateObject("Word.Application")
    
    objWord.Visible = True
    
    Set objDoc = objWord.Documents.Add
    
    For Each c In inputRange
        dvCell.Value = c.Value
        Range("A9:G48").CopyPicture Appearance:=xlScreen, Format:=xlPicture
        objWord.Selection.Paste
        objWord.Selection.TypeParagraph
    Next c
    
    Set objWord = Nothing
    Set objDoc = Nothing
    Set dvCell = Nothing
    Set inputRange = Nothing


End Sub
Hope this helps!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,344
Messages
5,486,309
Members
407,539
Latest member
ltwkuav

This Week's Hot Topics

Top