Private Sub CommandButton7_Click()
Dim varAnswer As String

varAnswer = MsgBox("Are you certain you wish to proceed? This cannot
be undone." & Chr(10) & Chr(10) & "Edits to this workbook my only be
entered into your Data Sheet manually once the current data is
compiled.", vbOKCancel)
If varAnswer = vbCancel Then
Exit Sub
End If
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With Worksheets("ws1")
.DisplayPageBreaks = False
StartRow = 2
EndRow = 21
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "a").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value <= " " Then .Rows(Lrow).Delete

'This will delete the row if the cell is empty

End If
Next

ActiveWorkbook.Unprotect ("xxx")
Sheets("ws1").Visible = True
Sheets("ws1").Select
.DisplayPageBreaks = False
Sheets("ws1").Range("a2:m21").Select
Selection.copy
Sheets("ws1").Visible = False
ActiveWorkbook.Protect ("xxx")
Sheets("Sheet2").Select

End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

MsgBox ("Your inspection data for this worksheet has been compiled.
Please go immeditately to the current version of you Data
Sheet/PivotTable to import this data.")

End Sub


Question1: .screenupdating = false and xlCalculationManual - are these
necessary. What would happen if I took those out?

Question2: this proceedure is activated with a button on Sheet2.
After the blanks are deleted, I need to copy A2:M21 on sheet "ws1", but
"ws1" is (1) hidden and (2) the workbook is protected. The code above
does what its supposed to do, but in proceedure the clipboard is
getting wiped out and I can't paste (into another, seperate workbook)
what I copied. Can I copy a range on a hidden sheet in a protected
workbook? If so, how?

Qestion3: When this proceedure ends, incell functions I have on Sheet2
cycle through again. Here is the function:


Function commenttext2(incell As String) As String
If incell " " Then commenttext2 = InputBox("Please enter your
datasheet comment for" & " " & " " & ActiveSheet.Range("c41").Value)
End Function

The comment that is typed into the inputBox is part of what is copied
in the proceedure at the top. Why is the execution of the proceedure
at the top causing these functions to re-cycle?

Thanks,


--
BigDave
------------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
View this thread: http://www.excelforum.com/showthread...hreadid=379869

"BigDave" wrote in
message ...

Question1: .screenupdating = false and xlCalculationManual - are these
necessary. What would happen if I took those out?

It would be slower and you would see lots of screen flashing.

Question2: this proceedure is activated with a button on Sheet2.
After the blanks are deleted, I need to copy A2:M21 on sheet "ws1", but
"ws1" is (1) hidden and (2) the workbook is protected. The code above
does what its supposed to do, but in proceedure the clipboard is
getting wiped out and I can't paste (into another, seperate workbook)
what I copied. Can I copy a range on a hidden sheet in a protected
workbook? If so, how?

Yeah, hidden and protected is okay


Worksheets("ws1").Range("A2:M21").Copy Worksheets("Sheet2").Range("A2")

Qestion3: When this proceedure ends, incell functions I have on Sheet2
cycle through again. Here is the function:

Don't understand this one.

Thanks - I cleaned it up quite a bit and it is doing 99% of what I wan
to. Thanks so much for the help, I really appreciate it.


Private Sub CommandButton7_Click()
Dim varAnswer As String

varAnswer = MsgBox("Are you certain you wish to proceed? This canno
be undone." & Chr(10) & Chr(10) & "Edits to this workbook my only b
entered into your Data Sheet manually once the current data i
compiled.", vbOKCancel)
If varAnswer = vbCancel Then
Exit Sub
End If
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Worksheets("ws1")
.DisplayPageBreaks = False
StartRow = 2
EndRow = 21
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "a").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value <= " " Then .Rows(Lrow).Delete

'This will delete the row if the cell is empty

End If
Worksheets("ws1").Range("a2:m21").copy
Next

MsgBox ("Your inspection data for this worksheet has been compiled.
Please go immeditately to the current version of you Dat
Sheet/PivotTable to import this data.")
End With
End Sub



--
BigDav
-----------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=37986