Cyclic copy

[Raul Sousa]

Hello
I have a file with several sheets. Every sheet has its name, like “homeâ€;
“jobâ€; etc.
I need to copy into a new sheet all sheet names and cell.
For example: I need in cell b2, sheet1 name; in cell b3, sheet2 name; in
cell b4, sheet3 name, etc.
Then, I need, in cell c2, sheet1 cell b9 data, in cell c3, sheet2 cell b9
data, in cell c4, sheet3 cell b9 data, etc.
I think this can be done thought a cyclic code. I can’t create this code.
So, any help is most welcome.

 

[Tom Hutchins]

Try this macro. Paste this code in a VBA module in your workbook:

Sub ListSheets()
'Lists all the worksheets in the current workbook.
'Declare variables for this macro.
Dim xx As Integer, NewWS As Worksheet
On Error GoTo LSerr1
'Add a new worksheet
Set NewWS = ActiveWorkbook.Sheets.Add(after:=Worksheets(Worksheets.Count))
For xx% = 1 To (Sheets.Count - 1)
NewWS.Cells(xx% + 1, 1).Value = Sheets(xx%).Name
NewWS.Cells(xx% + 1, 2).Value = Sheets(xx%).Range("B9").Value
Next xx%
'Done. Add headings for the output rows
NewWS.Cells(1, 1).Value = "Sheets in " & ActiveWorkbook.Name
LS_CleanUp:
Set NewWS = Nothing
Exit Sub
LSerr1:
MsgBox "Could not list sheets", vbExclamation, "ListSheets error"
GoTo LS_CleanUp
End Sub

To run it, select Tools >> Macro >>Macros. Select ListSheets, then click Run.

If you are new to macros, this Jon Peltier link may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch