Worksheet_Change(ByVal Target As Range) not executing Target inc by 1 as spinner button

**sdl2** · 12-16-2014, 03:18 PM

I have 2 worksheet_change events. The first works fine. The second is dependent on a spinner button which increase cell t65 by 1 but the code is not executing.

Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to UPPER case for the recipe
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
    On Error Resume Next
    If Not Intersect(Target, Range("I4:N61")) Is Nothing Then
        Application.EnableEvents = False
        Target = UCase(Target)
        Application.EnableEvents = True
    End If
    On Error GoTo 0
'''''''''''''''''''''''''''''''''''''''''''
'View Record'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
    Dim KeyCells As Range
    Set KeyCells = Range("T65")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
Application.ScreenUpdating = False
    Range("S69").Select
    Selection.Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S70").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S71").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S72").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A11:B11").Select
    ActiveSheet.Paste
    Range("S73").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A14:B14").Select
    ActiveSheet.Paste
    Range("S74").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A17:C17").Select
    ActiveSheet.Paste
    Range("S75").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A20:C20").Select
    ActiveSheet.Paste
    Range("S76").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S77").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C11:D11").Select
    ActiveSheet.Paste
    Range("S78").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C8").Select
    ActiveSheet.Paste
    Range("S79").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C5:D5").Select
    ActiveSheet.Paste
    Range("S80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S81").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E2:G2").Select
    ActiveSheet.Paste
    Range("S82").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S83").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S84").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S85").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S86").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S87").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S88").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S89").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G8:H8").Select
    ActiveSheet.Paste
    Range("S90").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G11:H11").Select
    ActiveSheet.Paste
    Range("S91").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G14:H14").Select
    ActiveSheet.Paste
    Range("S92").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G17:H17").Select
    ActiveSheet.Paste
    Range("S93").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G20:H20").Select
    ActiveSheet.Paste
    Range("S94").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G23:H23").Select
    ActiveSheet.Paste
    Range("S95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A25:H25").Select
    ActiveSheet.Paste
    Range("S96").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A28:H28").Select
    ActiveSheet.Paste
    Range("S97").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A31:H31").Select
    ActiveSheet.Paste
    Range("S98").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A34:H34").Select
    ActiveSheet.Paste
    Range("S99").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B26").Select
    ActiveSheet.Paste
    'WILL PASETE OVER FORMULAS
    'Range("S100").Select
    'Application.CutCopyMode = False
    'Selection.Copy
    'Range("A37").Select
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Range("S101").Select
    'Application.CutCopyMode = False
    'Selection.Copy
    'Range("A39").Select
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Range("S102").Select
    'Application.CutCopyMode = False
    'Selection.Copy
    'Range("A41").Select
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    '    :=False, Transpose:=False
    'DONT UNCOMMENT
    Range("S103").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B37:D37").Select
    ActiveSheet.Paste
    Range("S104").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B39:D39").Select
    ActiveSheet.Paste
    Range("S105").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B41:D41").Select
    ActiveSheet.Paste
    Range("S106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E37:F37").Select
    ActiveSheet.Paste
    Range("S107").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E39:F39").Select
    ActiveSheet.Paste
    Range("S108").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E41:F41").Select
    ActiveSheet.Paste
    Range("S109").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Range("S110").Select
    Selection.Copy
    Range("E46").Select
    ActiveSheet.Paste
    Range("S111").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F46:G46").Select
    ActiveSheet.Paste
    Range("S112").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A49").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S113").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A51").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S114").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A53").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S115").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A55").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S116").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A57").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S117").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A59").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S118").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B49:D49").Select
    ActiveSheet.Paste
    Range("R82").Select
    Selection.Copy
    Range("B51:D51").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("R82").Select
    Selection.Copy
    Range("B51:D51").Select
    ActiveSheet.Paste
    Range("R83").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B53:D53").Select
    ActiveSheet.Paste
    Range("R84").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B55:D55").Select
    ActiveSheet.Paste
    Range("R85").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B57:D57").Select
    ActiveSheet.Paste
    Range("R86").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B59:D59").Select
    ActiveSheet.Paste
    Range("R87").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E49:F49").Select
    ActiveSheet.Paste
    Range("R88").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E51:F51").Select
    ActiveSheet.Paste
    Range("R89").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E53:F53").Select
    ActiveSheet.Paste
    Range("R90").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E55:F55").Select
    ActiveSheet.Paste
    Range("R91").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E57:F57").Select
    ActiveSheet.Paste
    Range("R92").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E59:F59").Select
    ActiveSheet.Paste
    Range("R94").Select
    Range("R99").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P2:Q2").Select
    ActiveSheet.Paste
    Range("R100").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("R2:S2").Select
    ActiveSheet.Paste
    Range("R101").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("T2:U2").Select
    ActiveSheet.Paste
    Range("R102").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("V2:W2").Select
    ActiveSheet.Paste
    Range("R103").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P4:Q4").Select
    ActiveSheet.Paste
    Range("R104").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("R4:S4").Select
    ActiveSheet.Paste
    'Range("Q105").Select
    Range("R115").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
