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")
i need a VBA code to activate workbook (which is already opened) with reference to name in cell A1
in Cell A1 is "masterworksheet.xls"
I have 2 workbooks open
Book1 has a call to ChangeBook()
Book2 is empty
Function ChangeBook()
Application.Workbooks("Book2.xls").Activate
End Function
ChangeBook() is located in a .xla file loaded on Excel launch.
Calling the function from Book1 does not cause Book2 to activate.
Running the function via the F5 key in the IDE works fine.
I am modifying existing routines that have problems and being able to activate certain books at will would make the changes much easier.
On a command button in a userform, I have this code to open another workbook:
View 2 Replies View RelatedI have a workbook containing macros that opens a second one (no macros) and does some 'data mining'.
The following VBA is used for that (I use this code on dozen of files and it has always worked, except here):
Set WshShell = CreateObject("WScript.Shell")
ChDir (WshShell.SpecialFolders("MyDocuments"))
BladNaam = Application.GetOpenFilename("Excel File, *.xls", , "Excel")
Workbooks.Open FileName:=BladNaam
TabNaam = ActiveSheet.Name
The problem is that most of the time the newly opened workbook is NOT activated (i.e put on top) and thus the rest of the code is executed on the wrong workbook...
If I put in a Msgbox(ActiveWorkbook.Name) 9 out of 10 times the active workbook is the initial one and not the one that was opened by the code.
tried replacing Active.Workbook with wb (dim wb as workbook).
tried to wait-a-few-seconds in between code
tried renaming
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 RelatedI have a program that has all Excel Workbooks in seperate instances of Excel. There is a very sound reason for doing this.
The user has maybe 3 to 10 workbooks open. There are times when a workbook is active and has a Macro Link to open one of the already open workbooks.
When the user clicks the link, they naturally get an error message stating that the workbook is already open. Then they have to close the error msg and click on the Macrosoft Tab and look thru the list of open workbooks and then click the one they are looking for. Additionally, in this Menu Program the user really doesn't even have to know the name of the various workbooks.
I hope everyone will believe me when I state that this program works berautifully.
Right now I am simply cleaning up and making a few little things work better.
QUESTION When the user clicks on a macro link that is to open a workbook that is already open, how - On error - can I have the macro continue on and activate the requested workbook - - - Please remember they are all in separate instances of Excel.
Since the code I'm using can determine if the requested workbook is already open, I think there has to be a way to activate that workbook.
Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
View 13 Replies View RelatedI have a UDF in Cell Sheet1!A1 that inputs some ranges from Sheet1 and calculates a number. Then I have a sub that calculates the value of this function with Evaluate(Sheet1!A1.formula). However, this only works when I am in Sheet1. Otherwise, I get the wrong calculation. How can I avoid this? I will post the code in the open source forum as it otherwise is quite good and automatically resizes array functions to their proper size.
View 2 Replies View Relatedway of supressing the Circular Reference Warning on Workbook Open?
View 9 Replies View RelatedI have two workbooks. One is a no-nonsense form interface that my bosses will use to enter safety information. I'll call this workbook "Form". This file is stored locally on each of their computers. The other workbook is stored on a common drive. I'll call it "Master".
When my bosses fill out the Form and click "Submit", the Master file is opened, and certain cells are populated based on information entered in the Form. This is the code I am using to make this happen:
I'd prefer to edit links with the source workbook open, however I receive this error when I do "A formula in this worksheet contains one or more invalid references." I am able to edit links with the source workbook closed but this takes far too much time to be satisfied with that as the solution. I have used vlookup formulas and the index-match method and they both take about the same amount of time to update. I've separated each worksheet and used the "name manager" add-in and have searched all links for any type of error. I feel like I'm missing something. Is there a way to fix this "invalid reference" error so that I can edit my links with the source workbook open?
View 1 Replies View RelatedI am trying to create an event macro that would deactivate column and row headers when the workbook is launched and activate a sheet. i am not managing to do this,
View 2 Replies View Relatedi am trying to do a sum of my columns but it's not calculating at all.I keep getting "circular reference in an open workbook" error. This is the only workbook opened.
View 4 Replies View RelatedI 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
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
Is there a way to open a URL from a cell reference? For example, in cell B3 I would like to have the web site URL, Google, and have VBA reference the cell rather than a hard coded ActiveWorkbook.FollowHyperlink "[URL]".
View 4 Replies View RelatedI have the VB Editor open and am manually activating different workbooks in Excel (with 20/25 modules each), the VB Editor goes through a process of maximising each and every module in the workbook I have selected before I can edit any code or, indeed, do anything in the workbook. This process takes about 8-10 seconds every time I select a different workbook in excel! I have played around with the settings "Full Module View" in the options section of the VB editor, but to no effect.
View 3 Replies View RelatedI am trying to open a stored file on a network drive (with the file location stored in cell E4). When I try to run the macro, I get an error message on Line 3. (Sheets(E4.Value).Activate)
Does anyone know what I can do to fix this, or have a better method to open the closed files?
I've read that it's possible to reference information from a closed sheet, but I don't think that I'm experienced enough in VBA to take on that effort yet.
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"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
I have two or more DIFFERENT INSTANCES of excel workbooks open at the same time. EX: Wk1, Wk2, & Wk3
Currently I have Wk3 showing in the screen.
Through a Macro, how can I display (bring to front) Wk1 without closing Wk3 ?
This is part of a longer macro, so I only need to know how to do above.
I have an open workbook(A.xls) where the user can press a button which opens another workbook (B.xls) In workbook B they need to add new names then press another button to run another script. The script needs to switch to workbook A in order to work correctly. How can I switch to workbook A without using the name of the workbook? The reason I cant use the name to activate is because the first workbook that is open is not always A.xls
View 4 Replies View RelatedI 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
ImportData
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?
How do I use VBA to Activate Sheet1 of a workbook? I am using the following to go BACK a sheet, but really need to get to the first sheet in the workbook.
Code:
Sheets(Sheets.Count - 1).Select
If it's a new wb, then it's tab is called Sheet1.If it's an existing wb, and it only has 1 sheet, then I need that one (in case someone has added a sheet and deleted Sheet1)
I am having a "cosmetic" issue that I was curious if I can fix it.. I have a workbook that opens a network workbook, saves some data to it, and then closes the network workbook.
Everything is working fine, except I cant get the code to "reactivate" the initial workbook. After excel saves and closes the 2nd workbook I opened, my screen stays on my desktop, instead of refocusing on the initial workbook. I have to manually click on the initial workbook in my taskbar to bring it back into focus...
I thought the below code would bring the inital workbook back into "focus" on the users screen, but it's not working.
Windows("NEWRightFit.xls").Activate
Sheets("Sheet1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
Windows("sxssubmit.xls").Activate
Confirmation.Show
End Sub
I just moved the Application.ScreenUpdating line as initially I had it at the very bottom, but that did not correct. The confirmation.show is just a dialog box that informs the user the changes were saved successfully. I would like the first workbook "sxssubmit.xls" to come back into focus automatically though, without the user having to manually select it from the taskbar.
What is my code missing?
-I've been trying to get an answer to this problem for some time and I would like to try again from a different approach.
I may have 5 workbooks open in 5 different Instances of Excel. The number of files open varies. When the below Function is called, it checks to see if the file, trying to be opened, is already open or not.
Currently, if the file is already open, a mesage is displayed stating that it is open and the user has to search through tabs looking for the file so that it can be displayed.
What I want to do in place of a message, is activate the file that is already open and display it. No message necessary.
I have tried "Application.Activate" and can not make it work.
I have .xls files (ex: a.xls, b.xls etc) in a shared drive.How do I get information (run macros without changing anything) from a.xls and create a new file in my hard drive without opening a.xls. I don’t know if that is possible to do. May be sounds very weird. For example, I name this file on my hard drive as a01.xls, b01.xls from b.xls. Now I run other macros from a01.xls. which is active. I want this macros to run independent of the file name the user creates. In otherwords the workbook has to be variable. Not sure how to use ‘Thisworkbook’ function if that is what is needed to do.
View 12 Replies View RelatedI wrote code to update some workbooks. The code opens the workbooks and then activates the workbook to add the update.
I was tired when I wrote the code to activate the workbook and it is written:
Workbooks("Update").Activate
The updates have already been sent out and it is not working on some computers. (If I change the code to
Workbooks("Update.xls").Activate
it works fine.)
Is there some option in the VBA editor that I can have people change on their computer so the code will run? Why does it work on some computers and not others?
(Unfortunately, rewriting the code to add ".xls" and resending everything isn't an option.)
button on main workbook opens 2 other workbooks and assigns a workbook object to them. the 2 opened workbooks are Activated in turn, range values changed and macros on these sheets invoked and results captured and pasted back onto the starter workbook. the macro is within a sub in a module as are the ones in the second workbook. An example of the code used is:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Dim wkbTest As Workbook
Set wkbTest = Workbooks("Test.xls")
wkbTest.Activate
Sheets("G").Activate
Range("Today").Value = Format(Now(), "dd-mmm-yy")
Application.Run "'" & wkbTest.Name & "'" & "!TestMacro" ............
I have a work book, it copies a cell reference from a work sheet in workbook2 to workbook1
It then selects next work sheet in each page and then triggers the code again.
My problem is i want the following piece of code to work.....what should i put in where it says "activeworksheet"
[Code] ......
I am trying to write a formula that will pick the data out of a closed workbook.
Indirect works but it doesnt help because the workbook needs to be open and there are literaly hundreds of workbooks to sort thru.
Is there a way other than find and replace to have a formula pull data from a specific cell in another workbook, based on say cell A1 or A110?