Select sheet code not working (using variables)

[Adrae]

The line of code in red is not working....nothing happens...the rest of the code runs on a single sheet over and over again. Help please.

Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim ws As Worksheet
Dim shname As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.AskToUpdateLinks = False



With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks

If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
If Dir(.FoundFiles(i)) <> ThisWorkbook.Name Then
Set wbResults = Workbooks.Open(.FoundFiles(i))
For Each ws In wbResults.Worksheets
ws.Activate
Cells.Select
Selection.Copy
ThisWorkbook.Activate
shname = ws.Name
Sheets(shname).Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next ws
End If

wbResults.Activate
ActiveWorkbook.Close

Next i
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.AskToUpdateLinks = True

 

[jindon]

Hi

Not sure if this is what you mean

place shname = ws.name to right after the line of ws.activate


P.S. This kind of error can be avoided if you don't activate/select the object.