October 16th, 2013, 09:36 AM
Instead of looping all sheets, look wanted sheets.
Hi there, I have created this code that loops through every sheet looking for a value over 0 in an active cell..
However, now I want to change it so it starts at sheet "Q7.1" and the loops to the end!
Anyone know how to solve this?
Application.ScreenUpdating = False
' Declare Current as a worksheet object variable.
Dim current, ws As Worksheet
Dim wsCount As Integer
Dim i, x As Integer
Dim actCell, pstRow As Range
Dim actAdd As String
wsCount = ActiveWorkbook.Worksheets.Count
Set actCell = Application.InputBox(prompt:="Input Cell", Type:=8)
actAdd = actCell.Address
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Result" & Worksheets.Count
Set ws = Sheets("Result" & (Worksheets.Count - 1))
ws.Cells(1, 1).Value = "Sheet Name"
ws.Cells(1, 2).Value = "Value"
For i = 1 To wsCount
Set current = ActiveSheet
If ActiveCell > 0 Then
If ActiveCell = "Sheet Name" Then GoTo nextsh
Set pstRow = ws.Range("B65536").End(xlUp).Offset(1, 0)
pstRow.Offset(0, -1).Value = current.Name
Application.ScreenUpdating = True