Hello Ptkelly,
A copy of the macro follows at the end, but I will describe the steps
taken to create it if you want to make your own (to do this or a
similar task).
Recording the Macro
===================
[0] Before you start... I started w/ a new spreadsheet w/ 3 sheets. In
your case, you could do this with a copy of your "master position
sheet" as long as you have the extra sheets for the long / short
tables. Use
Insert -> Worksheet
and drag/drop the sheet tabs at the bottom to order the sheets them
the way you like.
[1] I entered some dummy data into the first sheet as follows:
Symbol How much Value
AAA 100 10
BBB 200 20
AAA 100 15
CCC 100 -10
DDD 100 -20
EEE 200 30
(seven rows, three column)
If your existing data has some other layout, make the changes I note
in the various steps that follow (or in the macro at the end).
[2] Select the upper left cell use:
Tools -> Macro -> Record New Macro
a dialog box appears, I changed the name to LS, and then selected OK.
I got a floating toolbar w/ a "stop" button and "relative reference"
button. I moved the toolbar out of the way (but where I can still see
it).
[3] Then I used
Data -> Filter -> Autofilter
and saw the three headings replaced by a set of pop up buttons. Each
should have a little up/down arrow icon on the right. I'll represent
that as ^v in the text below.
[4] Select
Value^v -> Custom Filter [or whatever your value column title is]
(be sure to click on ^v in the Value cell to get the pop up menu) and
another dialog box should appear. Change Equals to "Is Greater Than"
and set the value to zero. Select OK and you should see just your long
positions. On my system, the ^v changed from black to a blue color.
[5] Select
Edit -> Go To -> Special -> Current Region
and then OK. At this point the upper left region will be selected with
all of your long positions included.
[6] Select
Edit -> Copy
Go to the second sheet, select the upper right cell and select
Edit -> Paste
The cells representing your long position should now be on the second sheet.
[7] Go back to the first sheet and repeat steps 4-6 with the following differences:
- [4] Select "Is Less Than" as the filter method
- [5] Don't forget to reselect the current region
- [6] paste into the third sheet
[8] Go back to the first sheet and select
Data -> Filter -> Auto Filter
to turn off the filtering and select the upper left cell. Finally
select the stop button to stop recording the macro. It took about 10
steps to get to this point and the macro is complete.
Copy the Macro
==============
As an alternative to recording the macro you can copy the text of the
macro at the end and then:
[1] Select
Tools -> Macro -> Visual Basic Editor
a couple windows will pop up.
[2] Select
Insert -> Module
and a window will pop up.
[3] Paste the macro into the window. Change the
Field:=3
to whatever column number has your value data in the two places it
appears. Change the sheet numbers (1, 2, 3) to the sheets in your
workbook if you did not want the sheets ordered as Master Data, Long,
Short.
[4] Select
Quit and Return to Microsoft Excel.
At this point, you should be ready to run the macro as noted in the
next section. It should also be saved with the worksheet of master
position data. If you want to save the macro in another worksheet, let
me know with a clarification request so I can describe that process.
Updating the Tables
===================
When you update the master position sheet, the other sheets won't be
updated until you rerun the macro. To do that, simply:
Tools -> Macro
select LS and then
Run
and the two other sheets will be updated again. On my system, it took
a few seconds for the cursor to stop blinking and the sheets were
updated.
Adding the Button
=================
To add the button, do the following steps.
[0] Make sure the Forms toolbar is shown. If not, try
Tools -> Customize
and make sure Forms has a checkmark (as a toolbar) before using OK.
[1] The "button" on my system is the fourth toolbar item but its
position will vary based on the width / height of the toolbar. I just
pointed at the choices with the mouse until one said "button" and then
I selected it. Click / drag across the part of the sheet where you
want the button. A dialog box shows up and set the macro name to LS.
Select OK.
[2] At this point, the button can be resized, moved, and you can
change the label. I named mine "update" but you can use whatever word
you want. Click outside the button when you have it the way you want.
[3] Verify it works OK by clicking the button again and checking the
long / short sheets. They should be updated by the macro.
Looking at the Macro
====================
If you are interested, you can review the source code of the macro by using:
Tools -> Macro
make sure LS is selected, and then
Edit
to bring up the visual basic editor. It should look something like the
listing below. To return to Excel, use
Close and Return to Microsoft Excel
That covers everything I can think of. The answer gives you two ways
to get the macro into your worksheet, tells you how to add buttons,
and gives you an idea how to create your own macros using the record
features in Excel.
If any step of this process is unclear or does not work for you -
please use a clarification request. I will be glad to help. Good luck
with your stock picks and I hope will help you.
--Maniac
Sub LS()
'
' LS Macro
' Macro recorded 7/30/2004 by Maniac
'
'
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=">0", Operator:=xlAnd
Selection.CurrentRegion.Select
Selection.Copy
Sheets(2).Select
ActiveSheet.Paste
Sheets(1).Select
Selection.AutoFilter Field:=3, Criteria1:="<0", Operator:=xlAnd
Selection.CurrentRegion.Select
Selection.Copy
Sheets(3).Select
ActiveSheet.Paste
Sheets(1).Select
Selection.AutoFilter
Range("A1").Select
End Sub |
