Dropdown loop, copy and paste values named range to another worksheet

Hi Experts, I have a simple file attached which has a macro that loops through a Dropdown then does a copy and paste to the 'Output' sheet. This works fine, but I want to change the copy range from a single cell to a Named Range (called 'CopyRange') and to copy and paste values this range to the Output sheet.

Many thanks in advance!
Model----Loan-Level-v2.xlsm
scsnow2310Asked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
what about this?

Sub Engine()
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Worksheets("Model").Range("CopyRange").Copy
    Worksheets("Output").Range("A1").PasteSpecial xlPasteValues
    Application.ScreenUpdating = True
    Application.CutCopyMode = True
End Sub

Open in new window

0
 
Ryan ChongCommented:
your requirement seems not clear enough.

perhaps you can try and modify from this example:

Sub Engine()
    Dim dvCell As Range
    Dim inputRange As Range
    Dim c As Range
    Dim i As Long
    
    'Which cell has data validation
    Set dvCell = Worksheets("Model").Range("CopyRange")
    'Determine where validation comes from
    'Set inputRange = Evaluate(dvCell.Validation.Formula1)
    
    i = 1
    'Begin our loop
    Application.ScreenUpdating = False
        For Each c In dvCell
            Worksheets("Output").Cells(i, "A").Value = c.Value
            i = i + 1
        Next c
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
scsnow2310Author Commented:
Sorry, I forgot to post the code... Line 20 is what I needs changing. Currently it just copies Range("B7"). This needs to be changed to a named range called 'CopyRange', but I am not sure how to do this?

Sub Engine()
Dim dvCell As Range
Dim inputRange As Range
Dim c As Range
Dim i As Long


'Which cell has data validation
Set dvCell = Worksheets("Sheet1").Range("C3")
'Determine where validation comes from
Set inputRange = Evaluate(dvCell.Validation.Formula1)


i = 1
'Begin our loop
Application.ScreenUpdating = False
For Each c In inputRange
    dvCell = c.Value
    
    Worksheets("Sheet2").Cells(i, "A").Value = Worksheets("Sheet1").Range("B7").Value
    i = i + 1
Next c
Application.ScreenUpdating = True


End Sub

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Ryan ChongCommented:
can you provide the expected output?
0
 
scsnow2310Author Commented:
Hi Ryan,

So in the attached Model tab you will see a named range highlighted in yellow. I need to copy this range and paste it into the Output tab, for every value in the Dropdown.

Sub Engine()
Dim dvCell As Range
Dim inputRange As Range
Dim c As Range
Dim i As Long


'Which cell has data validation
Set dvCell = Worksheets("Model").Range("C3")
'Determine where validation comes from
Set inputRange = Evaluate(dvCell.Validation.Formula1)


i = 1
'Begin our loop
Application.ScreenUpdating = False
For Each c In inputRange
    dvCell = c.Value
    
    Worksheets("Output").Cells(i, "A").Value = Worksheets("Model").Range("B7").Value
    i = i + 1
Next c
Application.ScreenUpdating = True


End Sub

Open in new window

0
 
Ryan ChongCommented:
I need to copy this range and paste it into the Output tab, for every value in the Dropdown.
yes, so how should the "Output" looks like? exactly the same as what being displayed from "Model" ?
0
 
scsnow2310Author Commented:
Yes, the same, but obviously pasted below the previous in the Output tab.
0
 
scsnow2310Author Commented:
Many thanks Ryan - that works.  For completeness, the full code I am using is shown below.

Sub Engine()
Dim dvCell As Range
Dim inputRange As Range
Dim c As Range
Dim i As Long


'Which cell has data validation
Set dvCell = Worksheets("Model").Range("C3")
'Determine where validation comes from
Set inputRange = Evaluate(dvCell.Validation.Formula1)

i = 1
'Begin our loop
Application.ScreenUpdating = False
Application.CutCopyMode = False
   


For Each c In inputRange
    dvCell = c.Value
    Worksheets("Model").Range("CopyRange").Copy
    Worksheets("Output").Cells(i, "A").PasteSpecial xlPasteValues
    i = i + 12
Next c
Application.ScreenUpdating = True
Application.CutCopyMode = True

End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.