I have created a Hlookup to read a workbook that has several tabs. How do I write the formula to read every tab. So if my tabs are labelled like this... FY 2012, FY 2012-2, FY 2012-3 - each spreadsheet is formatted the same, same number of rows and columns but I need the formula to look on every sheet and then bring back the information.
View 8 Replies
I have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook
Using this code I get the colour code of the current tab
Code:
x = ActiveSheet.Tab.ColorIndex
But when I use this value in a procedure like this:
Code:
ActiveSheet.Tab.ColorIndex = x
The tab is a different colour!
i'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:
If student missed exam and has a zero – keep zero.
If student has a grade of 1-119 points, increase their grade 40 points.
If student has a grade of 120-125 points, increase their grade 35 points.
If student has a grade of 126-131 points, increase their grade 31 points.
If student has a grade of 132-139 points, increase their grade 27 points.
with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error.
=H4+HLOOKUP(H4,B24:D25,2).
I am trying to import a BASE ESTIMATE table into EXCEL.
I have problems with most of the formulas, especially this one:
=VLOOKUP($E$2,$B$24:$P$604,HLOOKUP($E$3,$D$22:$L$604,1)+2)*HLOOKUP(HLOOKUP($E$3,$D$22:$L$604,1),$D$2 2:$L$23,2)
and this one
=ROUND((IF(AND(OR(E7>E5,E7>E6),E3<40000),E7,IF(AND(E6<E5,E3<40000),E6,E5)))*E8*1.055*1.06,2)+10
I am not sure if EXCEL allows a HLOOKUP within an HLOOKUP. If not, how can I get around this?
I've got several workbooks that users will be using to keep track of individual projects. These workbooks will talk back to a master summary sheet that has the status of all projects on it. The possibility exists that someone else on the network will have the summary book open when the program tries to update it, which would cause an error when it opens as read only. So I want to do something like this.
View 2 Replies View RelatedFor a couple of reasons I have had to take a workbook off of a local machine and place it on the network so others can access it. I cannot share the workbook because of the macro's and protection on it. At any one time only one person should have full control of workbook. The others only need read only access. I won't go into the whole deal but I was hoping there was a way on startup to detect if the workbook is in read only mode or not (Other than the basic notifications and indications that excel gives as many users either ignore or do not understand them). If so I would like a big message to popup stating this fact to the user and also if possible show them who currently has it open. Lastly, they would have a cancel workbook open option until they get the other user to exit.
View 2 Replies View RelatedI am running a call centre with 20 users at peak time. I have 1 user who will always have a master workbook open and saving throughout the day. The remainder of the users during calls will need to open this file as read-only to simply view the latest saved version.
This works however sometimes is takes a over a minute for the file to download from the server for the read only box to appear. I was wondering if there was a way for the other users to view it in a simpler and quicker way?
Also issues with the 1 user saving the file which others have it open as read only?
I have created an Add-in for use across the business, the add in calculates financial impact based on figures that are stored on the shared drive.
The addin currently opens the workbook, and sets the variables to figures contained in the workbook - and then closes the workbook.
To roll this out to the business - I need to get the add-in to open the workbook as read Only, so that it will allow multiple users to access at once. I have set to open as read only in the past - however this prompts me to save the workbook as something else.
Is there any way I can get this to just open as read only, do what it needs to - and then close; without having the option to save as something else?
i need a way to alter the state of a workbook to "editable" then save and then switch back to "read only"
reason being is i need to hide tabs evertime the workbook is closed
I was wondering if there is a way to set a macro to run on an event only if the workbook is read-only. The macro I have set up right now reformats the toolbars to a custom user friendly one, but I want to be able to open the document with a password and not have the toolbars rearange on me.
View 2 Replies View RelatedI have a file that's only instruction is to open another workbook in Read Only; this is due to not having network privileges to make the entire folder Read Only.
I'm getting one user on the network where the Workbook.Open script doesn't fire up automatically and I can't see why. Then ocassionally i'm getting users where the file hasn't closed itself after the file has been opened.
Is there any obvious reason for both of these happening?
Code:
Application.ScreenUpdating = False
Workbooks.Open FileName:=ThisWorkbook.Path & "iSS.xls", ReadOnly:=True
Application.ScreenUpdating = True
ThisWorkbook.Close False
I have a shared Excel Workbook, and I am trying to prevent a non-permitted user from making any changes to it. I have managed to hide all of the worksheets bar one - The Warning Page - if the user is not one of my chosen users, but I am having a little trouble setting the worksheet to open as "read-only" if the user is not one that I have listed.
My macro code looks a bit like this:
If Application.UserName = "Vikki" Then
Sheets("Warning!").Visible = False
Sheets("Total").Visible = True
Else
Sheets("Warning!").Visible = True
Sheets("Total").Visible = False
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
End If
But when I am using this, a non-user will get a msg box asking if they want to save the workbook before changing to read only. Can I prevent this msg box from appearing?
I have a workbook which has a macro to loop round refreshing its data every half an hour and each time the refresh is performed the file gets saved. This file is opened by one user in the morning, and left open with the macro running to carry out the refreshing throughout the day. This workbook is then also opened by a number of users as read only who interrogate (but do not update) the data in the spreadsheet as part of their work. Periodically, they close and re-open their copy of the workbook to ensure they have the most up-to- date version open.
To let them know when a new version is available (i.e. when the refresh has been performed and the file re-saved), I have some code which compares the last saved date/time of the file they are using with the date and time of the file they loaded the file from, and displays a message when they values are different. I have, however, been unable to develop a macro which properly 're-loads' the workbook. I have been able to get the file closed and the latest version opened (through a 'ThisWorkbook.Open' statement). However, when the new version susbsequently opens, none of the open events are triggered, nor is the Auto_Open macro executed. This is a major problem, as the spreadsheet relies on this processing being performed for the file to properly work. (I know that macros are enabled (I get the 'enable macros' prompt in the course of the updated version being opened, and can run macros once the file has been opened).)
I have a Workbook named "Master Archive" that contain data from 9 workbooks. This is all done with a macro upon opening each workbook and using a command button to send the data to it. Question is I want everyone to be able to view the Master Archive Workbook in a readonly fashion and the orginal to always remain closed. Reason being is I cant run the macro to archive the data if someone has it open, which then would cause debug issues with the code.
Are there settings were I can be the only one to open the workbook and be able to Edit.
Also Everyone has a shortcut to this Master Archive on there Desktop.
I have completely forgotten what I need to enable to get this to work
I have one spreadsheet indexed to another. When one user opens their s/s and changes a value and then closes it I need the other s/s to update once they open and refresh.
I am trying to read some values from a closed workbook. I use the following code
View 3 Replies View RelatedI am trying to create a "macro" that will read values from one workbook into an array based on a key field. If the key isn't present the cells data is not to be stored. After the records are stored i want to test them agains a range in a differnt workbook. If the data compared matches a stored value, leave it alone. If there is no match, a cell needs to be added with a value stating a differnce.
Like i stated i am new at this and have no clue how to compare an data array to a field. Let alone read the values into the array.
way of changing the mode of a workbook to readonly upon opening it.
View 6 Replies View RelatedI have one excel workbook which has 10 rows of data. I need to access another excel wb which has 10 rows of data and get the variance. The key here is wb 1 has the data as I manual gather it and it changes very often. WB 2 also changes to equal the number of rows in Wb 1.
A> I would like to call Wb 2 from Wb 1 and copy those 10
B> It should be able to handle change in the number of rows in WB 2
C> It should be able to call workbook 2 irrespective i name it differently.
Is there a way i can achieve all the 3 above?
I've recently discovered the usefulness of VB in excel and have managed to write some basic macros that enhance many of my workbooks. BUT, I've bumped into a problem I can't seem to solve. Please help!
I'm looking to import the values in a large range of cells ( 5 x 5000) in a closed workbook into a range that i specify in an open one, where both workbooks are housed in the same directory. I also want to include logic that allows me to move the 2 files to different directories (the names will never change, only the paths) and have the code still work--I believe i'd use relative path references?
I've found lots of snippets of code on the topic but can't seem to get any of them to work. For instance: http://spreadsheetpage.com/index.php...a_closed_file/
Problem is, I'm so new that I don't even know where to begin pasting the code (objects vs. modules, etc) in the VBA editor.
I have a pair of macros that together close and reopen a workbook when the user clicks on a button labelled 'Refresh'. The reason for the macros is that any one of multiple users may have the workbook open for editing while the others have it open read-only, and those who have it open read-only will want to refresh it periodically to make sure they're viewing the most up-to-date version.
Now, here's the hitch. If I have the workbook open read-only and refresh it while no-one else has it open for editing, it doesn't reopen read-only (it reopens for editing). But I don't want it to reopen for editing (I want it to reopen read-only). Is there anything I can add to the macros to make the workbook reopen read-only, or is there a different pair of macros I can use? Here's what I have at the moment:
Macro 1
Sub CloseMe()
'
Application.OnTime Now, "OpenMe"
ThisWorkbook.Close SaveChanges:=False
'
End Sub
Macro 2
Sub OpenMe()
'
End Sub
I understand that vba can determine if the current file is read-only using the following to return true / false. GetAttr(ThisWorkbook.Path) And vbReadOnly
which seems to work fine for determining if the file is read-only, as there is another instance of the file already open. However when opening a file straight from a zipped directory (i.e. not extracting the file first), Excel opens the file as read-only. The above code returns false in this instance, i guess because the workbook path has a .zip ending rather than the normal .xls etc endings. how to return true that the zip file is read-only?
I have 3 workbooks with multiple spreadsheets (tabs) in each workbook. I want to open a blank workbook and copy various sheet tabs from the other workbooks into my new workbook. I need to keep the formatting etc when these are copied over. There are text boxes in some of the existing workbooks and macro's.
View 2 Replies View RelatedI have a workbook with several tabs. The top tab is the main spreadsheet
were I enter all my gathered data. The other tabs are the same spreadsheet,
the same data, but broken out by specific building.
Right now, I enter the data (alpha and numeric) into the main spreadsheet,
then manually copy/paste it to the respective sub-level spreadsheet.
I can specify the "IF" part of the line item that would identify which sheet
to copy it to, but is it possible to have a "THEN copy and paste [these
cells]" to the other spreadsheets?
I have a workbook that pulls in data from other sources and contains 15 linked sheets. The data changes when different criteria is selected in the first sheet. The first 2 sheets are summary sheets and the tabs are named correctly.
I would like to be able to rename the remaining 13 tabs using labels found in the cells in the first sheet. These tabs should update or change when the first sheet is manually recalculated (F9).
I have used ActiveSheet.Name=Range("A1") but it doesn't apply in this case. Is there a macro that could run with something like Sheet1.Name=Range("A1"), Sheet2.Name=Range("A2"), Sheet3.Name=Range("A3"), etc.... or maybe something in each sheet that would change the name of the tab to a specific name off sheet 1 when the whole workbook is recalculated?
Just wondering if there's a "codeless" way to print en entire workbook (i.e. all tabs) without moving from tab to tab and hitting print?
View 1 Replies View RelatedI have to prepare files of over 100 tabs in each. I have a list of numbers on a spreadsheet that must be entered on each tab. Am I making myself clear because I tried to do it manually and it's no joke?
View 1 Replies View RelatedIs there a way to Sort all the Tabs in a Workbook by the Tab name?
View 1 Replies View RelatedI have a program that takes a templete, copies it, fills in some information and then renames the tab based on text in a cell on that sheet. The code loops so I end up with about ten tabs that are created from the Template.
I want to move these newly created tabs to a new Workbook.
is there a way (code) that will move all the tabs except ones delineated in the code - like the tab named "Template"?
Is there any way to arrange the tab easily instead of move back and forth and drag? I have a work book with 50 tabs and if I like to arrange it, I have to move the cursor pointer zillions time and than find and drag the right tab at the right place. Is there any easir way? Like you do in power point?
View 6 Replies View Related
