By-passing clipboard message

[Ian M]

I want a macro to copy cells A1 to P392 from one Workbook to another.

However when I run the macro I get the automatic Excel dialogue box
reading,

"There is a large amount of text in the clipboard. Do you want to be
able to paste this information into another program later?

for which I have to click YES for the rest of the macro to run.

- Is there any way I can get the Macro to click YES for me? or

- Is there any other way I can transfer these cells other than the
COPY and PASTE command?

Kind regards

Ian M

 

[Jake Marx]

Hi Ian,

You can use Application.CutCopyMode=False after the paste to supress the
message.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Ian M]

Thanks Jake

However I'm not sure exactly where I should put you line of code in my
macro.

My macro is:

Selection.SpecialCells(xlCellTypeLastCell).Select
Range(Selection, Cells(1)).Select
Selection.Copy
ActiveWorkbook.Close
Range("A1").Select
ActiveSheet.Paste

I've tried putting your line before or after but it doesn't seem to
work. I must be doing something wrong?

Thanks again for your help.

Kind regards

Ian M

 

[Dave Peterson]

You would usually add Jake's line of code before you close your workbook. But
since you do your paste after you close the workbook, that won't work for your
existing code.

But this did:

Option Explicit
Sub testme01()
Selection.SpecialCells(xlCellTypeLastCell).Select
Range(Selection, Cells(1)).Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Range("A1").Select
ActiveSheet.Paste
End Sub

But I think I'd take a slightly different approach. You rely on the window to
be active when you close one workbook to be open to the worksheet where you want
to paste.

If you could specify the "from" worksheet and the "to" worksheet, then you could
do something like this:

Option Explicit
Sub testme01B()

Dim FromWks As Worksheet
Dim ToWks As Worksheet

Set FromWks = ActiveSheet
Set ToWks = Workbooks("book2").Worksheets("sheet1")

With FromWks
.Range("a1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy _
Destination:=ToWks.Range("a1")

Application.CutCopyMode = False
.Parent.Close savechanges:=False 'true '????
End With

End Sub