0

Very new VBA user here. I keep getting an error when I have copied over a range of cells from 1 workbook to another & then go to select a new cell. When I open the workbook that the data was copied too there is still the selection active where the data was pasted & I'm thinking this is causing the error but I would expect once I clicked a new cell the selection should cancel?

The error is occurring in the 3rd line of code:

'Select Method of Range Class Failed'

The strange thing is I do the same in some code earlier & it works.

Workbooks("PS 2019_June 19_updated (Macro).xlsm").Worksheets(8).Range("A2").CurrentRegion.Copy
Workbooks("PS & Config - Actuals & FC.xlsm").Worksheets(3).Range("a2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Workbooks("PS & Config - Actuals & FC.xlsm").Worksheets(3).Range("a1").Select
Workbooks("PS & Config - Actuals & FC.xlsm").Worksheets(3).Range("a2").Select
Vityata
  • 42,633
  • 8
  • 55
  • 100
Mturks83
  • 89
  • 2
  • 9
  • 1
    you must activate the workbook and worksheet before trying to select a cell. – Scott Craner Nov 20 '19 at 23:08
  • Thanks Scott - the strange thing is there is code exactly the same as the above the runs before it but for different data & it works? – Mturks83 Nov 20 '19 at 23:15
  • 1
    @Mturks83 which is probably the reason the second set does not work, the other workbook is the active workbook. – Sorceri Nov 20 '19 at 23:17

1 Answers1

0

First things first - try to avoid .Select and the .Activate from every VBA - How to avoid using Select in Excel VBA.

Concerning the error - it appears, because it is trying to select a cell in a non-selected worksheet. To avoid it, try adding this:

Before this line:

Workbooks("PS & Config - Actuals & FC.xlsm").Worksheets(3).Range("a1").Select

Write this line:

Workbooks("PS & Config - Actuals & FC.xlsm").Worksheets(3).Select
Vityata
  • 42,633
  • 8
  • 55
  • 100