Help w/VBA object interferring with Macro

[Drewbert34]

I'm pretty new at VBA coding and verbiage so I hope I'm describing this so that it is understood by those more familiar with this than I.

I have this VBA coding that will date stamp column B if any changes are made in the range.

"Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
If Not Intersect(cell, Range("A:BV")) Is Nothing Then
If cell <> "" Then
Application.EnableEvents = False
Range("B" & cell.Row) = Date
Application.EnableEvents = True
End If
End If
Next cell
End Sub"

Then I have 2 Macros that I need to run every Friday to format my workbook to remove Green highlighting and Bold font as well as devalue vlookups and certain formulas from my sheet.

"Sub RemoveGreenBold1()
'
' RemoveGreenBold1 Macro
'
'
Range("A7:BV7", Range("A7:BV7").End(xlDown)).Select
Selection.Font.Bold = False
ActiveSheet.Range("$A$6:$BV$313").AutoFilter Field:=1, Criteria1:=RGB(191, _
191, 191), Operator:=xlFilterCellColor
Range("B7:BV7", Range("B7:BV7").End(xlDown)).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveSheet.Range("$A$6:$BV$456").AutoFilter Field:=1
End Sub"

and...

"Sub CopyPasteSpecial1()
'
' CopyPasteSpecial1 Macro
'
' Keyboard Shortcut: Ctrl+y
'
ActiveSheet.Range("$A$6:$BU$456").AutoFilter Field:=1, Criteria1:="<>"
Range("D7:F7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("H7:Z7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-453
Range("I7:Z7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("AM7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("AP7:AT7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("AV7:AW7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("BD7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("BM7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("$A$6:$BU$620").AutoFilter Field:=1
ActiveWindow.SmallScroll Down:=-15
ActiveSheet.Shapes("Button 1").Visible = False
ActiveSheet.Shapes("Button 2").Visible = False
Range("a2") = Sheet1.Range("a2") + 7
End Sub"

The issue is that when the 2 Macros run they cause the VBA object to change the date in column B. How do I tell the Macros to diable the VBA object only while running the Macros?

 

[njimack]

Make sure your first line of code in each RemoveGreenBold1 and CopyPasteSpecial1 is:

Application.EnableEvents = False

Make sure the final row is:

Application.EnableEvents = True

 

[Drewbert34]

Make sure your first line of code in each RemoveGreenBold1 and CopyPasteSpecial1 is:

Application.EnableEvents = False

Make sure the final row is:

Application.EnableEvents = True

That is the greatest thing ever! Thank you so very much!

 

[njimack]

No problem, thanks for the feedback. As well as setting EnableEvents to False, you should also consider setting ScreenUpdating to False as well (just make sure you set it back to True at the end of your code).

By the way, your code will run a lot faster if you don't select ranges. The below code:

Range("D7:F7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

could/should be:

Dim LR As Long
LR = Range("D7").End(xlDown).Row

With Range(cells(7,4),cells(LR,6))
.Copy
.PasteSpecial xlPasteValuesAndNumberFormats
End With

 

[Drewbert34]

No problem, thanks for the feedback. As well as setting EnableEvents to False, you should also consider setting ScreenUpdating to False as well (just make sure you set it back to True at the end of your code).

By the way, your code will run a lot faster if you don't select ranges. The below code:

Range("D7:F7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

could/should be:

Dim LR As Long
LR = Range("D7").End(xlDown).Row

With Range(cells(7,4),cells(LR,6))
.Copy
.PasteSpecial xlPasteValuesAndNumberFormats
End With

Just so that I understand correctly. I would need to separate out each row individually? I guess length of the Macro is not necessarily indicative of the speed at which it runs?

I will do a search on the screen updating on/off. Thank you for the tip! I need all the help I can get...but I am learning!

Once again thank you for your time!

 

[njimack]

Just so that I understand correctly. I would need to separate out each row individually?

Not quite sure what you're asking. You would only Dimension LR once, at the start of your code. And providing the last row of column D is also the last row of your other columns, you would only set LR once.

Actually, your code has lots of overlapping ranges, which you could strip out. And since you're just copying and pasting as values, you could do something like this, which avoids using the clipboard:

With Range(cells(7,4),cells(LR,6))
.Formula = .Value
End With

 

[AlphaFrog]

TIP. When you are building your Event macro and say you have Application.EnableEvents = False at the start, If your macro, for some reason, errors and doesn't have a chance to run the line Application.EnableEvents = True, then your event macros will no longer trigger. The Application.EnableEvents has been left in the False setting.

You may want to have a short macro so you can manually re-enable the Events until you resolve whatever problem you had.

Sub ReEnabel_Events()
Application.EnableEvents = True
End Sub

 

[Drewbert34]

Not quite sure what you're asking. You would only Dimension LR once, at the start of your code. And providing the last row of column D is also the last row of your other columns, you would only set LR once.

Correct, however what I am not understanding is that I have columns which need to stay valued. How will it know which columns to value out and which to leave as is if I don't specify?

Actually, your code has lots of overlapping ranges, which you could strip out. And since you're just copying and pasting as values, you could do something like this, which avoids using the clipboard:

With Range(cells(7,4),cells(LR,6))
.Formula = .Value
End With

Time for me to go to school! lol

 

[njimack]

Correct, however what I am not understanding is that I have columns which need to stay valued. How will it know which columns to value out and which to leave as is if I don't specify?

Using Range(cells(7,4),cells(LR,6)) is the same as saying Range("D7:F" & LR), it's just a different method of referring to rows and columns.

 

[Drewbert34]

TIP. When you are building your Event macro and say you have Application.EnableEvents = False at the start, If your macro, for some reason, errors and doesn't have a chance to run the line Application.EnableEvents = True, then your event macros will no longer trigger. The Application.EnableEvents has been left in the False setting.

You may want to have a short macro so you can manually re-enable the Events until you resolve whatever problem you had.

Sub ReEnabel_Events()
Application.EnableEvents = True
End Sub

That's a great suggestion as well! I sure wish I had time to study VBA more and teach myself all of this stuff. What little I have taught myself has made a huge impact on my work! I really do thatnk you very much for helping me