 |
 | 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 / June 2007Macro for EACH workbook-->help modify to ONE macro for ALL workbooks?
Below is my macro that works 'perfectly', cross-platform on both Windows Excel97 and Mac OS 9, Excel 98, except... My problem is that I am using '6 edited individual macros', rather than just 'a single macro' for 6 workbooks that call the macro. How can I have my macro set a file name in the macro to that of the workbook's name that is calling the macro??? I currently run an 'edited copy' of my macro (see below), from within 6 different workbooks by clicking a button in the open workbook The macro finds and opens a specific workbook named "a_DK.xls" in the current directory and copies a named range "QuoteArea", then re-activates the workbook that called the macro (e.g."Ben.xls"), pastes the data, and then finally closes "a_DK.xls", (the data-source workbook). My problem is that in each of 6 copies of my macro, I've had to change/customize a 'file name' in the macro to that of the 'file name of the current active workbook's name' which is calling the macro. See the 5th line: *_Windows("Ben.xls") *, [in my macro below]... I've had to customize/change the file name "Ben.xls" to the name of the workbook that I call the macro from. (I've had to create a seperate macro for each of my 6 workbooks). Sub QuoteCopy_Ben() Application.ScreenUpdating = False mydir = ActiveWorkbook.Path Workbooks.Open FileName:=mydir & Application.PathSeparator & "a_DK.xls" Range("QuoteArea").Copy Windows("Ben.xls").Activate ' <--NEED TO EDIT THE WORKBOOK NAME HERE Range("QuoteDate").Select ActiveSheet.Paste Range("QuoteDate").Select Windows("a_DSK.xls").Activate Application.CutCopyMode = False Range("dkquotedate").Select Application.ScreenUpdating = True ActiveWindow.Close End Sub Thanks for loooking. -Dennis  SignatureDennis Kessler http://www.denniskessler.com/acupuncture Maybe... Option Explicit Sub QuoteCopy_Ben() Dim DKWkbk as workbook Dim ActSheet as worksheet Dim myDir As String Application.ScreenUpdating = False Set ActSheet = activesheet mydir = ActiveWorkbook.Path set dkwkbk = Workbooks.Open _ (FileName:=mydir & Application.PathSeparator & "a_DK.xls") 'is Quotearea on the first worksheet in a_DK.xls? 'and is Quotedate on the activesheet? dkwkbk.worksheets(1).range("quotearea").copy _ destination:=actsheet.range("QuoteDate") Application.CutCopyMode = False dkwkbk.close savechanges:=false Application.ScreenUpdating = True End Sub (Untested, watch for typos.) > Below is my macro that works 'perfectly', cross-platform on both Windows > Excel97 and Mac OS 9, Excel 98, except... [quoted text clipped - 46 lines] > Dennis Kessler > http://www.denniskessler.com/acupuncture SignatureDave Peterson Dave, two things... 1) what does "Option Explicit" mean and where does it go? 2) I think you wrote your macro to do something with my data source file ("a_DK.xls"), but this is not the problem, is it? I may be misunderstanding what you did. I thought I just needed to be able to set or define, for example ("Ben.xls"), which is the open workbook's name from which I start my macro.??? Thanks. -Dennis -------------------------------------- > Maybe... > [quoted text clipped - 76 lines] > > Dennis Kessler > > http://www.denniskessler.com/acupuncture SignatureDennis Kessler http://www.denniskessler.com/acupuncture It works of the PC! I haven't tested the Mac yet. Whoa! -Dennis SignatureDennis Kessler http://www.denniskessler.com/acupuncture > Maybe... > [quoted text clipped - 76 lines] > > Dennis Kessler > > http://www.denniskessler.com/acupuncture From the VBA Help Option Explicit Statement Used at module level to force explicit declaration of all variables in that module. Syntax Option Explicit Remarks If used, the Option Explicit statement must appear in a module before any procedures. When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time. If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement. Note Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope of the variable is not clear. > It works of the PC! > [quoted text clipped - 84 lines] > > > Dennis Kessler > > > http://www.denniskessler.com/acupuncture Am I doing something wrong here? I tried including the line... Option Explicit after the line Sub QuoteCopy_Ben() and the macro did not run. I got Compile error, and Option Explicit was highlighted in the VBA Editor. When I included Option Explicit before the line Sub QuoteCopy_Ben(), it looked to me, that it had nothing to do with the Sub QuoteCopy_Ben() module. What's the deal here? ;) -Dennis SignatureDennis Kessler http://www.denniskessler.com/acupuncture > From the VBA Help > [quoted text clipped - 118 lines] > > > > Dennis Kessler > > > > http://www.denniskessler.com/acupuncture Option explict goes at the top of the module--before any Sub or Function. It's not part of your macro. It's your way of telling excel/VBA that you want it to check to make sure all the variables that you use in that module are declared. > Am I doing something wrong here? > [quoted text clipped - 144 lines] > > > > > Dennis Kessler > > > > > http://www.denniskessler.com/acupuncture SignatureDave Peterson Oh! ...Since there was a separation line in the VBA editor above the line Sub QuoteCopy_Ben(), it looked to me, like it had nothing to do with the Sub QuoteCopy_Ben() macro. That's why I put Option Explicit after Sub (). In the module that contains Sub QuoteCopy_Ben(), there are other macros. Does the Option Explicit line affect the other macros that are above and below the Sub QuoteCopy_Ben() macro in the same module? -Dennis > Option explict goes at the top of the module--before any Sub or Function. > [quoted text clipped - 161 lines] > > > > > > Dennis Kessler > > > > > > http://www.denniskessler.com/acupuncture Yep. "Option Explicit" forces you to make sure any variable you use in any procedure (Sub or Function) has been declared. If you miss declaring even a single variable in that module, then when you try to run the procedure with the undeclared variable, it won't run. > Oh! ...Since there was a separation line in the VBA editor above the line > Sub QuoteCopy_Ben(), it looked to me, like it had nothing to do with the [quoted text clipped - 171 lines] > > > > > > > Dennis Kessler > > > > > > > http://www.denniskessler.com/acupuncture SignatureDave Peterson Woohoo!!! Option explicit will force you to declare any variables that you use. Without that, your code may have run, but may not have done what you wanted. (Undeclared variables could be treated as 0, "", ...) And it'll help you find typos: xlvalues (EX-ELL-values) compared with x1value (EX-One-values) This is the line that I think you had concerns about: Set ActSheet = activesheet So if you want your macro to work, you have to make sure you start your macro from the worksheet that gets the data (that owns the range named "QuoteDate"). If you don't start there, the code will fail. > It works of the PC! > [quoted text clipped - 88 lines] > > > Dennis Kessler > > > http://www.denniskessler.com/acupuncture SignatureDave Peterson Saved from a previous post. I do it for a much more selfish reason. If I add "Option Explicit" to the top of a module (or have the VBE do it for me via tools|options|Editor tab|check require variable declaration), I know that most of my typos will stop my code from compiling. Then I don't have to spend minutes/hours looking at code like this: ctr1 = ctrl + 1 (One is ctr-one and one is ctr-ell) trying to find why my counter isn't incrementing. And if I declare my variables nicely: Dim wks as worksheet not dim wks as object and not dim wks as variant I get to use the VBE's intellisense. If I use "dim wks as worksheet", then I can type: wks. (including the dot) and the VBE will pop up a list of all the properties and methods that I can use. It saves time coding (for me anyway). And one final selfish reason. If I use a variable like: Dim ThisIsACounterOfValidResponses as Long I can type Thisis and hit ctrl-space and the VBE will either complete the variable name or give me a list of things that start with those characters. And by using a combination of upper and lower case letters in my variables, the VBE will match the case found in the declaration statement. > Woohoo!!! > [quoted text clipped - 110 lines] > > Dave Peterson SignatureDave Peterson Woohoo!!! is Right!!! Your routine runs on both my old Mac OS 9, and in Excel 97 Windows 98. Initially I just copied your code, including the line Option explicit. When I pasted that code into the VB Editor, a separation line was created above the line Sub QuoteCopy_Ben(). The macro worked. I then went back a put a single quote (') before "Option explicit", and the the macro worked once again. I do/did run the macro while I'm looking at the worksheet that does contain the range named "QuoteDate" on the sheet. I did need to add back one line to your macro to make it select the active cell that I like to keep selected when I close each workbook, i.e., Range("QuoteDate").Select I added that line back just above the line Application.ScreenUpdating = True This is really teriffc! Thank you!!! I will now need to go back and study your macro. OTH: Your explanation of "Option explicit" went right over my head, including the part about typos. ...I gotta go have breakfast. ;) Thank you! -Dennis SignatureDennis Kessler http://www.denniskessler.com/acupuncture > Woohoo!!! > [quoted text clipped - 115 lines] > > > > Dennis Kessler > > > > http://www.denniskessler.com/acupuncture Glad it worked for you. Someday, you'll know what typos are <vbg>. > Woohoo!!! is Right!!! > [quoted text clipped - 153 lines] > > > > > Dennis Kessler > > > > > http://www.denniskessler.com/acupuncture SignatureDave Peterson Dave, I tried to use your instruction (just below), but I couldn't figure out how to implement it. Would you show me how I can use this line in my original macro? "This is the line that I think you had concerns about: Set ActSheet = activesheet" Thanks for your help and for the lessons! -Dennis SignatureDennis Kessler http://www.denniskessler.com/acupuncture > Woohoo!!! > [quoted text clipped - 107 lines] > > > > Dennis Kessler > > > > http://www.denniskessler.com/acupuncture If you're opening other workbooks or switching to other worksheets (in the same workbook or a different workbook), it's sometimes nice to know where you started. Set ActSheet = ActiveSheet is the code that sets a variable that keeps track of where you were. In your code, I guessed that you wanted everything done to whatever sheet was active when you started the macro. If you look back at the suggested code, you'll see that before anything really happens, there's that line that sets that variable. Later in the code, that same variable is used to qualify what ranges should be used--the range named QuoteDate on the worksheet that was active when you started the macro. > Dave, > [quoted text clipped - 124 lines] > > > > > Dennis Kessler > > > > > http://www.denniskessler.com/acupuncture SignatureDave Peterson Dave, I followed your explanation (just below), and that was what I assumed the Set line was supposed to work, but I still couldn't get 'ActSheet' to work in my original macro. Here's what I tried... I used your set line... Set ActSheet = ActiveSheet as one of the frist few lines in my original macro, and then I inserted ("ActSheet") in place of ("Ben.xls") as in the following line... Windows("Ben.xls").Activate What am I missing or misunderstanding? -Dennis > If you're opening other workbooks or switching to other worksheets (in the > same workbook or a different workbook), it's sometimes nice to know where [quoted text clipped - 145 lines] > > > > > > Dennis Kessler > > > > > > http://www.denniskessler.com/acupuncture Is the active sheet a worksheet or a chart sheet or what????? And if you changed the code, I think it's time to share what you're currently running. > Dave, > [quoted text clipped - 164 lines] > > > > > > > Dennis Kessler > > > > > > > http://www.denniskessler.com/acupuncture SignatureDave Peterson Dave, 1) The active sheet is a worksheet, yes!!!! :) I see that I have edited the 'name' on the worksheet tab, in each of my 6 workbooks, if that makes any difference. And there is a named range on each of the 6 worksheet/workbooks named: "QuoteArea". I have a button on each sheet that I click on, to run the macro. I have 6 workbooks that I look at, one at a time, and I manually click a button on the sheet in the workbook that I currently have open. This updates my data, on demand. The button runs my macro (that is shown at the bottom of this page). Now it runs your macro. ;) 2) What do you mean, 'share what you're currently running'? I am now using the marco that you wrote, and it works! (Thank your very much!)... ...But, I'm trying to learn by doing, how I could have made my original macro, (shown at the very bottom of this page), work for each of my 6 workbooks. What you see, (waaaay below), is what I was running. :) I was originally thinking that some small tweak to my macro that would 'Set' or mark the name of the worksheet/workbook that I run the macro from, would have allowed me to use a single macro routine, rather than 6 macros, (one for each of my worksheet/workbooks). I'd still like to understand how to do this. :) -Dennis > Is the active sheet a worksheet or a chart sheet or what????? > [quoted text clipped - 170 lines] > > > > > > > > > > > > > > > > -Dennis SignatureDennis Kessler http://www.denniskessler.com/acupuncture Dave, I got ActSheet to work... 1) I added the line near the top of the macro... Set ActSheet = ActiveSheet 2) Then I changed the line... Windows ("Ben.xls").activate to... ActSheet.Activate and now my original macro works for each of my 6 workbooks! Q) Why did I NOT NEED to also have this line?... Dim ActSheet as Worksheet -Dennis > Is the active sheet a worksheet or a chart sheet or what????? > > And if you changed the code, I think it's time to share what you're currently > running. Glad you got it working, but I don't understand your comments. If you look at the original code that I suggested, there was a line that declared ActSheet. So I think that you made a change (however minor) to that suggested code (or changed your own code???). That's why I asked to see the code that you were currently running. > Dave, > [quoted text clipped - 22 lines] > > And if you changed the code, I think it's time to share what you're currently > > running. SignatureDave Peterson Dave, Below are three WORKING versions of the data copy macro... 1. The first is the first that you posted, i.e., a complete re-do of mine; --> ONE macro for ALL workbooks. 2. The second is the "Set ActSheet" modification of my original; ONE macro for ALL workbooks. Q). In this version (#2 below), why did I not need the line?... Dim ActSheet As Worksheet 3. The third is my original, which works only for ONE "named" workbook. I did see that in my very first post, there was a TYPO in the name of my source data file. All three of these macros work cross platform, (Mac OS 9, and Windows 98se Excel 97)... 1... Sub QuoteCopy_ActiveSheet_to() ' <--[Dave's ONE macro for ALL] Dim DKWkbk As Workbook Dim ActSheet As Worksheet Dim myDir As String Application.ScreenUpdating = False Set ActSheet = ActiveSheet myDir = ActiveWorkbook.Path Set DKWkbk = Workbooks.Open _ (FileName:=myDir & Application.PathSeparator & "a_DK.xls") DKWkbk.Worksheets(1).Range("quotearea").Copy _ Destination:=ActSheet.Range("QuoteDate") Application.CutCopyMode = False DKWkbk.Close savechanges:=False Range("QuoteDate").Select Application.ScreenUpdating = True End Sub 2... Sub QuoteCopy_BenDaveSET() ' <--[Dave's "Set ActSheet", ONE for ALL] Application.ScreenUpdating = False Set ActSheet = ActiveSheet myDir = ActiveWorkbook.Path Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls" Range("QuoteArea").Copy ActSheet.Activate ' <--[This line replaces the line below] ' Windows("Ben.xls").Activate <--[This line is COMMENTED OUT] Range("QuoteDate").Select ActiveSheet.Paste Range("QuoteDate").Select Windows("a_DK.xls").Activate Application.CutCopyMode = False Range("dkquotedate").Select Application.ScreenUpdating = True ActiveWindow.Close End Sub 3... Sub QuoteCopy_Ben() ' <--[My Original Macro, ONE for EACH workbook] Application.ScreenUpdating = False myDir = ActiveWorkbook.Path Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls" Range("QuoteArea").Copy Windows("Ben.xls").Activate Range("QuoteDate").Select ActiveSheet.Paste Range("QuoteDate").Select Windows("a_DK.xls").Activate Application.CutCopyMode = False Range("dkquotedate").Select Application.ScreenUpdating = True ActiveWindow.Close End Sub > Glad you got it working, but I don't understand your comments. > [quoted text clipped - 29 lines] > > > And if you changed the code, I think it's time to share what you're > > > currently running. For #2. Technically, you don't need to declare your variables. If you don't put "Option Explicit" at the top of the module that contains that procedure, you don't need to declare any variable. I think that this is a very, very bad idea. I think each variable should be declared--and declared as the correct type ("as range", "as workbook", "as worksheet", "as long", "as variant"). But there's no law that says you have to do this. But if you post back with a question about why your procedure doesn't work as expected and it's becaused of a typo that would have been caught if you had used "option Explicit", then expect some grief <vbg>. Personally, I don't want to spend minutes/hours debugging these kinds of errors and I like the intellisense and autocomplete that I get with declared variables. === This was the same reason (lack of "option Explicit") that you didn't need the "dim myDir as string" in the 2nd procedure, too. But if you would have mistyped this line: Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls" as Workbooks.Open FileName:=miDir & Application.PathSeparator & "a_DK.xls" it might have taken a little time to notice that typo ======= If you decide that you want to declare those variables (and I think you should), then you can have excel help you. Inside the VBE: Tools|Options|Editor tab|check "require variable declaration" Then each new module that you create will have "option explicit" added to the top--you don't need to type it. ===== ps. If you're looking through any of my posts, you'll see that I try to include that "option explicit" on any procedure I post. It's my passive-aggressive way of trying to make people do what I want <vvbg>. pps. I would also modify that first macro. Instead of this line: Range("QuoteDate").Select I'd use: ActSheet.Select actsheet.Range("QuoteDate").Select I don't like to just rely on excel to determine the activesheet. It may be overkill in 99.99999% of the cases, but sometimes.... > Dave, > [quoted text clipped - 106 lines] > > > > And if you changed the code, I think it's time to share what you're > > > > currently running. SignatureDave Peterson Dave, Thank you very much for your help, your patience, and for the excellent lessons! Very much appreciated! I am curious why the following statement needed () around the file's path?... Set DKWkbk = Workbooks.Open _ (FileName:=myDir & Application.PathSeparator & "a_DK.xls") And in another version of the macro, the ()'s were not needed?... myDir = ActiveWorkbook.Path Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls" Thank you, once again. ;) -Dennis SignatureDennis Kessler http://www.denniskessler.com/acupuncture > For #2. Technically, you don't need to declare your variables. > [quoted text clipped - 163 lines] > > > > > And if you changed the code, I think it's time to share what you're > > > > > currently running. Notice that the top version uses a "Set" statement. And I need the parens there. The bottom version just calls workbooks.open and doesn't require them. > Dave, > [quoted text clipped - 188 lines] > > > > > > And if you changed the code, I think it's time to share what you're > > > > > > currently running. 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.