Hi, I am trying to copy a section of data from one workbook to another
via macro code. However the CLIPBOARD message pops up asking me to
either save the data on the clipboard(YES) to process later or Delete
the clipboard(NO).

I have tried to use the statement
Application.CutCopyMode = False, but that only generates
the Run-time error 1004: Method 'Paste of Object' Worksheet Failed

Below is my code:
Set text1_WB = Workbooks("TAPE1.TXT")
Set text1_sheet = text1_WB.Sheets("TAPE1")

Set text2_WB = Workbooks("TAPE2.TXT")
Set text2_sheet = text2_WB.Sheets("TAPE2")

text2_WB.Activate
text2_sheet.Select
Range("C1:N4000").Copy 'Copy Additional Channel
Columns
' Application.CutCopyMode = False
' Uncommenting the above statement will generate message:
' Run-time Error 1004: Method 'Paste of Object' Worksheet failed
text2_WB.Close 'Close TAPE2.TXT (do not
save)

text1_WB.Activate
text1_sheet.Select
Range("O1").Select
text1_sheet.Paste 'Inactivate CLIPBOARD
message???
' Clipboard dialog is displayed.
' Will work ONLY If 'YES' is chosen, 'NO' will receive the message:
' Run-time Error 1004: Method 'Paste of Object' Worksheet failed

PROGRAM Fails on
text1_sheet.Paste

I would like NOT to have to REPLY to the CLIPBOARD message everytime.
Any help would be much appreciated. Thank you.

Leslie

Your closing you workbook too soon

this is all you need.

Set text1_WB = Workbooks("TAPE1.TXT")
Set text1_sheet = text1_WB.Sheets("TAPE1")

Set text2_WB = Workbooks("TAPE2.TXT")
Set text2_sheet = text2_WB.Sheets("TAPE2")

text2_sheet.("C1:N4000").Copy _
text1_Sheet.Range("O1")

Application.CutCopyMode = False

text2_WB.Close

--
Regards,
Tom Ogilvy




"looloo" wrote in message
ups.com...
Hi, I am trying to copy a section of data from one workbook to another
via macro code. However the CLIPBOARD message pops up asking me to
either save the data on the clipboard(YES) to process later or Delete
the clipboard(NO).

I have tried to use the statement
Application.CutCopyMode = False, but that only generates
the Run-time error 1004: Method 'Paste of Object' Worksheet Failed

Below is my code:
Set text1_WB = Workbooks("TAPE1.TXT")
Set text1_sheet = text1_WB.Sheets("TAPE1")

Set text2_WB = Workbooks("TAPE2.TXT")
Set text2_sheet = text2_WB.Sheets("TAPE2")

text2_WB.Activate
text2_sheet.Select
Range("C1:N4000").Copy 'Copy Additional Channel
Columns
' Application.CutCopyMode = False
' Uncommenting the above statement will generate message:
' Run-time Error 1004: Method 'Paste of Object' Worksheet failed
text2_WB.Close 'Close TAPE2.TXT (do not
save)

text1_WB.Activate
text1_sheet.Select
Range("O1").Select
text1_sheet.Paste 'Inactivate CLIPBOARD
message???
' Clipboard dialog is displayed.
' Will work ONLY If 'YES' is chosen, 'NO' will receive the message:
' Run-time Error 1004: Method 'Paste of Object' Worksheet failed

PROGRAM Fails on
text1_sheet.Paste

I would like NOT to have to REPLY to the CLIPBOARD message everytime.
Any help would be much appreciated. Thank you.

Leslie

Tom, Thank you. the code worked perfectly AND you have helped me
realize how I can shorten my coding techniques as well.

Much appreciative.

Leslie