[RESOLVED] Looping through sheets problems and setting of variables - vba

[gphillips]

I am doing this simple routine - checking for values that contain prefix IK column A in all sheets except the IK ouput which is the ouput sheet only.

I am having difficulty setting the varaible in the correct way and getting it to work.

Please see comments which explains some of the difficulties.

Can anyone help debug??

Sub IKlist()
 
 
Dim wksSheet As Worksheet
Dim i As Integer
Dim thiscell As String
Dim Filelength As Integer
Dim obook As Workbook
Dim xlapp As Excel.Application
Dim j As Integer
 
' CREATE OUTPUT SHEET IK Output
 
'Count Worksheets
sheetcount = Worksheets.Count
 
'Add worksheets after the last sheet
Worksheets.Add After:=Sheets(sheetcount)
 
'Count Worksheets
sheetcount = Worksheets.Count
 
'Name the sheet and add the number of sheets
Worksheets(sheetcount).Name = "IK output"
 
'set varaibles to work with the active workbook and any sheet except the ouput sheet
 
Set xlapp = Excel.Application    'work with active excel application
Set obook = xlapp.ActiveWorkbook    'work with active workbook - not specific
Set wksSheet = ??????????????  'set wksheet to be any worksheet in the active workbook. This is because I am looping through each worksheet
 
' Loop through each worksheet in active workbook - not a specific work book- looking for values that contain prefix IK  and copying it to the IK output sheet
 
' The output sheet  is the ouput sheet only - therefore we should not loop through this sheet - its only used for poulating results
 
 
Filelength = wksSheet.usedrange.Rows.Count
   j = 1
For Each wksSheet In obook.Worksheets
 
    For i = 1 To Filelength
    thiscell = Cells(i, 1)
    If Left(thiscell, 2) = "IK" Then
    Worksheets("IK output").Cells(j, 1).Value = Cells(i, 1).Value
    End If
    j = j + 1
    Next i
Next wksSheet
 
 
 
End Sub