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

[AliStott]

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 ..... 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
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.

'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

 

[Norie]

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]

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

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]

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]

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