CODE CUT
Application.ScreenUpdating = True
 MsgBox "Cell " & Target.Address & " has changed."
    End If
End Sub

**Mumps1** · 12-16-2014, 03:34 PM

I'm not sure what you are trying to do but you can use only one worksheet_change event in any sheet. Secondly, you could tidy up your code quite a bit. You don't have to select a range to copy and paste it. For example:

Range("S71").Select
Selection.Copy
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

could be changed to:

Range("S71").Copy
Range("A8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

and

Range("S78").Select
        Selection.Copy
    Range("C8").Select
    ActiveSheet.Paste

could be changed to:

Range("S78").Copy Range("C8")

Also you only need to put this code in your macro once at the end:

Application.CutCopyMode = False

I hope this helps.

**stnkynts** · 12-16-2014, 03:38 PM

Nowhere in your code does the value in T65 increase by 1.

What does a "spinner button" mean to you?

Your code is extremely inefficient and there is so much of it I doubt anyone is going to rewrite it for you. Each .Select - .Copy - .Select - .Paste xlPasteValues can be simplified to this:

'old/bad
Range("S69").Select
    Selection.Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'new/better
Range("A3").Value = Range("S69").Value

Furthermore. You are going to want to use Application.EnableEvents = False for what you are doing.

**sdl2** · 12-16-2014, 03:54 PM

I used the recorder for the second part, it is copying many, many, many cells. I have a spin button (form control) separate which increases the value of T65 by 1. I wanted it to trigger the second part of the macro when cell T65 changes.

**stnkynts** · 12-16-2014, 05:40 PM

I used the recorder for the second part, it is copying many, many, many cells. I have a spin button (form control) separate which increases the value of T65 by 1. I wanted it to trigger the second part of the macro when cell T65 changes.

Your code is already set up to do that. For further analysis on your problem I recommend you submit a copy of you workbook with all sensitive information removed.

**sdl2** · 12-16-2014, 05:47 PM

Originally Posted by stnkynts

Your code is already set up to do that. For further analysis on your problem I recommend you submit a copy of you workbook with all sensitive information removed.

When I manually change it, it works fine once and then doesn't work again, when spin button changes it, nothing happens at all. Code has been cleaned up

Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to UPPER case for the recipe
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
    On Error Resume Next
    If Not Intersect(Target, Range("I4:N61")) Is Nothing Then
        Application.EnableEvents = False
        Target = UCase(Target)
        Application.EnableEvents = True
    'End If
    On Error GoTo 0
'''''''''''''''''''''''''''''''''''''''''''
'View Record'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
    Dim KeyCells As Range
    Set KeyCells = Range("T65")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
           Application.ScreenUpdating = False
           Application.CutCopyMode = False
           'Application.EnableEvents = False
           Application.ScreenUpdating = False
    Range("S69").Copy
    Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S70").Copy
    Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S71").Copy
    Range("A8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S72").Copy
    Range("A11:B11").Paste
    Range("S73").Copy
    Range("A14:B14").Paste
    Range("S74").Copy
    Range("A17:C17").Paste
    Range("S75").Copy
    Range("A20:C20").Paste
    Range("S76").Copy
    Range("C14").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S77").Copy
    Range("C11:D11").Paste
    Range("S78").Copy
    Range("C8").Paste
    Range("S79").Copy
    Range("C5:D5").Paste
    Range("S80").Copy
    Range("E5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S81").Copy
    Range("E2:G2").Paste
    Range("S82").Copy
    Range("E8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S83").Copy
    Range("E11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S84").Copy
    Range("E14").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S85").Copy
    Range("E17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S86").Copy
    Range("E20").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S87").Copy
    Range("E23").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S88").Copy
    Range("H5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S89").Copy
    Range("G8:H8").Paste
    Range("S90").Copy
    Range("G11:H11").Paste
    Range("S91").Copy
    Range("G14:H14").Paste
    Range("S92").Copy
    Range("G17:H17").Paste
    Range("S93").Copy
    Range("G20:H20").Paste
    Range("S94").Copy
    Range("G23:H23").Paste
    Range("S95").Copy
    Range("A25:H25").Paste
    Range("S96").Copy
    Range("A28:H28").Paste
    Range("S97").Copy
    Range("A31:H31").Paste
    Range("S98").Copy
    Range("A34:H34").Paste
    Range("S99").Copy
    Range("B26").Paste
    'WILL PASETE OVER FORMULAS
    'Range("S100").Copy
    'Range("A37").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Range("S101").Copy
    'Range("A39")..PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Range("S102").Copy
    'Range("A41").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    '    :=False, Transpose:=False
    'DONT UNCOMMENT
    Range("S103").Copy
    Range("B37:D37").Paste
    Range("S104").Copy
    Range("B39:D39").Paste
    Range("S105").Copy
    Range("B41:D41").Paste
    Range("S106").Copy
    Range("E37:F37").Paste
    Range("S107").Copy
    Range("E39:F39").Paste
    Range("S108").Copy
    Range("E41:F41").Paste
    'Range("S109").Copy
    Range("S110").Copy
    Range("E46").Paste
    Range("S111").Copy
    Range("F46:G46").Paste
    Range("S112").Copy
    Range("A49").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S113").Copy
    Range("A51").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S114").Copy
    Range("A53").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Range("S115").Copy
    Range("A55").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("S116").Copy
    Range("A57").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S117").Copy
    Range("A59").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S118").Copy
    Range("B49:D49").Paste
    'Range("R82").Copy
    
    'Range("B51:D51").Select
    'ActiveCell.FormulaR1C1 = ""
    
    Range("R82").Copy
    Range("B51:D51").Paste
    Range("R83").Copy
    Range("B53:D53").Paste
    Range("R84").Copy
    Range("B55:D55").Paste
    Range("R85").Copy
    Range("B57:D57").Paste
    Range("R86").Copy
    Range("B59:D59").Paste
    Range("R87").Copy
    Range("E49:F49").Paste
    Range("R88").Copy
    Range("E51:F51").Paste
    Range("R89").Copy
    Range("E53:F53").Paste
    Range("R90").Copy
    Range("E55:F55").Paste
    Range("R91").Copy
    Range("E57:F57").Paste
    Range("R92").Copy
    Range("E59:F59").Paste
    Range("R99").Copy
    Range("P2:Q2").Paste
    Range("R100").Copy
    Range("R2:S2").Paste
    Range("R101").Copy
    Range("T2:U2").Paste
    Range("R102").Copy
    Range("V2:W2").Paste
    Range("R103").Copy
    Range("P4:Q4").Paste
    Range("R104").Copy
    Range("R4:S4").Paste
    Range("R115").Copy
    Range("Q118").Select
    Range("O80").Copy
    Range("P41:Q41").Paste
    Range("O81").Copy
    Range("T41:U41").Paste
    Range("O82").Copy
    Range("P45:Q45").Paste
    Range("O83").Copy
    Range("R45:S45").Paste
    Range("O84").Copy
    Range("T45:U45").Paste
    Range("O85").Copy
    Range("V45:W45").Paste
    Range("O86").Copy
    Range("X45").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O87").Copy
    Range("P48:Q48").Paste
    Range("O88").Copy
    Range("R48:S48").Paste
    Range("O89").Copy
    Range("T48:U48").Paste
    Range("O90").Copy
    Range("V48").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O91").Copy
    Range("W48").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O92").Copy
    Range("X48").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O93").Copy
    Range("P51:Q51").Paste
    Range("O94").Copy
    Range("R51:S51").Paste
    Range("O95").Copy
    Range("T51").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O96").Copy
    Range("U51:V51").Paste
    Range("O97").Copy
    Range("W51").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O98").Copy
    Range("X51").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O99").Copy
    Range("P54:Q54").Paste
    Range("O100").Copy
    Range("T54:V54").Paste
    Range("O101").Copy
    Range("P56:X56").Paste
    Range("O103").Copy
    Range("P59:X59").Paste
    Range("O105").Copy
    Range("Y3:Z3").Paste
    Range("O106").Copy
    Range("AA3:AB3").Paste
    Range("O109").Copy
    Range("Y5:Z5").Paste
    Range("O110").Copy
    Range("AA5:AB5").Paste
    Range("O113").Copy
    Range("Y7:Z7").Paste
    Range("O114").Copy
    Range("AA7:AB7").Paste
    Range("O117").Copy
    Range("Y9:Z9").Paste
    Range("O118").Copy
    Range("AA9:AB9").Paste
    Range("M66").Copy
    Range("Y11:Z11").Paste
    Range("M67").Copy
    Range("AA11:AB11").Paste
    Range("M70").Copy
    Range("AF2:AH2").Paste
    Range("M71").Copy
    Range("AF4:AG4").Paste
    Range("M72").Copy
    Range("AH4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M73").Copy
    Range("AI4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M74").Copy
    Range("AF7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M75").Copy
    Range("AG7:AH7").Paste
    Range("M76").Copy
    Range("AI7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M77").Copy
    Range("AF9:AG9").Paste
    Range("M78").Copy
    Range("AH9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M79").Copy
    Range("AI9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M80").Copy
    Range("AH11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M81").Copy
    Range("Y13:AI13").Paste
    Range("M82").Copy
    Range("Y15:AI15").Paste
    Range("M83").Copy
    Range("Y17:AI17").Paste
    Range("M84").Copy
    Range("Y19:AI19").Paste
    Range("M85").Copy
    Range("Y21:AI21").Paste
    Range("M86").Copy
    Range("Y23:AI23").Paste
    Range("M87").Copy
    Range("Y25:AI25").Paste
    Range("M88").Copy
    Range("Y27:AI27").Paste
    Range("M91").Copy
    Range("Y31:AI31").Paste
    Range("M94").Copy
    Range("AH35").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M95").Copy
    Range("AI35").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M96").Copy
    Range("Y37:AA37").Paste
    Range("M97").Copy
    Range("AB37:AC37").Paste
    Range("M98").Copy
    Range("AD37:AE37").Paste
    Range("M99").Copy
    Range("AF37:AG37").Paste
    Range("M100").Copy
    Range("AH37").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M101").Copy
    Range("AI37").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M102").Copy
    Range("AD39:AE39").Paste
    Range("M103").Copy
    Range("AB39:AC39").Paste
    Range("M104").Copy
    Range("AD39:AE39").Paste
    Range("M105").Copy
    Range("AF39:AG39").Paste
    Range("M106").Copy
    Range("AH39").Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M107").Copy
    Range("AI39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M108").Copy
    Range("Y42:AI42").Paste
    Range("M111").Copy
    Range("Y45:AI45").Paste
    Range("M112").Copy
    Range("Y48:AI48").Paste
    Range("M114").Copy
    Range("Y51:AI51").Paste
    Range("M115").Copy
    Range("Y54:AI54").Paste
    Range("M116").Copy
    Range("Y57:AG57").Paste
    Range("M117").Copy
    Range("AH57:AI57").Paste
    Range("M102").Copy
    Range("Y39:AA39").Paste
    'Range("T90:U90").Select
    'Range("K102").Select
    'Range("K100").Select
    'Range("K99").Select
    'Range("O100").Copy
    'Range("T54:V54").Copy
    'Range("O102").Select
     'Range("Q105").Select
    'Range("N65").Select
    'Range("O65").Select
    'Range("N67").Select
    'Range("R94").Select
    End If
On Error GoTo 0
'On Error GoTo 0
Application.ScreenUpdating = True
'MsgBox "Recipe" & Range("K63")
    'With MsgUserForm ' this is the name I gave to the userform
    '    .TextBox1.Text = Range("K63")
    '    .Show
    'End With
    'End If
'Range("I3:N60").Select
'    With Selection
'        .VerticalAlignment = xlBottom
'        .WrapText = False
'        .Orientation = 0
'        .AddIndent = False
'        .ShrinkToFit = True
'        .ReadingOrder = xlContext
'        .MergeCells = True
'    End With
'    Range("I3:O60").Select
'    ActiveCell.FormulaR1C1 = Range("K63")
'    Range("I3:N60").Select
'    With Selection
'        .VerticalAlignment = xlCenter
'        .WrapText = False
'        .Orientation = 0
'        .AddIndent = False
'        .IndentLevel = 0
'        .ShrinkToFit = True
'        .ReadingOrder = xlContext
'        .MergeCells = True
'    End With
'    With Selection
'        .HorizontalAlignment = xlCenter
'        .VerticalAlignment = xlCenter
'        .WrapText = False
'        .Orientation = 0
'        .AddIndent = False
'        .IndentLevel = 0
'        .ShrinkToFit = True
'        .ReadingOrder = xlContext
'        .MergeCells = True
'    End With
'    With Selection
'        .HorizontalAlignment = xlCenter
'        .VerticalAlignment = xlTop
'        .WrapText = False
'        .Orientation = 0
'        .AddIndent = False
'        .IndentLevel = 0
'        .ShrinkToFit = True
'        .ReadingOrder = xlContext
'        .MergeCells = True
'    End With
   End If
End Sub

Worksheet_Change(ByVal Target As Range) not executing Target inc by 1 as spinner button

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Worksheet_Change(ByVal Target As Range) not executing Target inc by 1 as spinner button

Re: Worksheet_Change(ByVal Target As Range) not executing Target inc by 1 as spinner butto

Re: Worksheet_Change(ByVal Target As Range) not executing Target inc by 1 as spinner butto

Re: Worksheet_Change(ByVal Target As Range) not executing Target inc by 1 as spinner butto

Re: Worksheet_Change(ByVal Target As Range) not executing Target inc by 1 as spinner butto

Re: Worksheet_Change(ByVal Target As Range) not executing Target inc by 1 as spinner butto

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Worksheet_Change(ByVal Target As Range) - set to only update when 3 target cells changed?

[SOLVED] 2nd condition: Worksheet_Change(ByVal Target As Range)

Worksheet_CHange (ByVal Target as Range) when target is formula cell

[SOLVED] use of Worksheet_Change(ByVal Target As Range)

[SOLVED] Worksheet_Change(ByVal Target As Excel.Range)

Bookmarks

Bookmarks

Posting Permissions