CODEWITHSUNDEEP
excelvba
Enrico Caruso
Enrico Caruso

Reputation: 71

Error - VBA Object doesn't support this property or method

I am new to coding and am trying to perform the following;

Tracking Workbook contains project information which is taken from an input workbook. I am trying to open the input workbook copy all the data on the project info sheet and past it in the tracking workbook's project info sheet.Here is my coding below

Sub GetProjectInfo()

    Workbooks.Open Filename:="\\ccprd02\workgrp2\AM\SPIM Input.xlsm"
    ActiveSheet("Project Info").Select
    Cells.Select
    Selection.Copy
    Windows("SPIM Tracking Sheet.xlsm").Activate
    ActiveSheet("Project Info").Select
    Cells.Select
    Range("A1").Activate
    ActiveSheet.Paste
    Range("A1").Select
    Windows("SPIM Input.xlsm").Activate
    ActiveWindow.Close

End Sub

It is opening the file ok but then seems to stop just before copying the data. I can't seem to pinpoint the error. I have searched several threads with similar issues but have no had any luck. Is there something wrong with my coding?

Upvotes: 7

Views: 17602

Answers (2)

user4039065
user4039065

Reputation:

The problem with your code is in the second line.

ActiveSheet("Project Info").Select

This should be,

ActiveWorkbook.Sheets("Project Info").Select
'alternative
Worksheets("Project Info").Select

As mentioned in my comment, see How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals. The ActiveWorkbook property, Application.ActiveWindow property, ActiveSheet property and ActiveCell property are just not reliable methods of referrencing.

Upvotes: 8

Scott Holtzman
Scott Holtzman

Reputation: 27269

When writing VBA code, you are best served by qualifying all objects with variables and working directly with the objects themselves.

Try this:

Dim wbC as Workbook
Dim wbP as Workbook
Dim wsC as Worksheet
Dim wsP as Worksheet

Set wbP = Workbooks("SPIM Tracking Sheet.xlsm")
Set wsP = wbP.Sheets("Project Info")
Set wbC = Workbooks.Open (Filename:="\\ccprd02\workgrp2\AM\SPIM Input.xlsm")
Set wsC = wbC.Sheets("Project Info")

wsC.Cells.Copy wsP.Range("A1")

wbC.Close False 'assuming you want to close the workbook you opened.

I also wonder if you really want to copy every cell of the worksheet or do you just want to copy cells with actual data?

If you just want cells with actual data, you can use wsC.UsedRange.Copy wsP.Range("A1") Note - this could have issues as well, but I will let you tell me if it does :)

Upvotes: 4

Related Questions