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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
can you provide the expected output?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.