Get rid of Information message

[fmistry]

I am using a macro to transfer a large data from one worksheet to
another, and I would like this is to occur automatically at a given
frequency (once an hour, etc). I get the information message that
"There is a large amount of information on the clipboard. Do you want
to save it? etc" and the macro stops there, waiting for a reply. How
do I get the macro to either suppress this message or answer the
message with a "no"? Also, how do I set up a loop to run this macro at
regular intervals? Thanks for your help.

 

[Ron de Bruin]

Hi fmistry

You can copy without using the clipboard (one line of code)
Sheets("Sheet1").Range("A1:Z1000").Copy Sheets("Sheet2").Range("A1")

Or use this to clear the clipboard after the paste line in your code
Application.CutCopyMode = False

 

[fmistry]

Thanks, Ron.

The second method you suggested worked very well.

I am using the code generated by "Record macro" to copy data from one
file to another. For example, to copy data from file 901-154.log to
E901-154.xls, I use

Windows("901-154.log").Activate
Range("A2:A9001").Select
Selection.Copy
Windows("E901-154.xls").Activate
Sheets("Valve SP").Select
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

How would I use your Sheets command?

About running the macro at fixed intervals, what should I do?

Thank you for your help.

Firoz

 

[Ron de Bruin]

Try this

With pastespecial you can't use a one liner only with a normal copy

You can remove the PasteSpecial lines you not want

Sub test()
Workbooks("901-154.log").Sheets(1).Range("A2:A9001").Copy

With Workbooks("E901-154.xls").Sheets("Valve SP")
.Range("A11").PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
.Range("A11").PasteSpecial xlPasteValues, , False, False
.Range("A11").PasteSpecial xlPasteFormats, , False, False
Application.CutCopyMode = False
End With
End Sub