VBA WORKSHEET CHANGE

[robertmwaring2]

First, please let me clarify that I am not fluent in VBA, but research what I can to do what I need to on occasion.

Here is a link to the sheet for reference, but including the information below as well: <Click here for Workbook>

A little about the sheet: I am trying to figure out how to make different modules run based on specific cell changes in a worksheet change event btu cannot for the life of me figure out what I am doing wrong. On the sheet in question, the first range(s) that i want to trigger one macro is rows 2:36 in columns A,C, E, G, and I, I am trying to avoid refernces to a specific sheet because there will be 13 other sheets with the same structure that will need the same worksheet change event coding. the second range(s) on the sheet that will trigger a different macro is row 2, but every 4th column beginning at X and continuing to AAR. In part of the code I have come up with, I attempted to ensure that once the macro has run, the selected cell on the sheet is the changed cell (either the cell itself if info was deleted, or the row beneath it if something was entered into the cell.) Below is what I have thus far that I cannot get to come together -

Private Sub Worksheet_Change(ByVal Target As Range)
'call module 1 to create a list of all menu items on the sheet
    If Intersect(Target, Range("A2:J36")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Call CombineMenuLists
    'after list is compiled, returns to cell below changed cell if changed cell is not blank
    If Range(Target.Address).Value <> "" Then
    Range(Target.Address).Offset(1, 0).Select
    End If
   'if changed cell is blank, returns to changed cell
    If Range(Target.Address).Value = "" Then
    Range(Target.Address).Select
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True

'call module 2 to create a list of all display names on the sheet
    If Not Intersect(Target, Range("X2:AAU2")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Call DisplayList
    'after list is compiled, returns to cell below changed cell if changed cell is not blank
    If Range(Target.Address).Value <> "" Then
    Range(Target.Address).Offset(1, 0).Select
    End If
    'if changed cell is blank, returns to changed cell
    If Range(Target.Address).Value = "" Then
    Range(Target.Address).Select
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  End Sub

A little about the macros: for the range(s) that include columns A, C (etc) - the macro is to simply copy the info (from first to last item) in each individual column and paste them into column U. pretty straight forward and I have determined that if I run this macro by itself, it works as intended. so I dont believe this is the issue but here is the coding for review:

Sub CombineMenuLists()
ActiveSheet.Select
Range("U2", Range("U2").End(xlDown)).Clear
If IsEmpty(Range("A2").Value) = False Then
Range("a2", Range("a2").End(xlDown)).copy
Range("U2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
If IsEmpty(Range("C2").Value) = False Then
Range("C2", Range("C2").End(xlDown)).copy
Range("U2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
If IsEmpty(Range("E2").Value) = False Then
Range("E2", Range("E2").End(xlDown)).copy
Range("U2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
If IsEmpty(Range("G2").Value) = False Then
Range("G2", Range("G2").End(xlDown)).copy
Range("U2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
If IsEmpty(Range("I2").Value) = False Then
Range("I2", Range("I2").End(xlDown)).copy
Range("U2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
End Sub

The second macro will to run for cahnges in every 4th column of row 2 beginning at X is as follows:

Sub DisplayList()
ActiveSheet.Select
Range("t17:t400").Value = ""
Dim rRange As Range
Dim rEveryNth As Range
Dim lRow As Long
With ActiveSheet
Set rRange = .Range("S17:S716")
End With
For lRow = 1 To rRange.Rows.Count Step 4
If lRow = 1 Then
Set rEveryNth = rRange(lRow, 1)
Else
Set rEveryNth = Union(rRange(lRow, 1), rEveryNth)
End If
Next lRow
Application.Goto rEveryNth
    Selection.copy
    Range("T17").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ActiveSheet.Range("$T$16:$T$191").AutoFilter Field:=1, Criteria1:="<>"
    Range("t17", Range("t17").End(xlDown)).copy
    Range("V2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ActiveSheet.Range("$T$16:$T$191").AutoFilter Field:=1
End Sub

I realize all this information is likely extra, but am trying to be clear what is happening so sorry. Thank you in advance to anyone who can help! I greatly appreciate it. As a last resort, I was simply going to resort to command buttons, but was trying to avoid the chance that someone may neglect to use them in the event information was changed,

Sincerely,

Robert

 

[mumps]

Sometimes it is hard to determine exactly what you want to do by trying decipher your code. I can't promise a solution, but I think it would be easier to help if you can describe in words, step by step, what you are trying to do referring to specific cells, rows, columns and sheets using a few examples from your data.

 

[robertmwaring2]

My apoloogies!
I'll do my best to explain the goal of what Im trying to accomplish. This spreadsheet is to create production sheets for a culinary team in a hotel. On the worksheet (keep in mind there are 14 sheets that are identical in structure and placement as the one in the link in my previous message, so i need to avoid referencing individual sheets, hence why i used activesheet) when any information is entered or deleted from A2:A36, C2:C36, E2:E36, G2:G36, or I2:I36, i want to copy all the information (excluding the blank or empty cells in those ranges) from these ranges and paste them, one under the other, into a single column beginning au U2. The selected cell after this process should return to the original cell if data was deleted or the cell below it if data was entered.
On the same sheet, if any data is enetered/deleted from X2 (or every fourth column in row 2 after that until column AAR), i would like to copy the data from of those individual cells into a single column beginning at V2, and have the selected cell after the process return to either cell of the deleted data or the one beneath it if data was enetered.

As I mentioned, I am VERY new to this and have used formulas within the sheet to assist me and vba when I can to finish the task. Columns S & T are examples of this, where i used formulas to transpose X2:AAU2 into a single column (S) and then used more formulas to copy every 4th entry in the range to the next column (T). From there i used vba to copy and paste the info in column v without blanks. I can run the both macros without issue, but cannot seem to work out the worksheet change event to trigger each individual macro as needed. If I could have it all in one code for the worksheet cahnge event versus the extra formulas, macros and sheet code, that would be AWESOME, but I am not that skilled (lol).

I hope this provides a better scope of intention for you, and thank you for responding - I GREATLY APPRECIATE IT!!

 

[mumps]

Let's take this one step at a time.

i want to copy all the information (excluding the blank or empty cells in those ranges) from these ranges and paste them, one under the other, into a single column beginning au U2

How do you want to trigger this action? Do you want to click a button to run a macro or do you want to use a Worksheet_Change macro? If you want to use a Worksheet_Change event, which cell/cells will be changed to trigger this action? Do you want the data from A2:A36, C2:C36, E2:E36, G2:G36 and I2:I36 to be copied/pasted at U2 all at once or one cell at a time as it is entered?

The selected cell after this process should return to the original cell if data was deleted or the cell below it if data was entered.

Please explain what you mean by this using references to specific cells. What is the address of the selected cell and the original cell? Also, I don't understand why you would be deleting data.

I ask that you explain using references to specific cells. For example, if I add data to C2, I want this to happen; if I add data to G2, I want this to happen, etc.

 

[robertmwaring2]

Let's take this one step at a time.

How do you want to trigger this action? Do you want to click a button to run a macro or do you want to use a Worksheet_Change macro? If you want to use a Worksheet_Change event, which cell/cells will be changed to trigger this action? Do you want the data from A2:A36, C2:C36, E2:E36, G2:G36 and I2:I36 to be copied/pasted at U2 all at once or one cell at a time as it is entered?

Please explain what you mean by this using references to specific cells. What is the address of the selected cell and the original cell? Also, I don't understand why you would be deleting data.

I ask that you explain using references to specific cells. For example, if I add data to C2, I want this to happen; if I add data to G2, I want this to happen, etc.

 

[mumps]

I was wondering if you had some answers to those questions. Also, when replying, please click the "Reply" button instead of the "+Quote" button to help avoid clutter.

 

[robertmwaring2]

Sorry I didn't make things any clearer in my last post.

How do you want to trigger this action?
If data is entered or deleted in any cell in the range A2:A36, C2:C36, E2:E36, G2:G36 and I2:I36
Do you want to click a button to run a macro or do you want to use a Worksheet_Change macro?
I would like this to be in a worksheet change event so it is instantaneous and to avoid someone neglecting to use the button
If you want to use a Worksheet_Change event, which cell/cells will be changed to trigger this action?
A2:A36, C2:C36, E2:E36, G2:G36 and I2:I36
Do you want the data from A2:A36, C2:C36, E2:E36, G2:G36 and I2:I36 to be copied/pasted at U2 all at once or one cell at a time as it is entered?
All at once, in the order it is listed (A2:A36, C2:C36, E2:E36, G2:G36 and I2:I36), but any existing data in column U from row 2 down should be deleted and replaced wioth the new data. Im basically trying to keep a running list of all data in the ranges (A2:A36, C2:C36, E2:E36, G2:G36 and I2:I36) in Column U (beginning at row 2) as data is entered/changed/deleted.

Please explain what you mean by this using references to specific cells. What is the address of the selected cell and the original cell?
Also, I don't understand why you would be deleting data.
If data is changed in a cell in the range (A2:A36, C2:C36, E2:E36, G2:G36 and I2:I36), once all the data is copied to column U, I want the selected cell to be the cell that was changed initially that triggered the copy process. for example, if D2 data was deleted, copy all ranges to U, and upon completion, D2 would be the selected cell. OR if data was added to D2, once the copy process to column U was completed, the selected cell would then be D3. I want this to happen for any of the cells in the range(s) A2:A36, C2:C36, E2:E36, G2:G36 and I2:I36.

With regards to the second range/worksheet change event on the same page:
The goal is to compile a list of all data from the cells in row 2 beginning at column X - but only every 4th column through column AAR. (ie. X2, AB2, AF2, AJ2, etc . .) into a single list in column V beginning at row 2. I am very limited in my knowledge of vba so I used a formuls in columns S and T beginning at row 17 to help with this. the formula i have in S2;S716 merely transposes X2:AAU2 to S17:S716. From there column T copies every fourth row in in S2:S716 to T16:T192. the macro i created then copies filters that range (T16:T192) to remove blanks and copies it, then pastes it to column V beginning at row 2. If i could eliminate those formulas and have it all in code, that would be phenominal, but i fear I've already been diffcult enough.
How do you want to trigger this action?
When any data is enetered/changed/deleted from every fourth column in the range X2:AAR2
Do you want to click a button to run a macro or do you want to use a Worksheet_Change macro?
worksheet change, again - dont want to run the risk of someone forgetting to click a button.
If you want to use a Worksheet_Change event, which cell/cells will be changed to trigger this action?
Every fouth coulmn in the range X2:AAR2, beginning with X2 (ie. X2, AB2, AF2, AJ2, etc . .)
Do you want the data to be copied/pasted at V2 all at once or one cell at a time as it is entered?
Im going to say all at once again, sinc ethis is a running list of items from X2:AAU2. it need to be updated instantaneously as data in X2:AAU2 is entered/changed/deleted. if there is existing data in column V (starting with row 2) - it should be removed and replaced with the new copied data from X2:AAU2.
Please explain what you mean by this using references to specific cells. What is the address of the selected cell and the original cell? Also, I don't understand why you would be deleting data.
Once the copy process completes, the selected cell should return to the cell that triggered the process. for example, it data was deleted from X2, begin the copy process and once completed, the selected cell should be X2. If data was entered or changed in X2, once the copy process has completed, the selected cell should be the cell below it (X3)

I apologize i failed to be clear in the beginning, or in my second reply, but i hope this clears it up.

 

[mumps]

if D2 data was deleted

When you delete a value in A2:A36, C2:C36, E2:E36, G2:G36 and I2:I36, will you immediately enter a new value in the now blank cell or will you leave the cell blank?

 

[robertmwaring2]

Possibly, but there can be in instances where it will simply be deleted. There may not be data in each of the cells in those ranges. It could also be possible for e2:e36 to be completely blank. It will vary constantly

 

[mumps]

OK. I think I have the first part done. Now to the second part.

i have in S2;S716 merely transposes X2:AAU2 to S17:S716. From there column T copies every fourth row in in S2:S716 to T16:T192. the macro i created then copies filters that range (T16:T192) to remove blanks and copies it, then pastes it to column V beginning at row 2.

If the data from every fourth column in X2:AAU2 can be copied directly to column V, it sounds like you don't need the data to be copied to S17:S716 and then to T16:T192 and then filter T16:T192 to copy to column V. Is this correct? Can I simply copy directly to column V leaving S17:S716 and T16:T192 blank?