[VBA] Loop through Data Validation and Print Output

Seba Robles

Board Regular
Joined
May 16, 2018
Messages
71
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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:
Upvote 0
Solution
Awesome explanation, it works now. Thanks you very much for your help!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top