need macro to call a workbook w/o the actual name of the workbook
 

In pseudo code

If workbooks.count < 2 then Exit Sub
For each wb in workbooks
If wb.name < activeworkbook.name then
' do the stuff here, this is the one
End If
next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"crimsonkng" wrote in message
...
I have two workbooks open. In my macro, I don't want to refer to them by
their names because, basically, I never know what the name of the files
will
be. In other words, if I'm in one workbook, I merely want my macro to go
to
"the other workbook." And, then, if I'm in THAT workbook, I want the
macro
to go to the OTHER workbook.

Below, are some lines from my macro. I'm in one workbook, I copy some
cells, then I open a new workbook (below, called Book6), paste the data
into
the new workbook. Then, I add some sheets to the new workbook and rename
'em. Then, I want to go back to the other workbook (called "Investment
Quotation Install.xls" below) and copy-and-paste some more stuff.

The problem is that I will never know the name of either workbook. So, I
don't want to refer to them as "Investment Quotation Install.xls" or as
"Book6." (That just happens to be their name in my example.) I just want
to
say "go to the other workbook."

I assume that there are keystrokes that will switch back-and-forth between
the two, open workbooks (like Alt-W, 2) but the SendKeys commmand never
works
(for me, anyway) in a macro.

Any ideas?


Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("Sheet1").Select
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Expenses"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Installation"
Sheets("Installation").Select
Sheets.Add
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Hours"
Sheets("Installation").Select
Sheets("Installation").Move Befo=Sheets(1)

Windows("Investment Quotation Install.xls").Activate
Sheets("Expenses").Select
Range("A1:J4").Select
Selection.Copy
Windows("Book6").Activate
Sheets("Expenses").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A5").Select

(Truth be told, what I'm actually trying to do is to create a new workbook
without any macros. So, I'm copying the data from the one workbook - the
one
with the macros - to a new workbook (that won't have any macros) that will
have the data (values) from the first workbook. But I think it's
impossible
- or very difficult - to delete all the macros from my original workbook.
I
have about 50 of 'em and there's just no easy way to do it via a macro.)

Whew. Thanks in advance.