0

Im using following code to select a particular range in a selected sheet in excel

Sub openWordvanuitWord()

Dim excelApp As Excel.Application
Dim Excel As Excel.Workbook
Dim sht As Excel.Worksheet

Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True

Set Excel = excelApp.Workbooks.Open("C:\Documents and   Settings\aa471714\Desktop\Book1.xls")
Set sht = Excel.Worksheets(1)


With sht

.Range("b3:h5").Select


End With

End Sub

Problem is that it want it to select sheet 2. Only when I change

Set sht = Excel.Worksheets(1)

to

Set sht = Excel.Worksheets(2)

I get an error

Anybody suggestions?

Community
  • 1
  • 1
user181796
  • 185
  • 7
  • 22

1 Answers1

1

You can only set the selection on the active worksheet. So you have to activate it first: 

Set sht = Excel.Worksheets(2)
sht.activate
With sht
    .Range("b3:h5").Select
End With

Btw: In most cases you don't have to work with the selection (you can read and write values without using that, for example) but that's another topic...

KekuSemau
  • 6,830
  • 4
  • 24
  • 34
  • + 1 on the last sentence. For OP: Please avoid the use of `.Select/.Activate` [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) – Siddharth Rout Feb 07 '14 at 11:02