0

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":

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

I think that the Select Case Range("G2") needs to change but I have tried everything.

Here is my Questar macro code:

Sub Questar() 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

I will eventually add more cases but I want to get one worksheet working correctly before adding more cases and macros. Any suggestions?

Improve this question
1
  • The simplest change would be to replace Select Case Range("G2") with Select Case Target.Value - that will cause it to look at the Target cell (or, unfortunately - because it complicates things, all cells) which generated the Change event. But if you need to let the Questar macro know which cells to copy, etc, you may need to pass something like Target.Row as a parameter to Questar so that it can then operate on the appropriate data. Commented Nov 8, 2016 at 3:34

1 Answer 1

Reset to default
0

EDIT: updated to single procedure, assuming all sheets exist which are named in column G...

Something like:

Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, c As Range, rngDel As Range On Error GoTo haveError Set rng = Intersect(Target, Range("G2:G1001")) If Not rng Is Nothing Then For Each c In rng.Cells If Len(c.Value) > 0 Then 'copy to appropiate sheet With ThisWorkbook.Worksheets(c.Value).Cells(Rows.Count, 1).End(xlUp) .Offset(1, 0).Resize(1, rng.Cells.Count).Value = _ c.EntireRow.Range("A1:F1").Value End With 'build up a range of rows to delete... If rngDel Is Nothing Then Set rngDel = c Else Set rngDel = Union(c, rngDel) End If End If Next c 'any rows to delete? If Not rngDel Is Nothing Then Application.EnableEvents = False rngDel.EntireRow.Delete Application.EnableEvents = True End If End If Exit Sub haveError: 'make sure to re-enable events in the case of an error Application.EnableEvents = True End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Sorry still very new. Can you give me an example of how to add argument to my Questar sub?
Sorry again. I see how you passed the argument but I don't understand what to do with the rest of my Questar macro. Also, I don't always want A1:F1, it will just be the columns to the left of whatever G cell I selected. I have edited my original question to include my entire Questar code. Anyway you can put your suggestion into the macro and reply with the entire macro updated? It would be greatly appreciated! :)
You are right that the related subs will just replace Questar. I have other worksheet names from the list: RMPower, Water, Groceries, etc. Can you show me an example of how to pass a second String parameter?
Tim, I had a follow up question: stackoverflow.com/q/40538259/7129429. Do you mind taking a look?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.