For Each Loop
 

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

For Each Loop
 

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


-----Original Message-----
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




.

For Each Loop
 

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" wrote in message
...
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

For Each Loop
 

Excellent, Thanks



"Wolf" wrote in message
...
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


-----Original Message-----
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




.