For Each Loop

[Matt]

Could someone tell me why the following sub will not paset each book name
and sheet to its own workbook and sheet. I am obvously doing something
wrong but don't know what.


Sub TestSub()

Dim wk As Workbook
Dim ws As Worksheet


For Each wk In Workbooks
For Each ws In Worksheets
ws.Cells(1, 1) = ws.Name
ws.Cells(3, 1) = wk.Name
Next ws
Next wk



End Sub

 

[Wolf]

Sub TestSub()

Dim wk As Workbook
Dim ws As Worksheet


For Each wk In Workbooks
For Each ws In wk.Worksheets
ws.Cells(1, 1) = ws.Name
ws.Cells(3, 1) = wk.Name
Next ws
Next wk

End Sub

works for me

Best regards

Wolf

 

[Bob Phillips]

Matt,

The problem is caused by not referencing the correct workbook when iterating
through the worksheets. SO you need to add the wk. object qualifier to it


Sub TestSub()

Dim wk As Workbook
Dim ws As Worksheet

For Each wk In Workbooks
For Each ws In wk.Worksheets
ws.Cells(1, 1) = ws.Name
ws.Cells(3, 1) = wk.Name
Next ws
Next wk

End Sub

Incidentally, you can do this with worksheet function s

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,100)

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("file
name",A1))-FIND("[",CELL("filename",A1))-1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Matt]

Excellent, Thanks

Sub TestSub()

Dim wk As Workbook
Dim ws As Worksheet


For Each wk In Workbooks
For Each ws In wk.Worksheets
ws.Cells(1, 1) = ws.Name
ws.Cells(3, 1) = wk.Name
Next ws
Next wk

End Sub

works for me

Best regards

Wolf