VBA How to create a data validation loop with single value copy/paste and offset

Question

I want to create a macro that loops through a data validation list I have located in cell C8 on the sheet named Gym Weekly Template. For each value in the data validation list (the list is made up of data from Range A6:A45 from sheet "Testing Data"), I have a vlookup value that generates in cell W73 of the same sheet.

I want to paste each value from cell W73 to a new worksheet named Gym Load Monitoring starting at cell B2 and down the B column, ending the macro once the data validation list has been looped through. If possible, if I was to run the macro again I want it to recognize there is data in column B in worksheet Gym Load Monitoring and paste the values in the next blank column, and so on for each time, the macro is run. I've got the current code written but I have a feeling I am completely off track:

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


'Which cell has data validation
Set dvCell = Worksheets("Gym Weekly Template").Range("C8")

'Determine where validation comes from
Set inputRange = Evaluate(dvCell.Validation.Formula1)

i = 1
Application.ScreenUpdating = False
For Each c In inputRange
    dvCell = c.Value

    With Worksheets("Gym Load Monitoring")
        ThisWorkbook.Sheets("Gym Weekly Template").Range("W73").Copy.Range("B" & .Rows.Count).End (xlUp)

Next c
Application.ScreenUpdating = True


End Sub

I'm not very experienced with VBA coding so this is probably wrong. I know I need to add in Offset but not sure where.

Tim Williams Tim Williams · Accepted Answer · 2018-10-04T01:03:52

Added few additional variables to reduce repetition, and a check to make sure you're writing to an empty column each time you run your Sub.

Untested:

Sub PasteLoads()
    Dim shtGWT As Worksheet, shtGLM As Worksheet
    Dim dvCell As Range
    Dim inputRange As Range, resultRange As Range
    Dim c As Range
    Dim i As Long, nextCol As Long


    Set shtGWT = Worksheets("Gym Weekly Template")
    Set shtGLM = Worksheets("Gym Load Monitoring")

    'Which cell has data validation
    Set dvCell = shtGWT.Range("C8")
    Set resultRange = shtGWT.Range("W73")

    'Determine where validation comes from
    Set inputRange = Evaluate(dvCell.Validation.Formula1)

    nextCol = 2
    'find an empty column
    Do While Application.CountA(shtGLM.Cells(2,nextCol).Resize(500, 1)) > 0
        nextCol = nextCol + 1
    Loop

    i = 2
    Application.ScreenUpdating = False
    For Each c In inputRange.Cells
        dvCell.Value = c.Value
        shtGLM.Cells(i, nextCol).Value = resultRange.Value
        i = i + 1
    Next c
    Application.ScreenUpdating = True


End Sub

VBA How to create a data validation loop with single value copy/paste and offset

1 Answers