copy and paste macro problem

[jek40]

Hi

i am using the macro below to copy and paste some data from one workbook to another.

When the macro runs I get a message box that says

"there is a large amount of info on the clipboard.
To save it on the clipboard click yes
To delete it and free up memory click no"

How can i stop this message from appearing?

also is there a more efficient way to write this code? especially the copy and paste portion. (code below)

Thanks for looking

appreciate any help

John

Workbooks.Open Filename:="C:\temp\update.xls"
Sheets("udateF").Select
ActiveSheet.Range("b2:f20").Select
Selection.Copy
ActiveWindow.Close
Windows("main data").Activate
Sheets("auto update").Visible = xlSheetVisible
Sheets("auto update").Select
ActiveSheet.Range("b2").Select
ActiveSheet.Paste
Sheets("auto update").Visible = xlSheetVeryHidden
Application.CutCopyMode = False
Sheets("links").Select
MsgBox "   UPDATE COMPLETE" & Chr(13) & "Return to the Main Screen"
Sheets("main menu").Select
End Sub

 

[erik.van.geit]

"there is a large amount of info on the clipboard.
To save it on the clipboard click yes
To delete it and free up memory click no"

How can i stop this message from appearing?

Hi,

this might help
http://www.mrexcel.com/board2/viewtopic.php?t=231700

kind regards,
Erik

 

[jek40]

Thanks Erik

That took care of it

thanks
John

 

[erik.van.geit]

you're WELCOME

I might be wrong but it seems like you could replace all those lines

Sheets("udateF").Select 
ActiveSheet.Range("b2:f20").Select 
Selection.Copy 
ActiveWindow.Close 
Windows("main data").Activate 
Sheets("auto update").Visible = xlSheetVisible 
Sheets("auto update").Select 
ActiveSheet.Range("b2").Select 
ActiveSheet.Paste 
Sheets("auto update").Visible = xlSheetVeryHidden 
Application.CutCopyMode = False 
Sheets("links").Select

by a single line

ActiveWorkbook.Sheets("udateF").Range("b2:f20").Copy Workbooks("main data").Sheets("auto update").Range("B2")

anyway this must be close ...

you don't have to unhide a sheet to write to it
you don't need to select cells to write to them
check out the helpfiles for "Copy"