Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro call, one workbook to another | Excel Programming | |||
How to call macro from other workbook | Excel Programming | |||
Call Macro from another WorkBook? | Excel Programming | |||
How can I call a macro in another workbook? | Excel Programming | |||
Call a macro in other workbook | Excel Programming |