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

 

[robertmwaring2]

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

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?

Absolutely, I just didn't know how to accomplish it.

 

[mumps]

Click here to download your file. Please note that I have made several changes in formatting and I have also deleted unused columns and rows which now makes the file much smaller. Please use this file going forward because if you try the macro on a different file it may not work properly. If you delete a value, the cursor will remain in that cell. If you add a value and press the RETURN key, the cursor will be placed in the cell below. Give it a try and see how it works out.

 

[robertmwaring2]

Click here to download your file. Please note that I have made several changes in formatting and I have also deleted unused columns and rows which now makes the file much smaller. Please use this file going forward because if you try the macro on a different file it may not work properly. If you delete a value, the cursor will remain in that cell. If you add a value and press the RETURN key, the cursor will be placed in the cell below. Give it a try and see how it works out.

Currently not in a position to look it over, but wanted to thank you for your assistance nonetheless. When I get home later this evening I will check it out. Thank you again for your time, I sincerely appreciate your efforts!

 

[robertmwaring2]

YOU ARE AMAZING!! This is far more than I was hoping for when I was asking for help. I can't begin to comprehend what you have written, but it works flawlessly and is exactly what I've been trying to accomplish for WEEKS! I cannot thank you enough! I truly do appreciate this, it's magnificent!

 

[mumps]

You are very welcome.

 

[tdinsdale]

Hi, I was following this thread and also had a question of my own.

I work for a company that underwrites loans on commercial real estate. All the work is conducted in Excel and the workbook we currently use is dated. I'm attempting to revamp the workbook but am rather new to Macros.

My goal is to have a macro run from a cell input. The cell input is the type of collateral for the loan (Retail, Multifamily, Industrial). Using the input, the macro would then change a different sheet with how we value the real estate and financial inputs. For example, if the input was "retail" the 'Pro Forma' tab would change a range of cells to be divided by square feet or if the input was "Multifamily" the range would be divided by units.

My thought process was to create a macro that adjusted the Pro Forma tab to the pro forma used to value the real estate and then reference that macro in the code.

The problems I've run into:

I'm starting to understand how to run a macro when a cell is changed, "If Target.Address = Range("B73").Address Then". However, I'm having difficulty calling on the macro to change based on the input.

I might be in over my head but was hoping there might be a chance. Thanks for any help,

Tim

 

[robertmwaring2]

I'm a novice personally, but calling a macro is fine simply by
Call "macro"
Where macro is the title in the sub of the macro, not the actual macro number. So for example, when you create the macro, it starts with sub, then your title, then (), to call that macro
Simply enter call, followed by a space, then the title of that macro.
If you were looking to do anything more, I might have misunderstood.

 

[tdinsdale]

That helps but I'm attempting to call the macro from a input in a certain cell.

i.e. = C47 = "Retail" Runs Macro_Retail on the Pro Forma Tab

 

[mumps]

Could you upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[tdinsdale]

Mumps -

Yes, I'll get it organized and send it over shortly.