delta_negative
New Member
- Joined
- Mar 11, 2013
- Messages
- 37
The following code works great for the original intended purpose but there are some undesirable effects that need to be changed:
1. It will not allow copy/paste on any other worksheet or workbook or even a web browser while the macro is running. I think that this code is the reason
Application.CutCopyMode = False.
Have tried to alter but w/o satisfactory results.
Desired functionality is for CutCopy Mode to effect only this sheet.
2. Can't have any other worksheet open in any workbook because the code
Set Crng = WS.Range("A8:AL8")
Set Prng = WS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Crng.Copy
effects all worksheets and causes blank rows to be copied. Additionally it attempts to copy to all open workshheets in the referenced range.
3. Would like to suppress screen updating for this sheet only, if possible.
4. It seems to stop working if the sheet is not the active sheet.
Some of the code for a timer is omitted as it doesn't seem to effect the above mentioned problems.
Any help greatly appreciated.
1. It will not allow copy/paste on any other worksheet or workbook or even a web browser while the macro is running. I think that this code is the reason
Application.CutCopyMode = False.
Have tried to alter but w/o satisfactory results.
Desired functionality is for CutCopy Mode to effect only this sheet.
2. Can't have any other worksheet open in any workbook because the code
Set Crng = WS.Range("A8:AL8")
Set Prng = WS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Crng.Copy
effects all worksheets and causes blank rows to be copied. Additionally it attempts to copy to all open workshheets in the referenced range.
3. Would like to suppress screen updating for this sheet only, if possible.
4. It seems to stop working if the sheet is not the active sheet.
Some of the code for a timer is omitted as it doesn't seem to effect the above mentioned problems.
Any help greatly appreciated.
Code:
Public RunWhen As Double
Public Const cRunIntervalSeconds = 1
Public Const cRunWhat = "CopyPaste"
Sub CopyPaste()
Dim Crng As Range, Prng As Range
Dim CurrentTime As Date
Dim CurrentDate As Date
Dim WS As Worksheet
Set WS = Worksheets("Data Logger")
WS.Range("C1") = WS.Range("C1") + 1
Set Crng = WS.Range("A8:AL8")
Set Prng = WS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Crng.Copy
Prng.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub