Problem with macro effecting all sheets

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.

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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
What do you meant when it's running?

Is there other code that is setting this sub to run at a specific interval?
 
Upvote 0
Not sure about all the points, but does this help at all?

Code:
Sub CopyPaste()
    Dim Crng As Range, Prng As Range
    Dim CurrentTime As Date
    Dim CurrentDate As Date
    Dim WB As Workbook
    Dim WS As Worksheet
    Set WB = ThisWorkbook
    Set WS = WB.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).Resize(, 38)
                Prng.Value = Crng.Value
End Sub
 
Upvote 0
Not sure about all the points, but does this help at all?

Code:
Sub CopyPaste()
    Dim Crng As Range, Prng As Range
    Dim CurrentTime As Date
    Dim CurrentDate As Date
    Dim WB As Workbook
    Dim WS As Worksheet
    Set WB = ThisWorkbook
    Set WS = WB.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).Resize(, 38)
                Prng.Value = Crng.Value
End Sub

Thank you very much for your help. I will try it tomorrow when macro is receiving data and let you know.

Why did you add the resize()? I have a lot to learn, I know.
 
Upvote 0
Not sure about all the points, but does this help at all?

Code:
Sub CopyPaste()
    Dim Crng As Range, Prng As Range
    Dim CurrentTime As Date
    Dim CurrentDate As Date
    Dim WB As Workbook
    Dim WS As Worksheet
    Set WB = ThisWorkbook
    Set WS = WB.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).Resize(, 38)
                Prng.Value = Crng.Value
End Sub

Have tested the code and it appears that the screen flicker on other workbooks in different instances of excel has stopped but the flicker is still there for worksheets in the same workbook.

If screenupdating=False then copy only works sporadically even though it is set to execute every second by a timer function.

Any help greatly appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top