Untested, but give this a first-try on backup copies of allstar.xls and
snapshot_report.xls

Sub test()
'Setting up:
'In the workbook "Snapshot Report.xls", in the worksheet "Tickers" I input
'stock symbols in A2 through A500.
'Macro:

For Each c In Workbooks("snapshot report.xls").Sheets("tickers").Columns(1)
'process for each row
'Step 1) Insert new blank worksheet in Snapshot Report.xls
'dealt with as part of step 6
'
'Step 2)
'In Snapshot Report.xls / Tickers, copy the cell A2 and paste value in
cell
'E4 of Allstar.xls / Snapshot.
Workbooks("allstar.xls").Sheets("snapshot").Range( "e4").Value = _
Workbooks("snapshot report.xls").Sheets("tickers").ActiveCell.Value
'
'Step 2)
'Run another macro in "Allstar.xls" (e.g., Application.Run "Batman") to
'generate a report on the worksheet "Snapshot".
applicaton.Run ("allstar.xls!batman")
'
'3) Copy the entire worksheet "Snapshot."
'4) Paste values into the blank new worksheet of Snapshot Report.xls.
'5) Paste format -- including column widths.
'6) Rename the worksheet where the values and formats were just pasted
by the
'value in cell E4 of the same worksheet.
Workbooks("allstar.xls").Worksheets("snapshot").Ce lls.Copy _
Destination:=Workbooks("snapshot report.xls").Sheets(Sheets.Count +
1).Range("a1")
Workbooks("snapshot report.xls").Sheets(Sheets.Count).Name =
Range("E4").Value
Next c 'process next ticker
End Sub


"SteveC" wrote in message
...
I have two workbooks. Here is what I'd like a program to do.

Setting up:
In the workbook "Snapshot Report.xls", in the worksheet "Tickers" I input
stock symbols in A2 through A500.

Macro:

Step 1)
Insert new blank worksheet in Snapshot Report.xls

Step 2)
In Snapshot Report.xls / Tickers, copy the cell A2 and paste value in cell
E4 of Allstar.xls / Snapshot.

Step 2)
Run another macro in "Allstar.xls" (e.g., Application.Run "Batman") to
generate a report on the worksheet "Snapshot".

3) Copy the entire worksheet "Snapshot."

4) Paste values into the blank new worksheet of Snapshot Report.xls.

5) Paste format -- including column widths.

6) Rename the worksheet where the values and formats were just pasted by
the
value in cell E4 of the same worksheet.

7) Return to step 2, but this time move to cell A3..., repeat steps 3-6
and
return to step 2, but this time move to cell A4, etc., until reports are
created for all the tickers that have been inputted in A2:A500. E.g., if
only 20 tickers were entered, only 20 additional worksheets/reports should
be
created.

Basically this creates a report in a few seconds, saving me a lot of time.

Thanks for taking a look!

SteveC