microsoft.public.excel.programming

Jon Peltier

12/18/2006 10:01:00 PM

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://Pelti...
_______


"crimsonkng" <crimsonkng@discussions.microsoft.com> wrote in message
news:C88F32DC-BCFF-436C-8427-0360DB055C0F@microsoft.com...
>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 Before:=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.
>
>