0
 Dim wb As Workbook
    Set wb = Application.Workbooks("Book2.xlsx")
    wb.Activate
    wb.Sheets("Sheet1").Range("A1").Select

Ps: Some times above code working properly ,most of times throwing exception

Looking at this link Run Time Error '1004': Select method of Range Class failed using ThisWorkbook i did the same as above still failing

user1844634
  • 1,221
  • 2
  • 17
  • 35

2 Answers2

0

try to fist activate the sheet and then use the select but first of all do you really need the select? It's the most expensive method you can call (still sometimes needed)

If you just need to read/write a value into the cell you can do it this way

Dim wkb as Workbook
Dim wks as Worksheet

wkb = Application.Workbooks("Book2.xlsx")
wks = wb.Sheets("Sheet1")

System.Windows.Forms.MessageBox.Show(wks.Range("A1").Value2)
' or use this code for selecting
' With wks
'   .Activate()  ' not really sure you need it, test it
'   .Range("A1").Select
' End With
PetLahev
  • 688
  • 3
  • 18
  • wb = Globals.ThisWorkbook.Application.Workbooks.Open(Filename:=fileName, ReadOnly:=False) wb.Activate() TemplateSheet = Globals.ThisWorkbook.Sheets.Add(After:=Globals.ThisWorkbook.ActiveSheet) TemplateSheet.Activate() TemplateSheet.Cells(1, 1).select() Even i tried activating the sheet, Though it's not working – user1844634 Sep 07 '17 at 12:22
  • I guess it throws the error on the last line? Try if the sheet activate command works. I can't test now don't have Visual Studio with me. Just tested in VBA this and it seems to be working `Sub test() Dim wkb As Workbook Set wkb = Application.Workbooks.Add Dim wks As Worksheet Set wks = wkb.Sheets.Add wks.Activate wks.Cells(10, 1).Select End Sub` – PetLahev Sep 07 '17 at 12:40
  • I tried with sheet activate command. It works some times, some times it throws the error. Strange.. – user1844634 Sep 07 '17 at 13:48
0

if you want to reference a range in a different sheet, you can't use select metod. you should write your code as following

Application.Goto Sheets(1).Range("A1")
Volkan Yurtseven
  • 425
  • 3
  • 15