Open A Workbook And Run 1 Of 4 Macros Based On 3 Cell Values

Jul 30, 2009

I'd like to automatically run 1 of 4 macros depending on some criteria. Every workbook created has, unfortunately, the same worksheet name, so that leaves the only differences between the 4 possible loaded workbooks in the cells area and even those can be similiar in many ways. But....I found some criteria to separate all 4 workbooks...Here they are...

run macro 1 if this

1. Worksheet name says "Screen"
2. Cell H1 has the word "Lead"

run macro 2 if this

1. Worksheet name says "Screen"
2. Cell B1 has the word "records" somewhere in the cell
3. Cell H1 has the word "Lead"

run macro 3 if this

1. Worksheet name says "Screen"
2. Cell B1 has the word "records" somewhere in the cell
3. Cell N2 has the word "Delivered"

run macro 4 if this

1. Worksheet name says "Screen"
2. Cell B1 has the word "records" somewhere in the cell
3. Cell N2 has the word "Bevel"

I don't know if this can be done, but if so, that would be fantastic! I'm thinking that the macro would have to be "global" and in the user's personal workbook?

View 12 Replies


Macro To Copy Folders To Path Of Open Workbook Based On Cell Values

Aug 3, 2014

I have an Excel sheet that contains a few thousand folder paths in the first column. The first few cells in the column look similar to below.

C:UsersNameDesktopFolder Copy12010360
C:UsersNameDesktopFolder Copy12010361
C:UsersNameDesktopFolder Copy12010362
C:UsersNameDesktopFolder Copy12010363
C:UsersNameDesktopFolder Copy12010364
C:UsersNameDesktopFolder Copy12010365

I am trying to create a macro that will copy any folders that exist in any of the paths listed in the first column. The folders should be copied to the path of the open workbook containing the macro. Below is the macro I have currently, much of which was taken from information I found in this thread [URL]....


Sub wrapper3()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("Air").Cells(x, 1) <> ""
v = InStrRev(Sheets("Air").Cells(x, 1), "")
dest = ActiveWorkbook.Path & Mid(Sheets("Air").Cells(x, 1), v, 99)


This code seems to work fine if all of the folders exist to be copied. My problem is that some do not and it is creating a "Path Not Found" error for which I need a fix. If the folder doesn't exist at the path nothing should be copied and the next path can be evaluated.

View 2 Replies View Related

Don't Open Or Use Workbook Unless Macros Are Turned On?

Dec 9, 2008

Is there a way to prevent a workbook from opening unless macros are allowed?

View 2 Replies View Related

Workbook To Open Only If Macros Are Enabled

Mar 29, 2009

