Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic copy
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 cant create this code. So, any help is most welcome. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cyclic copy
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(Worksh eets.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/200...e-elses-macro/ Hope this helps, Hutch "Raul Sousa" wrote: 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 cant create this code. So, any help is most welcome. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average cyclic error | Excel Worksheet Functions | |||
Graphing cyclic data | Excel Discussion (Misc queries) | |||
Display Cyclic Error as Blank ? | Excel Programming | |||
How do I correct a cyclic redundancy check error | Excel Discussion (Misc queries) | |||
cyclic redundancy check | Excel Programming |