come creare macro perché i valori da copiare in DropDown stanno aumentando sempre. e devo dare origine a creare elenchi nella validazione dei dati

Vorrei fare drop down list in sheet2 che contiene valori dalla colonna sheet1. Ho provato questo codice.

Sub testIt() Dim r As Long, endRow As Long, pasteRowIndex As Long endRow = 10 ' of course it's best to retrieve the last used row number via a function pasteRowIndex = 1 For r = 1 To endRow 'Loop through sheet1 and search for your criteria If Cells(r, Columns("B").Column).Value = "YourCriteria" Then 'Found 'Copy the current row Rows(r).Select Selection.Copy 'Switch to the sheet where you want to paste it & paste Sheets("Sheet2").Select Rows(pasteRowIndex).Select ActiveSheet.Paste 'Next time you find a match, it will be pasted in a new row pasteRowIndex = pasteRowIndex + 1 'Switch back to your table & continue to search for your criteria Sheets("Sheet1").Select End If Next r

le colonne in foglio1 cambiano spesso. quindi deve creare codice dinamico VBA Macro.

Vi prego di guidarmi per questa domanda.

Per il tuo caso, non credo che hai bisogno di una macro per gestire l'elenco a discesa, ma forse la validation dei dati farà.
Creare un nuovo foglio di lavoro,
Ho un foglio di lavoro che contiene i seguenti dati nella colonna A
immettere qui la descrizione dell'immagine
Al foglio di lavoro che voglio la list di dropdown, ho solo evidenziare la cella e fare clic sul button di validation dei dati al nastro dei dati

Nella validation dei dati, creare l'impostazione seguente

Fare clic sul button OK e l'elenco verrà creato
immettere qui la descrizione dell'immagine

Poiché nelle colonne nel foglio di lavoro (sorgente) continua a cambiare, è necessario scrivere la macro per copiare tutta la colonna necessaria escludere l'intestazione della colonna nel foglio di lavoro successivo (ad es. Foglio di lavoro che crea l'elenco a discesa).

Modificato: Codice per rilevare la colonna dei criteri e copiare la colonna

 Option Explicit Dim MyWorkbook As Workbook Dim MyWorksheet As Worksheet Dim MyWorksheet2 As Worksheet Dim WantedColumn As Long Dim ColumnPointer As Long Sub copyCriteria() Set MyWorkbook = Workbooks(ActiveWorkbook.Name) Set MyWorksheet = MyWorkbook.Sheets("Sheet6") Set MyWorksheet2 = MyWorkbook.Sheets("Sheet5") For ColumnPointer = 1 To MyWorksheet.Cells(1, Columns.Count).End(xlToLeft).Column If MyWorksheet.Cells(1, ColumnPointer).Value = "ColumnE" Then MyWorksheet.Columns(ColumnPointer).Copy MyWorksheet2.Range("A1").PasteSpecial xlPasteValues Application.CutCopyMode = False MyWorksheet2.Rows("1:1").Delete Shift:=xlUp End If Next End Sub Opzione esplicita Option Explicit Dim MyWorkbook As Workbook Dim MyWorksheet As Worksheet Dim MyWorksheet2 As Worksheet Dim WantedColumn As Long Dim ColumnPointer As Long Sub copyCriteria() Set MyWorkbook = Workbooks(ActiveWorkbook.Name) Set MyWorksheet = MyWorkbook.Sheets("Sheet6") Set MyWorksheet2 = MyWorkbook.Sheets("Sheet5") For ColumnPointer = 1 To MyWorksheet.Cells(1, Columns.Count).End(xlToLeft).Column If MyWorksheet.Cells(1, ColumnPointer).Value = "ColumnE" Then MyWorksheet.Columns(ColumnPointer).Copy MyWorksheet2.Range("A1").PasteSpecial xlPasteValues Application.CutCopyMode = False MyWorksheet2.Rows("1:1").Delete Shift:=xlUp End If Next End Sub Application.CutCopyMode = Falso Option Explicit Dim MyWorkbook As Workbook Dim MyWorksheet As Worksheet Dim MyWorksheet2 As Worksheet Dim WantedColumn As Long Dim ColumnPointer As Long Sub copyCriteria() Set MyWorkbook = Workbooks(ActiveWorkbook.Name) Set MyWorksheet = MyWorkbook.Sheets("Sheet6") Set MyWorksheet2 = MyWorkbook.Sheets("Sheet5") For ColumnPointer = 1 To MyWorksheet.Cells(1, Columns.Count).End(xlToLeft).Column If MyWorksheet.Cells(1, ColumnPointer).Value = "ColumnE" Then MyWorksheet.Columns(ColumnPointer).Copy MyWorksheet2.Range("A1").PasteSpecial xlPasteValues Application.CutCopyMode = False MyWorksheet2.Rows("1:1").Delete Shift:=xlUp End If Next End Sub

Quello che si sta tentando di fare può essere fatto con una gamma semplice denominata e con Validazione dati per utilizzare tale nome. Se non avete sentito parlare di Dynamic Ranges, allora dovresti leggere.

Se Sheet1 dispone solo della colonna 1 per l'elenco DropDown tramite Validation dei dati, è consigliabile utilizzare un'intervallo nominato invece di un intervallo fisso. Ma questa gamma nominata è dynamic (utilizzando la formula)! Vedere l'utilizzo di OFFSET .

Supponiamo che Sheet1 sia come sotto:
sheet1

Permetta di dire che il nome da utilizzare è MyList , quindi in Excel fare clic su Nome Manager nella scheda Formulazioni e mettere in basso come l'intervallo si riferisce a:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))
NameRange
NameManager

Ora in Sheet2, la validazione dei dati viene posta su B2, quando la impostazione, una volta inserito nella sorgente =MyList , Excel lo mette in evidenza:
DataValidation_1

Poi l'elenco a discesa ha funzionato:
DropDownList1

Ora, se si aggiungono dati al tuo elenco (colonna A sul foglio 1), MyList si espande automaticamente e quindi l'elenco a discesa DataValidation!

Notare che l'elenco salirà alla prima cella vuota della colonna A, quindi NO GAPS!

Godere!