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

Question

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?

@findwindow - What objects? The whole sub is based upon Select, Activate and ActiveWorkbook. — , Dec 24 '15 at 18:06
Only if they are vars that have been dimmed as a worksheet type or range type and then they need to be `Set` to a worksheet or range of cells. — , Dec 24 '15 at 18:08
Enrico - see [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for methods on getting away from relying on select and activate to accomplish your goals. — , Dec 24 '15 at 18:09
@findwindow the `Set` keyword is used for assignment. There is no assignment in this code. (The assignment operator is `=`, which does not appear at all.) — phoog, Dec 24 '15 at 18:11
Heh. I never considered it a two step process but makes sense it is. — findwindow, Dec 24 '15 at 18:12
@findwindow - it's not that the variable is the object. A workbook / worksheet / cell is always an object. However, the only time you would use `Set` is when you want to assign an object to a variable of that same object type. *edit - maybe you already understood that, but just thought it was worth making clear, based on your previous comment* — Scott Holtzman, Dec 24 '15 at 18:13
Scott's more articulate than Jeeped /me hides Edit: the clarification is great. It's not a distinction I ever truly thought about. — findwindow, Dec 24 '15 at 18:15

score 8 · Answer 1 · edited May 23 '17 at 12:31

8

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.

edited May 23 '17 at 12:31

Community

1
1

answered Dec 24 '15 at 18:16

3

nice way to point out a main issue without manipulating the original code more than needed :) *needed being relative, of course :)* – Scott Holtzman Dec 24 '15 at 18:19

Scott Holtzman · Answer 2 · 2015-12-24T18:10:38.480

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 :)

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

2 Answers2