Copy & Paste between Workbooks - Run-time error '1004'

AliStott

New Member
Joined
Oct 11, 2006
Messages
14
Hi, I've looked through the postings on copy and paste and it appears that a few people have had this problem, but I can't find a solution to suit what I need :confused: ..... apologies if you are repeating an explanation I'm fairly new to this.

Many Thanks
Ali.

AIM
All I am trying to acheive is to copy data (no formulas) from one workbook into another, pasting below previously entered data (hence the variable FirstCell)

RESULT
With the code below I get "Run-time error '1004': Paste method of Worksheet class failed"

FRUSTRATION :eek:
I recorded the macro earlier today and tweeked the code to the script below and IT WORKED. Now it does not and I don't know what I've changed.

Code:
'Find first available row to paste new data
        Worksheets("Contracts Traded").Select
        [a1].Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        Selection.Offset(1, 0).Select
        FirstCell = Selection.Address

        'Activate T File (already open)
        Workbooks(2).Activate
        Worksheets("Summary").Select
            
            'Copy Data
            Range("A2", Range("A2").End(xlToRight).End(xlDown)).Select
            Selection.Copy
            ActiveWorkbook.Save
            ActiveWindow.Close
        
        'Activate T Monthly File and paste data
        Workbooks(1).Activate
        Sheets("Contracts Traded").Select
        Range(FirstCell).Select
        ActiveSheet.Paste
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Ali

The problem probably lies in your use of select.

It's not normally needed when doing this sort of thing.

Also you seem to be closing the workbook you are copying from before you do the paste.

I think that might clear the clipboard and therefore there won't be anything to paste.

Where are you copying/pasting from/to?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

check the VBA-helpfiles !
- declaring variables
useful link: http://www.cpearson.com/excel/DeclaringVariables.htm
- Rows
- Count
- Copy
and any other item you find in this code
there are good examples in the HELP

try this
not sure if this is exactly what you need but at least you'll get the idea
Code:
Sub test()
Dim FBR As Long     'First Blank Row
Dim WB1 As Workbook
Dim WB2 As Workbook


Set WB1 = Workbooks(1) 'better alternatives: 1. ThisWorkbook 2. Workbooks("T FILE")
Set WB2 = Workbooks(2)

FBR = WB1.Worksheets("Contracts Traded").Range("A" & Rows.Count, 1).End(xlUp).Offset(1, 0).Row
WB2.Worksheets("Summary").Range("A2").CurrentRegion.Copy WB1.Sheets("Contracts Traded").Range("A" & FBR)
WB2.Close True
End Sub
kind regards,
Erik

EDIT: your "selects" doesn't seem to be wrong in your code, but they can slow down the process
 

AliStott

New Member
Joined
Oct 11, 2006
Messages
14
Thank you for your responses, they certainly gave food for thought. I will be making a concerted effort not to constantly use Select (I think I breakdown my actions too much)

The Pearson Software Consulting Link is now bookmarked!

Thanks!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
You're welcome :)
I think I breakdown my actions too much
that's the result of working with the macrorecorder: it's a very useful tool, but you need to clean it up to get good flexible code
 

Forum statistics

Threads
1,137,207
Messages
5,680,194
Members
419,888
Latest member
Prasad K

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