Create a list with values from multiple Excel sheets

Question

I am working on VBA code that will run through a dropdown menu in Sheet "voorblad".

For every value in the dropdown menu I want to copy the value "Voorblad".range"K9" and "Calculation".range"G35" and paste it in a sheet called "LIST Sheet".

The VBA is meant to create a list of all the values.

Sub CreateList()

Dim Answer As VbMsgBoxResult

Answer = MsgBox("Validation message!", vbYesNoCancel, "CreateList")

If Answer = vbYes Then

    Application.ScreenUpdating = False

    With Sheets("Voorblad").Range("K9").Validation
        For Each rCell In Range(.Formula1)
            .Parent.Value = rCell.Value

            Sheets("Voorblad").Select
            Range("K9").Select
            ActiveCell.Copy
            Sheets("LIST Sheet").Select
            Range("B2").Select
            Selection.PasteSpecial Paste:=xlPasteValues
            Sheets("LIST Sheet").Select
            ActiveCell.Offset(1, 0).Select

            Sheets("Calculation").Select
            Range("G35").Select
            ActiveCell.Copy
            Sheets("LIST Sheet").Select
            Range("G2").Select
            Selection.PasteSpecial Paste:=xlPasteValues
            Sheets("LIST Sheet").Select
            ActiveCell.Offset(1, 0).Select

        Next rCell
        .Parent.Value = ""

    End With

    Application.ScreenUpdating = True

    MsgBox "Export geslaagd! Het PDF is opgeslagen in jouw Documenten"
End If

End Sub

The VBA code is not creating a list. Every time the macro runs through the code it will reselect the pre-selected cells at Range("B2") and Range("G2"). I want it to paste the values one row down. Eventually this must create a list.

First and foremost, avoid select/activate where possible. E.g., Sheets("Calculation").Range("G35").Copy insted of selecting sheet, cell, then copying cell — Cyril

James James · Accepted Answer · 2020-01-07T16:49:00

To create the list properly, you need to ensure you are moving to the next empty cell on the target worksheet (List Sheet). Try something like

Sheets("LIST Sheet").Range("G2").End(XLdown).Offset(1,0).PasteSpecial Paste:=xlPastValues

This should put the value in the next blank cell. Same with column G

HTH

Create a list with values from multiple Excel sheets

1 Answers