VBA With / select statement not working

[IanWallbridge]

Hi all,

I have a VBA script that combines data from two different workbooks. This includes a need to change the format of some data, but this part of the script isn't working as expected:

Set extwbk = Workbooks.Open(myLSSource)
Set x = extwbk.Worksheets("Sheet1").Range("$A$2:$O$10000")
Set y = extwbk.Worksheets("Sheet1").Range("A:A")
Set twb = Workbooks.Open(mySIPSource)

With extwbk
Range("A:A").Select
    With Selection
    .NumberFormat = "000000"
    .Value = .Value
    End With
End With

Although I've used a With statement, debugging the script shows that the Range("A:A").Select is acting on the workbook twb, and not extwbk. Can anyone shed any light on this?

Many thanks,

Ian

 

[eduzs]

Try:

.Range("A:A").Select

 

[Fluff]

You cannot select a range on a non active sheet, yoou also need to specify the sheet, try

Set extwbk = Workbooks.Open(myLSSource)
Set x = extwbk.Worksheets("Sheet1").Range("$A$2:$O$10000")
Set y = extwbk.Worksheets("Sheet1").Range("A:A")
Set twb = Workbooks.Open(mySIPSource)

With extwbk.Sheets("Sheet1").Range("A:A")
    .NumberFormat = "000000"
    .Value = .Value
End With