copy and paste macro problem

jek40

Active Member
Joined
Jan 17, 2005
Messages
317
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


Code:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you're WELCOME :)

I might be wrong but it seems like you could replace all those lines
Code:
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
Code:
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

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
Top