[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Loop vertically, copying and pasting inputs for each cell in Col A

Steve C

12/15/2006 7:22:00 PM

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
2 Answers

Jef Gorbach

12/16/2006 6:40:00 AM

0

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").Cells.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" <SteveC@discussions.microsoft.com> wrote in message
news:DDE6E694-2CAF-405B-82D2-3D59020C0BA8@microsoft.com...
> 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
>
>
>

Steve C

12/18/2006 3:57:00 PM

0

It seems the syntax error was fixed when I removed the colon from this:

Destination:=Workbooks("Snapshot Report.xls").Sheets(Sheets.Count +
1).Range("a1")

so it became this:

Destination = Workbooks("Snapshot Report.xls").Sheets(Sheets.Count +
1).Range("a1")


Now I have a new error:

Run-time error '438':
Object doesn't support this property or method

The following code is then highlighted:

Workbooks("Allstar.xls").Sheets("Snapshot").Range("e4").Value = _
Workbooks("Snapshot Report.xls").Sheets("Tickers").ActiveCell.Value

Thanks for any suggestions...






"SteveC" wrote:

> Jeff, Thanks for taking a look at this.
>
> Here is what I have now, based on your suggestion:
>
> Sub Snapshot_Multiple()
>
> For Each c In Workbooks("snapshot report.xls").Sheets("tickers").Columns(1)
>
> Workbooks("allstar.xls").Sheets("snapshot").Range("e4").Value = _
> Workbooks("snapshot report.xls").Sheets("tickers").ActiveCell.Value
>
> Application.Run ("allstar.xls!RefreshWorksheet")
>
> Workbooks("allstar.xls").Worksheets("Snapshot").Cells.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
>
> End Sub
>
> I get an error: "Compile Error: Syntax Error" and the following line is
> highlighted and the font turns red:
>
> Destination:=Workbooks("Snapshot Report.xls").Sheets(Sheets.Count +
> 1).Range("a1")
>
> I foooled around wtih the code a bit, but couldn't get it to work... any
> suggestions? Thanks for your time...
>
> SteveC
>