Excel VBA - Run a macro based on a range of dropdown lists

[Bobby77]

It's been a few years since VBA class so please respond as if you were writing in an "Excel VBA for Dummies" book.
In column G, each cell in range G2:G1001 is an individual data validation drop down list of all the worksheets in my workbook. I have a macro that when you select "Questar" from the dropdown in cell "G2", it copies cells A2:F2 and pastes them to the worksheet titled "Questar" in the first empty row. That all works fine.
However, my issue is it only works in cell G2. I have data in rows 2-1001 and I need this to work for all cells G2:G1001. Here is what I have so far and works for cell "G2":
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G2:G1001")) Is Nothing Then
Select Case Range("G2")
Case "Questar": Questar
End Select
End If
End Sub</code>I think that the Select Case Range("G2") needs to change but I have tried everything. I will eventually add more cases but I want to get one worksheet working correctly before adding more cases and macros. Any suggestions? Please let me know if more information is needed.

 

[Arul.rajesh]

need to look at the macro that does the copying

 

[Bobby77]

Thanks for the quick reply. Sorry, I didn't think it was needed. Here it is:

Sub Questar()
' Questar Macro


Worksheets("AFCU Auto-Add").Range(ActiveCell.Offset(0, -6), ActiveCell.Offset(0, -1)).Copy
Worksheets("Questar").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True


Sheets("AFCU Auto-Add").Select
Range(ActiveCell.Offset(0, -6), ActiveCell.Offset(0, -1)).Select
Application.CutCopyMode = False
Selection.ListObject.ListRows(1).Delete
Range("G2").Select


End Sub

 

[Arul.rajesh]

try changing

select case range("G2")

to

select case Target.Value

 

[Bobby77]

Just tried but I'm getting run-time error '13': Type mismatch and it is pointing to this line of code:

Case"Questar": Questar

I don't really know what this error means or how to fix it. Any thoughts?