0

I am trying to open multiple excel worksheets for one workbook. I have following code but it generates error on Sheets("sheet3").select line.

I don't know why?

Set xlSheet = xlBook.Worksheets(3)

With xlSheet
    .Name = "sheet3"
    ....
    ....
    ....

 i = 6
    Do While Not rsl.EOF
        .Range("A" & i).Value = Nz(rsl!x, "")
        .Range("B" & i).Value = Nz(rsl!y, "")
        .Range("C" & i).Value = Nz(rsl!z, "")
        .Range("D" & i).Value = Nz(rsl!xx, "")
        .Range("E" & i).Value = Nz(rsl!yy, "")
        .Range("F" & i).Value = Nz(rsl!zz, "")
        .Range("G" & i).Value = Nz(rsl!xxx, "")
        .Range("H" & i).Value = Nz(rsl!yyy, "")
        .Range("I" & i).Value = Nz(rsl!zzz, "")           
        i = i + 1
        rsl.MoveNext
    Loop
    End With


With xlSheet
 Set rng = .Range("A6:I5000")
 Sheets("sheet3").Select
 rng.FormatConditions.Add Type:=xlExpression,   Formula1:=""
 rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
 With rng.FormatConditions(1).Interior
     .PatternColorIndex = xlAutomatic
    .Color = ANy Color      
 End With


Set rng = .Range("A6:I5000")
Sheets("sheet3").Select
rng.FormatConditions.Add Type:=xlExpression, Formula1:=""
rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
With rng.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = Any color
    .TintAndShade = 0
End With

End With

Do I need to replace Sheets("").Select with something else? Or how do I achieve it?

Thanks,

user659469
  • 325
  • 1
  • 7
  • 22
  • what error do you get? That may be pointless, but could it result from "sheet3" instead of "Sheet3"? Try the latter naming – EngJon Jan 29 '16 at 15:24
  • 2
    you don't need `Sheets("sheet3").Select` at all! Because you're already working directly on the `xlSheet` object that is already set to `sheet3`! Here is great primer for [Avoiding Select Statements](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). And so you know, the error occurs because the line `Sheets("sheet3").Select` is simply invalid syntax inside the `With` block It's *almost* like saying `Sheets("sheet3").Sheets("sheet3")` and the object `Sheets` is not an object of `Sheets` object. – Scott Holtzman Jan 29 '16 at 15:28
  • 1
    Thanks Scott. It makes sense. It's working now. – user659469 Jan 29 '16 at 15:57

0 Answers0