Error - VBA object does not support this property or method

Question

I am new to coding and trying to do the following:

Workbook tracking contains project information that is taken from an input tutorial. I try to open the input book, copy all the data in the project information sheet and skip it into the project information sheet of the tracking workbook. 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 opens the ok file, but then it seems to stop before copying the data. I can’t pinpoint the error. I searched for several topics with similar problems, but they were out of luck. Is there something wrong with my coding?

Answer 1

The problem with your code is on the second line.

 ActiveSheet("Project Info").Select 

It 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 to avoid using selection and activation to achieve your goals. the ActiveWorkbook property, Application.ActiveWindow property, ActiveSheet property, and ActiveCell property are simply unreliable referrencing methods.

Answer 2

When writing VBA code, it’s best for you to qualify all objects with variables and work directly with the objects themselves.

Try the following:

 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 am also wondering if you really want to copy every cell of the worksheet, or just want to copy cells with actual data?

If you need cells with actual data, you can use wsC.UsedRange.Copy wsP.Range("A1") Note. This may have problems, but I will let you tell me if that is so :)