I have created an Excel Workbook and this Workbook has a VBA code that enforces an expiry date of the workbook (TimeBomb from So, if the expiry date has passed, the workbook will not open. But since it is VBA related, the expiry date will work only if one has enabled macros. If macros are disabled, the workbook will open regardless of the date.

So, is there a way for Excel to check if macros are enabled? and is there a way for Excel not to open the workbook, if the macros are disabled?

View 10 Replies View Related

Can't Open An Excel Workbook With Macros

Nov 2, 2008

My son just upgraded my computer from 2000 to xp - a new application on a new hard drive. in the process, Excel was upgraded from 97 SR-1 to 97 SR-2.

My checkbook is on excel, and I really need to open it! We just moved to a new location, with the typical problems with email, internet etc.

I can open workbooks without macros. But I cannot open any with macros.

I get this message:
"Could not register the Visual Basic environment."

When that window is closed, the next one says:
"An error occured when initializing the VBA libraries (126)"

View 9 Replies View Related

Open Workbook, Disable Macros

Apr 22, 2006

Is it possible to open a workbook via vba, but disable the macros? I know this sounds strange, but the workbook is coded to prevent users from saving it, and the only way to save it is to either open it with macros disabled or change the variable OKtoSave to true, but the variable is global only to the sheet and I can't find a way to change it through a macro written in my personal.xls workbook.

View 5 Replies View Related

Force Macros Enabled On Workbook Open?

Oct 28, 2013

I'm posting a workbook on our network that I want to enable users to go in and update. I have the workbook designed, with entry fields for the data they need to enter, and they then push "Submit" to submit the new data to a hidden tracking log within the workbook.

Many of the users will be complete Excel newbies, and although I haven't posted the workbook yet, I am trying to anticipate possible issues. The first that comes to mind is that the user opening the workbook may not have macros enabled in their version of Excel, and they will get a message, and can't perform the udpate. Is there a way to add a Workbook Open event that automatically enable macros in the workbook when they open it, even if it is for this workbook only?

View 5 Replies View Related

Open Workbook Based On Cell Name?

Jul 30, 2012

Ok, so I have some code that currently opens workbooks via directly calling them in the code via the normal open command. These workbooks belong to individual employees and this method works fine until a new person needs to be added to the process. I currently have to go into the code and add that person's workbook to the code.

Is there a way to instead pull a list of workbooks from say range D1 through D20 and open all workbooks that are listed in this range?

View 1 Replies View Related

Open Workbook Based On Cell Value

Jun 1, 2007

I am trying to make a spreadsheet that has a macro that will allow me/user to open a specific workbook in another folder based on a specific cell value in the active spread sheet. I.E. if cell B2 has a value of 7101010 the macro will open that .xls file when used or any other part number typed into B2.

View 9 Replies View Related

Open Workbook Where Named Cell Contain Name & Path, Copy Range And Paste Values

May 19, 2009

I am successfully opening a .csv file using a variable value stored in a named range in my Main file (the variable includes the directory and path).
I copy data from the .csv file to the Main file then I need to close the .csv file without saving but I want to do that by using the


command where "xxxx" is the namedrange in my Main file which stores the .csv filename (without the directory and path prefix).

I can use the


command but if I have another workbook open, this one closes instead of the .csv file I opened from the macro.

I realise this is probably very basic and I've searched the forums but can't find any identical postings.

View 5 Replies View Related

Open Multiple Workbooks Based On Cell Values And Copy And Paste Information?

Apr 22, 2014

I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.What I am trying to do is that;1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me? [URL]

View 5 Replies View Related

VBA To Delete Formulas, Names, & Macros Of Another Workbook, But To Keep Values

May 15, 2009

I want to delete names, formulas, macros of another workbook. For example, A.xls is my codes workbook. From this file, I want to delete the names, formulas, macros (but to keep the values & formatting in tact) of another workbook (there must be a prompt for which .xls file, the names etc. to be deleted). All files are in a same folder.

View 5 Replies View Related

Prevent/Allow Workbook Closing Based On Cell Values

Feb 15, 2010

I have a small macro that before closing the workbook will check a range ("C4:G24") on Sheet ("Data Checks"). It will then show a msgbox displaying each non "OK" result in the range. I want this to close the workbook if all cells in the range have the value "OK" and cancel the close if the msgbox appears. This is what I have so far...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim r As Range, txt As String
With Sheets("Data Checks")
'for each non "OK" result in range
For Each r In .Range("C4:G24")
If r.Text <> "OK" Then
txt = txt & r.Text & vbLf
End If
End With
If txt <> "OK" Then
MsgBox "Please check:" & vbLf & vbLf & txt, vbExclamation
Cancel = True
End If
End Sub

The problem I have is that although the msgbox displays the correct cell values if they are not "OK", it also appears and cancels the close if all cells in range DO have value "OK"???

View 2 Replies View Related

Pull Data From Closed Workbook Based On Cell Values

Apr 28, 2008

I want to pull data from closed workbook based on cell values of open workbook of column B and the source file name is on cell J1. Actually I save monthly files and opening balnce of current month should take vakues from previous month file.

Suppose current month is May 2008. Then Column Column D for May month shold take value from column G of April 2008. For simplicity the previous month’s name and thus source file name will be placed on cell J1.

The code should loop from column B of source file and current May 2008 file and should pull values for only those items which are in the current file in the Column B. Thus those products which are deleted or newly added item in the current item should not copied. Though for new item no name will be thre in the source file but for deleted items the item might be there in the source file but the code should ignore those value.

View 4 Replies View Related

Prompt Open Closed Workbook Then Copy Range And Paste Special Into Open Workbook

Apr 6, 2013

I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.

I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.

I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need

A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)

I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.

week 1 week 2 week 3 week 4


View 7 Replies View Related

VBA To Open Workbook And Sum Based On Criteria?

Mar 4, 2014

I have a lot of data that I want to put into another workbook so as to free up space and make the workbook efficient, however, I do not know how to open a workbook in VBA.

Once this workbook is open, I then want to sum the data based on set criteria.

View 7 Replies View Related

Open Workbook, Find Sheet That Contains Cell Value From Active Workbook

Jun 20, 2008

I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...

'Dim officen As Integer
'Dim thiswb As Workbook

officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"

RowCount = ActiveSheet.UsedRange.Rows.Count

View 8 Replies View Related

Open Workbook Based On Value Found In Range

Jun 11, 2008

I am trying to open a specific workbook, from several books on a shared drive, based on a value found in a range of cells.

For example, range A3:B3 will contain a number, and cmay also contain some text, but based on the number found in that range, I want to open or activate a particular workbook.

Let me know if further clarification is required.

View 12 Replies View Related

Make Workbook Open- Open Correct Month Workbook

Jul 8, 2006

I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook

open macro-

Private Sub Workbook_Open()
Dim dTime As Date
dTime = Time
If dTime >= TimeValue("9:30 PM") And _
dTime < TimeValue("9:40 PM") Then
End If
End Sub

This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?

View 9 Replies View Related

Run Macros Based Cell Value

