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](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) where possible. E.g., `Sheets("Calculation").Range("G35").Copy` insted of selecting sheet, cell, then copying cell — Cyril, Jan 07 '20 at 16:28
^ on top of that, try to find the [**last used row**](https://stackoverflow.com/q/11169445/9758194). — JvdV, Jan 07 '20 at 16:31

score 0 · Answer 1 · edited Jan 07 '20 at 18:56

0

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

edited Jan 07 '20 at 18:56

Tim Williams

154,628
8
97
125

answered Jan 07 '20 at 16:49

James

68
8

Create a list with values from multiple Excel sheets

1 Answers1