Solved

Application defined error

Posted on 2011-03-16
9
204 Views
Last Modified: 2012-05-11
Hi,

I have the below code and it errors on

 ActiveSheet.Paste


Application or object defined error.

Can anyone see why this is?

Thanks
Seamus
Windows("balances.xls").Activate
    Columns("A:I").Select
    Selection.Copy
    Windows("Triple_Merit_Template.xls").Activate
    Columns("A:A").Select
    ActiveSheet.Paste
    Range("A2").Select

Open in new window

0
Comment
Question by:Seamus2626
  • 6
  • 3
9 Comments
 
LVL 7

Expert Comment

by:hippohood
ID: 35150191
You can't paste to a sheet, in general. ¿ou should do something like
ActiveCell.Paste

Open in new window

0
 

Author Comment

by:Seamus2626
ID: 35150247
Thanks hippohood, i get the same error though......
0
 
LVL 7

Expert Comment

by:hippohood
ID: 35150255
I am sorry, I am wrong. Yes, you can use the ActiveSheet.Paste.
The problem is somewhere else, let run check your code and see
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Seamus2626
ID: 35150294
This is my entire sub

Cheers,
Seamus
Sub Fiscal_Merit_Bals()
'
' Fiscal_Merit_Bals Macro
' Macro recorded 09/03/2011 by Kellum
'

'
Dim r As Long

        
    Workbooks.Open Filename:= _
        "G:\Asset Services MI\UnMatched Merit\Triple_Merit_Template.xls"
    Workbooks.Open Filename:="G:\Asset Services MI\UnMatched Merit\Balances.xls"
    
    Windows("Triple_Merit_Template.xls").Activate
    Rows("1:1").Select
    Selection.AutoFilter
    
    
    
    Windows("balances.xls").Activate
    Columns("A:I").Select
    Selection.Copy
    Windows("Triple_Merit_Template.xls").Activate
    Range("A2").Select
    ActiveCell.Paste
    Range("A2").Select
 
    Windows("Balances.xls").Activate
    ActiveWindow.Close
    Range("A2").Select
    
       
End Sub

Open in new window

0
 
LVL 7

Expert Comment

by:hippohood
ID: 35150328
The issue could be one of the followin:
- the mentioned files are not be open when you run the macro
- the mentioned files are open in different INSTANCES of Excel. Try to close both the files, and then open Excel, and open the fioles using File/Open
- there is anothe rprocess, which interrupts macro action
0
 
LVL 7

Expert Comment

by:hippohood
ID: 35150374
In the shown example Line 25 is wrong - you are trying to insert several columns in a cell atrting A2.
You should either define a rectangular area instead of columns (i.e., "A1:I200" instead of "A:I") or incert into cell in the first row (i.e., A1)
0
 
LVL 7

Accepted Solution

by:
hippohood earned 500 total points
ID: 35150481
I could not generate same error with your macro. But it I know it is not unusual to happen, when you are using Copy/Paste with activating Windows. The problem occur because windows don't get activated, turn out to be minimized, editing is in process, active sheet turn out to be a chart, active cell is in a wroing place or a drawing object is active instead of a cell on the target sheet.

Try replacing lines 21-31 with the ones below. Direct references are usually more reliable
Workbooks("balances.xls").Worksheets(1).Range("A1:I250").Copy
    Workbooks("Triple_Merit_Template.xls").Activate
    Workbooks("Triple_Merit_Template.xls").Worksheets(1).Range("A1").Select
    Workbooks("Triple_Merit_Template.xls").Worksheets(1).Paste
    
    Workbooks("Balances.xls").Close

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 35150703
Found it! Some hidden code in Sheet1

Thanks!
0
 
LVL 7

Expert Comment

by:hippohood
ID: 35151773
So, it was an event handler?
Thank you for the points
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question