Hi all,
Could I kindly ask for help with a basic code on the following:
- i have a first tab with data arranged by columns : entity, analyst, date of input, region, etc.
- i want to create in a second tab a table with various information from the first tab, which will fill automatic by my code, as soon as I select from a drop down list the name of the entity i want the informations for
There is no need for the full code, but I would be extremely grateful if you can help with the basic code to paste cells based on a condition and any other tip!!
Thank you!!!
Assume the drop down in column G of Sheet 1 has 3 values of "A", "B" and "C" for the user to select. Also assume these are validation droipdown and not combobox.
2 methods
1. If...Then...ElseIf...Then...End If method
Code:
With Sheets("Sheet1")
If .Range("G1").Value = "A" Then
.Range("A2:F5").Copy Sheets("Sheet2").Range("D2")
ElseIf .Range("G1").Value = "B" Then
.Range("A2:F5").Copy Sheets("Sheet2").Range("D3")
ElseIf .Range("G1").Value = "C" Then
.Range("A2:F5").Copy Sheets("Sheet2").Range("D4")
End If
End With
2. Select Case Method
Code:
Dim rng As Range
With Sheets("Sheet1")
Select Case .Range("G1").Value
Case "A": Set rng = Sheets("Sheet2").Range("D2")
Case "B": Set rng = Sheets("Sheet2").Range("D3")
Case "C": Set rng = Sheets("Sheet2").Range("D4")
End Select
Range("A2:F5").Copy rng
End With
Both methods do the same thing.
Thank you very much the quick reply. I may have been too brief on explaining the additional difficulty:
1. In my data tab, the name of columns represent the names of the rows i would like to have in the table tab
2. the table should have a specific project in every column ("EntityName" in image 1), under which i will show certain information (country, date, analyst, etc).
3. in my data tab, there are more columns that I need but I shouldnt delete anything in the data tab - so I needed to specifically add in the code which elements i need, for example for a given project A, i would need the columns "country' and "analyst" from the data tab to be my first two rows for the table tab (in reality the final table will have about 20-30 rows)
4. again, in my table tab, the name of the columns = project i am looking for. However, the selection of the project is made based on a drop down list which includes all the projects appearing in the data tab, because if not there would be too many columns in my table. The purpose of the table tab is to allow comparison between different projects that have appeared in the data tab (already subject to a filter) but on which i decide to put into a comparable table: once a project name is selected in cell D2 (second image), the rest of the rows should be automatically updated.
I am not sure if all this is possible...
Again, thank you very much for your time.
