Caveman1964
Board Regular
- Joined
- Dec 14, 2017
- Messages
- 127
- Office Version
- 2016
- Platform
- Windows
I have written this over and over again. Its takes 30 seconds. I am a novice doing this a few months.
I should be defining...tried that but could not get this to speed up. It works but looks embarrassing, I am aware.
After a couple of days of trying to speed it up, I am turning to the Guru's once again.
please review and tell me why this is so slow. I so appreciate any help or rewrite.
Sub SubmitDataNewEntry()
'unprotect
Sheets("Do Not Alter").Unprotect "1"
Sheets("Data Collection").Unprotect "1"
Sheets("Complaint Entry").Unprotect "1"
Sheets("Do not alter").Rows(7).Copy
Sheets("Data Collection").RANGE("A7").End(xlDown).Offset(1, 0).EntireRow.Insert
Sheets("complaint entry").RANGE("e5:e22").Copy
Sheets("data collection").RANGE("A7").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Sheets("data collection").RANGE("l7:l2500").Copy
Sheets("data for dashboard").RANGE("a77").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Sheets("Complaint entry").Activate
RANGE("e5:e22").ClearContents
RANGE("E5").Select
ActiveWorkbook.Save
'Protect
Sheets("Do Not Alter").Protect "1", True, True
Sheets("Data Collection").Protect "1", True, True
Sheets("Complaint Entry").Protect "1", True, True
End Sub
I should be defining...tried that but could not get this to speed up. It works but looks embarrassing, I am aware.
After a couple of days of trying to speed it up, I am turning to the Guru's once again.
please review and tell me why this is so slow. I so appreciate any help or rewrite.
Sub SubmitDataNewEntry()
'unprotect
Sheets("Do Not Alter").Unprotect "1"
Sheets("Data Collection").Unprotect "1"
Sheets("Complaint Entry").Unprotect "1"
Sheets("Do not alter").Rows(7).Copy
Sheets("Data Collection").RANGE("A7").End(xlDown).Offset(1, 0).EntireRow.Insert
Sheets("complaint entry").RANGE("e5:e22").Copy
Sheets("data collection").RANGE("A7").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Sheets("data collection").RANGE("l7:l2500").Copy
Sheets("data for dashboard").RANGE("a77").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Sheets("Complaint entry").Activate
RANGE("e5:e22").ClearContents
RANGE("E5").Select
ActiveWorkbook.Save
'Protect
Sheets("Do Not Alter").Protect "1", True, True
Sheets("Data Collection").Protect "1", True, True
Sheets("Complaint Entry").Protect "1", True, True
End Sub