Copy Module To External Workbook
Mar 26, 2009
I have been trying for some days to copy a module to an external workbook with VBA. I have about 40 workbooks that have the same format. although I developed these sheets. I am not the person who uses them. I have to on occasion alter the code for some reason, its getting tiresome.
I don't think I can use the personal workbook method for this as it is not me using the workbooks. I don't even know some of the people that use them. This courses me problems because of the number of workbooks. I want to be able to maintain the code by having to write it once and propagate it through the necessary files via the VBA.
I have code from C Pearson that copies a module to another workbook.
I have posted this here just in case. Most threads on this subject just direct you here any way....
View 9 Replies
ADVERTISEMENT
May 6, 2007
I have a workbook (Program_1.xls) which references another workbook (Program_2.xls). I want to close Program_1.xls using code in Program_2.xls.
When the Program_1.xls is closed, the code stops.
Program_1.xls code
Sub Program_1()
MsgBox "This is program #1"
Application.Run "Program2.xls!Program_2"
End Sub
Program_2.xls code
Sub Program_2()
Source = "C:Documents and SettingsCohenMy Documentsprogram3.xls"
target = "C:Documents and SettingsCohenMy Documentsprogram1.xls"
Workbooks("Program1.xls").Close savechanges:=False
' ---> code halts here
MsgBox "This is program #2"
FileCopy Source, target
End Sub
How do I get the final 2 lines of code to execute?
View 9 Replies
View Related
Jul 14, 2006
Is there anyway to copy a module from one workbook to another using a macro?
View 6 Replies
View Related
Jun 21, 2013
Want to Copy a Module(s) to another workbook in a different path and also password protect it at the same time using VBA.
I need to copy some Module(s) from One WorkBook to another using VBA..
I have used the following code to good effect, however my TargetWb is not at the same path, so I need some modification in this code if that's possible and also this VBA Module which is Copied and Pasted in the Target WOrkbook needs to be Password Protected so that it cannot be tampered.
The code which I'm using currently is:
Code:
Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _
[Code]...
Code:
Sub Try()
Call CopyModule(Activeworkbook, "Module1", TargetWb)
on this to pass the TargetWb as a variable which can be generated in the code used for generating the File using a Macro..
End Sub
In the above code The SourceWB would be the ActiveWorkbook and the Target Workbook is the file created using another Macro from several sheets of the Active Workbook and its created in a folder with a specific name and therefore the Path is one more level inside than the Active Workbook.
if the Transferred Module can be Passworrd Protected and Locked for editing..for the end user.
View 9 Replies
View Related
Aug 26, 2009
If possible, Can a module be copied into a new workbook from the master workbook. When the one I have does not copy the modules when creating a new workbook. If so it would make my life a bit easier.
View 9 Replies
View Related
Jan 24, 2013
Let's say i have 2 Modules on my VBA forms, is it possible to Copy all the Codes in Module 1 and Paste it to Module 2 by using a Command Button?
View 1 Replies
View Related
Mar 20, 2007
I will shortly be running a project over a twenty four hour period, where a group of people will be inputting data into a workbook (one worksheet per hour), and what I would like to know is if it is possible, to input some coding into a thisWorkbook module to action a save on the workbook every time an entry is placed in column A (of each worksheet) which is divisable by 10 (a10, a20, a30 etc).
View 2 Replies
View Related
Jan 18, 2014
I am creating a File/Workbook using MACRO, however i need to ensure that one of the Modules in the created workbook remains protected and hidden...
Since there are lot of these files which are created, I would be interested in protecting them via a macro..
View 4 Replies
View Related
Mar 15, 2007
I have multi- sheet workbook that opens to a specific ws and to a specific cell.
Application.Goto Reference:=Worksheets("Menu").Range("A1"), _
One of the several ws is a form; when the user saves it, all other ws are deleted and the wb is saved as a new file name. (Need to retain other macros for future functionality, seems easiest to just delete the unneeded sheets).
Is their a way to modify the sheet reference from "menu" to "sheet-name" (will be a constant). Because it is likely that additional sheets will be added in the future to this new wb, I'd like for it to open to a specific ws.
As my VBA skills are "in development" (basement level, just now), please provide a complete answer (assuming this can be done).
View 9 Replies
View Related
Feb 5, 2008
I have a list of objects in column A
I want to select that list or part of it then run a VB module that references each item in the selection and compares it to a master list in a separate workbook that contains both the object and an additional value, and display that additional value in column B of the original workbook against each object in column A
View 9 Replies
View Related
Mar 13, 2012
I have a workbook which I regularly upload that cannot have macros within it; however, to update and process the data, I must utilize VBA Code.
I tried to work around this by storing my macros in a module in another workbook. My process is to have the VBA Code Module from the other workbook open and have the active workbook be the one I want to change, then I run the VBA Code from the other workbook.
This used to work. However, I am recently getting problems with the Code not running correctly. Sometimes it only runs the code if the affected cells are not hidden; other times it will not run the code at all!
View 4 Replies
View Related
May 15, 2008
I want to write some code that runs everytime the workbook is initialized. I found out that the code should be placed in the following "Private Sub":
Private Sub Workbook_Open()
'My code
End Sub
I know that this code should be placed in the Private Module of the Workbook! I just can not find where is the Private Module of the Workbook in Excel 2007... =/
View 3 Replies
View Related
Jul 31, 2014
I have a document (unfortunately I cannot attach it) in Excel 365 that each time I open it I get the usual prompt that it is calling an external link. As much as i'd love to hide the popup I need to find the link to solve to problem but I'm at a loss.
Simply breaking the links doesn't work for some reason, so I've tried updating the source to reflect the current file. No luck there.
I've searched the workbook for the name of the link in question, searching for all formula for name itself as well as variants of "[" and ".xlms".
I don't see anything in the name manager referencing that external file.
I don't recall if I copied from that particular document, I may have as it was a duplicate file. I tried looking over the cells I believe I copied from it but didn't see the reference.
I've installed Kutools and Bill Manville (MS MVP) FindLink Tool. Both of which says there is no external link.
I've looked at the compatibility checker which basically just says, yes there is an external link but give no insight on how to address.
I've tried a couple of macros, some of which crashed excel, some of which simply said that there was an external link but not how to find it. In my frustration I forgot which one that was but I'm about to try that approach again and look for that code.
external links.png
View 5 Replies
View Related
Nov 23, 2009
I want to move the workbook I have created to a different folder on the same drive. The workbook is pretty big and has loads of links to other workbooks. If I move it will all of these links fail and if so will I have to manually reset them all? I guess I am asking if there is specific method of moving workbooks which will automatically change the links to the cope with the new location?
View 3 Replies
View Related
Dec 14, 2009
I'm having trouble working this around, i use the following code in one of my Workbook sheets.
View 10 Replies
View Related
May 2, 2008
Is it possible for the INDIRECT function to be tweaked so it updates even when the external workbook is closed?
View 13 Replies
View Related
Mar 16, 2008
I need to create a formula or code which will return the value of cell d16 from an external and unopened workbook, where that external workbook's file name, directory and sheet names are provided within cells (able to be updated) of the current workbook. I have downloaded the add-in for INDIRECT.EXT but either being unfamiliar with this function or that I'm barking up the wrong tree all I'm getting is #REF!
And to add to my problem, I was hoping to use this formula / code within an excel table embedded in a word document.
View 9 Replies
View Related
Jan 22, 2007
Once I open a new workbook, how do I make it the active workbook to have a function make changes to it? Problem I am having is even after it is open, the function will only make changes to the workbook containing the Macro (Thisworkbook)....
View 9 Replies
View Related
Jul 9, 2013
How to delete another excel private module of the Workbook Object (ThisWorkbook) VBA codes of the workshook event.
This code to run from my PERSONAL.XLSB
Sub Macro1()
Windows("Book1.xls").Activate
ActiveWorkbook.Vbproject
Don't know what other codes I need to put after this
End Sub
This code to be deleted via Macro1 above code
Private Sub Workbook_Open()
Call CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub
View 2 Replies
View Related
May 30, 2007
I've used the following code to export macros from one workbook to another but I was wondering how you export macros from the ' Sheet' code module to the 'Sheet' code module of another workbook.
Sub Import_Macro()
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
strRet = .Lines(1, .CountOfLines)
End With
Set wkb = Workbooks("Test Workbook.xls")
wkb.VBProject.VBComponents. Import ("G:SCSSCSALLReportsVB MacrosGeneral.bas")
wkb.VBProject.VBComponents.Import ("G:SCSSCSALLReportsVB MacrosMJ Selections.bas")
With wkb.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines '//Delete Codes already wrriten
.InsertLines 1, strRet '// Write Code
End With
End Sub
View 8 Replies
View Related
May 16, 2013
Is there a way to specify the filename for an external workbook that can be referenced throughout the spreadsheet? In my case it would be better if I could change the single reference instead of having to change it throughout the entire spreadsheet everytime I needed to use data from a different file.
View 2 Replies
View Related
May 21, 2014
I am new in VBA, but I have a short procedure to break all external links in the currently active workbook.Is there any option to firstly update all external links and only then break them?
Sub BreakLinks()
Dim Links As Variant
Dim i As Integer
With ActiveWorkbook
Links = .LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
[Code]....
View 5 Replies
View Related
Oct 26, 2008
I'm trying to summarize some data from an external workbook. The problem is that the worksheet names there are months (Jan2009, Feb2009 etc.) and the sheets rotate (change position) with time. In the summarizing workbook I would like to use a function to refer to (for example) cell A1 on the third worksheet, regardless of the current name of that worksheet. So I might have a cell in the summary workbook that looks something like "=CoolFunction(ExternalWB.xls, 3, A1)"
View 3 Replies
View Related
Jun 12, 2009
I want to copy a named range from an external workbook. Currently I am using some VBA copied from a recorded macro that first opend the 2nd workbook and then selects the named range, changes back to the original workbook and drops the copied range into the active workbook.
I would like to find a way to get around having to open the reference workbook, and instead simply point to the proper workbook name and named range.
View 3 Replies
View Related
Jul 22, 2009
I'm trying to create a workbook that opens the windows explorer and let me select a workbook, then in one worksheet merge all the active sheets present in one.
View 2 Replies
View Related
Sep 16, 2009
I have a list that show file names and when the necessary works on these files were completed, but the powers that be want more. I have been asked to add two more columns, one showing when the file was last opened and the other when it was last modified.
Is it possible to do this through VBA without having to open the files that are listed? If so I will run the macro when the workbook is first opened.
View 10 Replies
View Related
Nov 24, 2011
I've got a field where I enter the current month, from this I derive a file path which is stored in A4.
ie: A1 = '01/11/2011' (UK Date format)
A4 = '\myserverdirectory20111111MyFile.xls'
What I'm trying to achieve is to then have a link in cell A6 that will link to the file in A4. I can specify my cell reference as this will never change, just the file path.
So, if I changed A1 to '01/12/2011' my A4 changes to '\myserverdirectory20111211MyFile.xls'
I'm trying to set A6 to something like ="&$A$A4&"!B1 to get the value of B1 on my referenced sheet.
View 1 Replies
View Related
May 2, 2009
I have 3 sheets: 1: master entry sheet that fills data in 5 different excel docs. Then there is another doc that aggregates data from the 5 and presents totals on the data. I am trying to get the end result without having to have 7 different excel files open, but when I enter into the master entry sheet, the data doesn't seem to be "pushed" up to the 5 different docs until each one is opened up. Obviously, the master aggregator is not updated since the 5 don't have the new data. When the files are all opened at the same time, the data flows perfectly, I'm just trying to find a way to avoid having to open the 5 docs in the middle of the process.
View 9 Replies
View Related
Jun 19, 2006
when i run this code under a button on a sheet it works fine. but i would like to run when the workbook opens (but i get an error message
On Error Resume Next
Application. ScreenUpdating = False
Dim wb As Workbook
Dim ws As Worksheet
'create new work order number...
Set wb = Application.Workbooks.Open("c: empex_ExternalOrderNumber.xls")
Set ws = Worksheets("NumberIncrement")
'get new WO number from numberincrement.xls
If Err.Number <> 0 Then
MsgBox Err.Description & "...help"
Else
mynewnumber = ws. Range("b1").Value
Range("g5").Value = mynewnumber
wb.Close
End If
Application.ScreenUpdating = True
View 2 Replies
View Related
Apr 21, 2008
I am designing a spreadsheet at work, and need a little assitance. I can't find the answer I need online or here either. Here's the problem: I want to be able to enter data (A 5 digit job number, 80227 for example) into a cell, and then multiple other cells would auto fill with data from outside worksheets. The worksheets are in a seperate folder on my server and named under the same name as the project. (So if I entered 80227 as the data in A1, I would want A2, A3 and A4 to open spreadsheet 80227.xls on the server, locate the referenced cell/s, and insert the info from 80227 into them.)
I can easily reference the cells alone and have them input the data, but this is extremely time consuming since this spreadsheet has all the current jobs our company is working on. I currently am running a macro/VBA that automatically updates all linked spreadsheets without them having to be open, so that is not an issue.
View 3 Replies
View Related
