[VBA] Loop through Data Validation and Print Output

[Seba Robles]

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?

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

 

[Seba Robles]

Please help

 

[Domenic]

The problem lies with the way you're declaring your variables. For example, in the following declaration...

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

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

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!

 

[Seba Robles]

Awesome explanation, it works now. Thanks you very much for your help!