Dec 12, 2007

I am trying to create a macro based on value in a cell. For example if J2 is greater than 1 then run macro A. The code I have is:

If j2 > 1 then
Call MacroA

The file imported as a text tab deliminated file and I believe that even though J2 might be 10.05, it does show up in the formula bar as just 10.05 not =10.05. I don't if this makes a difference. The J2 cell could either bland or values ranging from 1 to 100. If it is blank I want to run a different macro.

View 2 Replies View Related

How To Delete Another WorkBook Macros Using Macros

Jul 23, 2005

What is the commands or script for deleting a macro automatically using
another workbook macro.

View 9 Replies View Related

Open Workbook From Cell Value

Aug 31, 2009

say that in Sheet1 ( i renamed it to "Input Data") contain data in 2 cell:

Q5 = 08-AUG-2009
Q6 = 30

I want to use this cell to look up a folder name i.e: "Z:Shift ProdSouthCOMBINED LOGS 20098-AUG-200930" and use the code below, but didn't work.

View 2 Replies View Related

Open Workbook With Name In Cell

Jun 20, 2007

I would like to create a macro (in a destination spreadsheet) which will open up a (source) spreadsheet. The file path and spreadsheet name will be manually entered into cell A1 (on the destination spreadsheet) so the macro would need to refer to this cell to find the correct source spreadsheet to open.

I would then like to attach this code to a command button so that the action above is executed when the button is pressed.

View 8 Replies View Related

Automatically Open New Workbook After 1st Workbook Open 4 Seconds

Nov 6, 2009

I have a presentation that I open from a short-cut. After the "Welcome Page" is opened, I want to open a second workbook in a new instance of Excel after 4 seconds.

I think that I can open the new instance of excel, but I don't know how to activate the macro after 4 seconds.

I'm sure there is a function someplace for this that can be used in a macro.

Then, after the second Workbook is opened, I want the Welcome Page "Workbook" closed, leaving the second Worbook open.

View 14 Replies View Related

Find Workbook With Specified Cell Value And Open?

Nov 6, 2013

I am needing a macro that will search all excel files in directory C: and open the workbook with cell F4 value equal to "Checklist".

View 9 Replies View Related

Reference Cell Of Another Open Workbook

Jun 11, 2008

I would like to know the proper syntax for recalling the value of a cell in another workbook. For example, I want a variable in my code to equal cell(1,1) in sheet1 of workbook1.xls.

View 5 Replies View Related

Setting Macros - Possible To Put Image In Comment Based On Value In Cell

Nov 12, 2012

Is it possible to put image in comment base on the value in cell ?

For example

c1 c2 c3
R1 A
R2 B
R3 C

I can put A.jpg to C1R1"A", B.jpg to C2R2 "B", and C.jpg to C3R3

Or irregular arrangement ??

View 9 Replies View Related

Macros To Save Worksheet Based On Cell Reference

Feb 12, 2009

I have a macros ( code inserted ) as you can see in my code the macros save the workbook in any file name you chose just by changing the any filename option.

I would like this to be changed so that it saves based on a cell reference, say i has a name in lets say B10 i would like it to save as the name in B10.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Dim bk1 As Workbook
Dim bk As Workbook
Dim myfilename As String

View 9 Replies View Related

Open Workbook, Find Cell Value & Input To Cell Next To It

Jun 17, 2008

I have Quote Master.xls open
I have a value in AA1 that carries a number
I want to open Quote Log.xls
I want to find the cell in Column A (Quote Log.xls) that AA1 (Quote Master.xls) directly corresponds to
I want to offset from that found cell 3 cells to the right and insert from T7 (Quote Master.xls) into this offset cell. Here is the code so far

Private Sub InputIntoQuoteLog7_Click()

Dim CostSheetBook As Workbook
Dim QuoteLogBook As Workbook
Set CostSheetBook = Workbooks("Quote Master.xls")
Set RFQQNumber = CostSheetBook. Sheets("RFQ").Range("AA1")
Set RFQQStartDate = CostSheetBook.Sheets("RFQ").Range("T7")

Workbooks.Open ("\ACT3ENGVAULT EngineersLsheriffDocuments (2008)Quote System MashQuoteLog.xls")

Set QuoteLogBook = Workbooks("QuoteLog.xls")

Set vOurResult = .Find(What:=RFQQNumber, After:=[A1], _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)

vOurResult.Value = RFQQStartDate.Value

End Sub

I get an un-numbered error on the .Find

View 8 Replies View Related

Activate Open Workbook From Cell Reference

Aug 20, 2008

activate an open workbook based on the workbook name that is typed into a cell that is a named range?

So, I keep wanting to do this:
Windows.Activate Filename:=Range("MyRange")

Because this works:
Workbooks.Open Filename:=Range("MyRange")

View 9 Replies View Related

Copyrights 2005-15, All rights reserved