 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Excel VB Macro
Hello all: I have created macro in Excel that will essentially open a new workbook, and copy certain cells off of certain pages to the new workbook. At the end, it gives the user a chance to save it. The problem is that as it references the different workbooks (the new one just opened, and the source workbook), it will have problems if the filename of the source file is changed (because I have it as a static name), and the new workbook name differs depending on how long/many workbooks are opened (i.e. book1, book12, book14, etc.). I would like to assign the source workbook a name via a variable, and do the same with the exported workbook. Can you show me how to do this? I am quite an amateur when it comes to VB macros, so if you could give me a little detail, or even examples I would appreciate it. I would need to know how to define the variable based on the different file names, AND how to incoporate it in a line such as this: Windows("MY WINDOWS SPREADSHEET.xls").Activate Jon The code you give is sparse, but the idea is to assign the opened workbook to a 'workbook' variable, than when you refer to that variable you will always be referring to that workbook, so Sub ReferenceWorkbook() Dim NewWb as Workbook set NewWb=workbooks.add 'Now NewWb refers to the workbook you just added Windows(NewWb.Name).activate End Sub  SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS web: www.nickhodge.co.uk blog (non tech): www.nickhodge.co.uk/blog > Hello all: > [quoted text clipped - 17 lines] > > Windows("MY WINDOWS SPREADSHEET.xls").Activate Hello. Thanks for the help. I think this is defining the variable for the new opened book. How would I define the variable for the currently opened book (the source book)? Also, is it necessary to do that Sub in the middle of my code (would be a sub in a sub), since I am already running a macro? This would obviously apply to the End Sub too. Thanks > Jon > [quoted text clipped - 30 lines] >> >> Windows("MY WINDOWS SPREADSHEET.xls").Activate Don't use wb names, use wb objects as Nick showed you Set thisWB = Activeworkbook Set newWB = Workbooks.Add and when you want to refer to the original workbook, use thiswB, such as thisWB.Worksheets(1).Activate ThisWB.Close as examples and the new workbook, use newWB, such as thisWB.Worksheets(1).Range("A1:A10").Copy _ newWB.Worksheets("Master").Range("A1") Just use this within your existing code SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hello. > [quoted text clipped - 41 lines] >>> >>> Windows("MY WINDOWS SPREADSHEET.xls").Activate Bob showed you how to use a variable for the activeworkbook. But if you want to refer to the workbook with the code, you can use ThisWorkbook. Thisworkbook.worksheets("sheet1").range("a1").value = "hi there" > Hello. > [quoted text clipped - 51 lines] > >> > >> Windows("MY WINDOWS SPREADSHEET.xls").Activate SignatureDave Peterson
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.