Re: Active buttons work in 2007, but 2010 it doesnt i have to assgin again or i can run it in macros hit "run"
Not sure if this is an Office version problem but it may have coincided with my upgrade from 2010 to 2016? If I run my macro in VB it works fine. If I run it via my macro button in the "Dashboard" worksheet it runs but don't get the same result. Strangely, the start and the end of the code is fine but it has a problem with only some parts. The code in blue is what is not happening if I run the macro from the button in the worksheet. As I said, works fine if I Run or Step-into via VB. Also, I'm a new, self-taught hack at VBA so apologies in advance for the inefficient code writing! Any help would be much appreciated.
Sub Dashboard_Refresh_Data()
'
' Dashboard_Refresh_Data Macro
'
' Clear old Contracts data from report
Worksheets("Dashboard").Range("A59:I66").ClearContents
' Copy required data from the Contracts tab and paste it into the report
Application.ScreenUpdating = False
Sheets("Contracts").Select
Dim x As Long
For x = 2 To 1000
If Cells(x, "Q") = "OPEN" Then
Cells(x, "D").Resize(, 2).Copy
Worksheets("Dashboard").Range("A66").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(x, "H").Resize(, 2).Copy
Worksheets("Dashboard").Range("C66").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(x, "O").Resize(, 1).Copy
Worksheets("Dashboard").Range("F66").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(x, "M").Resize(, 1).Copy
Worksheets("Dashboard").Range("G66").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(x, "R").Resize(, 1).Copy
Worksheets("Dashboard").Range("I66").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next x
' Clear old AR data from report
Worksheets("Dashboard").Range("A76:I88").ClearContents
' Copy required data from the Tickets tab and paste it into the report
Worksheets("Tickets").Select
Dim y As Long
For y = 2 To 10000
If Cells(y, "AG") = "N" Then
Cells(y, "B").Resize(, 1).Copy
Worksheets("Dashboard").Range("A88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "F").Resize(, 1).Copy
Worksheets("Dashboard").Range("B88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "D").Resize(, 1).Copy
Worksheets("Dashboard").Range("C88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "M").Resize(, 1).Copy
Worksheets("Dashboard").Range("D88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "AD").Resize(, 1).Copy
Worksheets("Dashboard").Range("G88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "AH").Resize(, 1).Copy
Worksheets("Dashboard").Range("I88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Cells(y, "AJ").Resize(, 1).Copy
Worksheets("Dashboard").Range("F88").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next y
'Delete old data from Appendix Summary
Sheets("Appendix").Activate
ActiveSheet.Range("A4", Range("A4").SpecialCells(xlLastCell)).ClearContents
' Copy required data from the Contracts tab and paste it into the Appendix Contracts Summary report
Worksheets("Contracts").Range("CONTRACTS[Contract_Date]:CONTRACTS[Cont_Balance]").Copy
Worksheets("Appendix").Range("A4").PasteSpecial xlPasteValues
Worksheets("Contracts").Range("CONTRACTS[Contract_Status]").Copy
Worksheets("Appendix").Range("P4").PasteSpecial xlPasteValues
Worksheets("Contracts").Range("CONTRACTS[Payment_Terms]").Copy
Worksheets("Appendix").Range("Q4").PasteSpecial xlPasteValues
Worksheets("Contracts").Range("CONTRACTS[On Farm Price]").Copy
Worksheets("Appendix").Range("R4").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Dashboard").Select
Application.ScreenUpdating = True
End Sub