Check if sheet name exists, and insert sheet name and cell value into a table if true

[Gregory72]

Hi,

I'm havingconsiderable trouble writing a macro to do the following:

1) Select aparticular sheet in the workbook

2) Select a cell inthat worksheet

3) Check using a forloop if sheets exists and then insert data into a table if true
The sheets I'mchecking for existence (whether they are visible or not visible does not matterto me) are named "01", "02", "03", ... ,"99".
I want to insert thevalue of a particular cell within the sheet being checked into the active cell.I also want to insert the sheet name into the cell immediately left of theactive cell.

4) Select the cellimmediately below the active cell until the for loop is completed

5) Select a cell atthe top of the sheet


This is what Ihave:



SubFormChecklistTitles()

'1)
Sheets("FORMCHECKLIST").Select

'2)
Range("C4").Select

'3)
Dim i As Integer

For i = 1 To 99

If Sheets("0" &i).exist Or Sheets(i).exist = True Then

If i <= 9 Then
ActiveCell.Value ="='0" & i & "'!$M$1"
Else
ActiveCell.Value ="=" & i & "'!$M$S1"
End If

'4)
ActiveCell.Offset(0, -1).Select

Else
End If

Next
'5)
Range("C1").Select
End Sub



Thanks in advancefor any help.

 

[Ferdi24]

Hi, nice explanation in your post!

Try this:

Sub FormChecklistTitles()

'1)
Sheets("FORMCHECKLIST").Select


'2)
Range("C4").Select


'3)
Dim ws As Worksheet
Dim i As Integer


For Each ws In ActiveWorkbook
    If IsNumeric(ws.Name) Then
        i = CInt(ws.Name)
            If i <= 9 Then
                ActiveCell.Value = "='0" & i & "'!$M$1"
            Else
                If i <= 99 Then
                    ActiveCell.Value = "=" & i & "'!$M$S1"
                End If
            End If
    End If


    '4)
    With ActiveCell
        .Offset(0, -1).Value = ws.Name
        .Offset(1).Select
    End With
Next ws


'5)
Range("C1").Select
End Sub

 

[Gregory72]

Thanks for your help Ferdi24,

I'm getting the following error message:
"Object doesn't support this property or method"
while highlighting in the code:
"For Each ws In ActiveWorkbook"

I'm not sure how to fix this. Any suggestions?

 

[Ferdi24]

Ups, sorry, it should be

For Each ws in ActiveWorkbook.sheets

 

[Gregory72]

Nice! Works great! The only change I made was to put the With statement inside the If IsNumeric statement so only numbered forms were inserted.
Thanks for your assistance. Here's my final code:


Sub FormChecklistTitles()


'1)
Sheets("FORM CHECKLIST").Select




'2)
Range("C4").Select




'3)
Dim ws As Worksheet
Dim i As Integer




For Each ws In ActiveWorkbook.Sheets


If IsNumeric(ws.Name) Then
i = CInt(ws.Name)

If i <= 9 Then
ActiveCell.Value = "='0" & i & "'!$M$1"
Else
If i <= 99 Then
ActiveCell.Value = "='" & i & "'!$M$1"
End If
End If

'4)
With ActiveCell
.Offset(0, -1).Value = ws.Name
.Offset(1).Select
End With
End If
Next ws




'5)
Range("C1").Select
End Sub