I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy. I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them. I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine. Here's the code that does it. Code: Sheets('WELCOME').Select Range('A1:N18').Select ActiveWindow.Zoom = True By repeating this code for each worksheet, I can make each one be zoomed just right.
However, the file contains 8 sheets that are all identically laid out, except the number of rows is different. What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout. I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
In this tutorial we will guide you on how to save files in Excel using VBA. Visual Basic for applications (VBA) is a powerful programming language designed and developed by Microsoft.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes. Anyone know how to do this?
I have a workbook that has compliance dates in columns 'F','G' and 'H' from row 7. What I need is when the date in either column comes within 30 days to auto send an email, address in column 'A', recipients name in column 'B'.
And then place todays date in column 'P'. ALso need to send a follow up email when either date comes within 7days and then place todays date in column 'Q'. If there is a date in column 'P' then don't send email. If there is a date in column 'Q' then don't send follow up. Can this be done without the users intervention and each time the workbook is opened. Thanks in advance for any assistance.
Hello, Here is my issue: I have a list of SKUs in Excel and I need to add one same word to 500 cells that have pre-existing text in the cells. See example below: 1) List of SKU's in cells: 34 35 39 55 2) text that needs to be added IN FRONT of every number: DF So the result would be: DF34 DF35 DF39 etc. How do I do that in a formula and not manually? I found another similar thread and I understand how to do it from the instructions there but the same text goes on the back and the result is: 34DF but I need it to be like: DF34. I am using Office 2007 for mac. Thank you for the help, Chris. I am creating a basic accounting spreadsheet and I ask for assistance to automate data entry posts.
I would like to have entries that are entered in the general journal automatically post to the corresponding ledger accounts. The entries need to remain in chronological order. Relative cell referencing will not work because the rows in the ledgers will not correlate to the rows in the journal because the journal but the entries in the ledger need to input on the next available row in that ledger account. Additionally, as the number of entries increase, can the ledger accounts add a new row? Attached is a very simple example with three transactions.
I have included an end state view in the ledger accounts tab. Right now I have a sports league with 8 teams, I'd like to create a random 7-game schedule where each team plays the other team only once. Essentially this will be a 7-game round robin. However, I'd like to be able to use this for any number of teams and games. I'd like to do this in Excel, but I can't figure out how to have a randomly generating non-repeating macro with text values in cells. So right now I have a column of 8 values and need matrix of 7 columns by 8 rows next to it.
I've found this thread that has one for numbers, but I can't figure out how to do it for text values. I have looked over the forums for information on how to do this, and can not find anything. I have used conditional formatting to identify duplicates in my spreadsheet.
I have a unique user ID, which combines the first and last name fields into one. However, in 10,000 there are a number of John Smiths, Joe Adams, etc. With smaller lists, we search manually, and then add a 1, 2, 3, etc to each duplicate to create a unique value for the User ID. My question is: Is there a way to check for duplicates, and then automatically add numbers to the duplicate values to make them unique? If so, how can this be done?
So that you will have this: JohnSmith JohnSmith2 JohnSmith3 JaneDoe JaneDoe2 JohnAdams JohnAdams2 JohnAdams3 JohnAdams4 I think I have done a good job in illustrating what I would like to be able to do, but if there is still something that is unclear, I will post whatever clarification is needed. Thank you in advance for your help!!
I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database. Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference. I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN). What modifications do I make to this to get this to work per above requirements?
Sub CopyMe Dim SaveMeAs As String SaveMeAs = Sheets('Sheet1').Range('B2').Text Sheets('Sheet3').Copy ActiveWorkbook.SaveAs Filename:='C: My Documents ' & SaveMeAs End Sub. I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook.
Sheet 1 is the master sheet 'Team Stats'. There will be an undetermined number of individual worksheets to accomodate new staff.
Each worksheet will be identical, using columns A-I with row 1 having the headings: Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell). There will be a varying number of rows in each of the individual sheets. If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.
If anyone can help it would really cut down the time I spend collating these stats every day! I was wondering if anyone could help me out please?!?! I need to have a cell on Sheet2 display the TEXT from a cell on Sheet1, is his possible??
Example: Sheet1, Cell A1 contains a clients name and I would like to have Sheet2, Sheet3, Sheet4 etc. Display that clients name in a cell of my choosing (could be a different cell on each Sheet) automatically after entering it once on Sheet1, cell A1. I thought this was possible but I can't seem to figure it out. I haven't done any real Excel work in quite a long time but I thought I had done this before a long time ago!! Any help would be GREATLY appreciated!!
Thanks in advance. If I use autosum to do this it just displays the number 0, obviously, since it is trying to add numbers. I uploaded a copy of the spreadsheet, What I would like to happen is have the text from Sheet1,D4 automatically be placed in Sheet2,A1 and Sheet3, B2. Hope this helps -Aric. Hello everyone, I am new to this forum and have been using excel for about 3 years.
I have learned alot but still can not figure out how to make numbers add up in one box like a running total. Meaning, If I have a list and in the list is one cell for each item say like cell a1 is for a can of coke and cell a2 is for a car tire and so on,and i want to keep a running total by adding a number to cell a1 or a2 like say '3 i.e. 3 tires or cans of coke' and later that day i come back and need to add 2 more.so i want to be able to just click on the cell a1 or a2 and enter the number 2 and the cell would add the number in it to the number im adding to it to show a result of 5 tires or whatever. I am sorry If i am not asking the question in an understandable manor but its the best way I can think to ask it.lol anyways, any help would be appreciated, Thanks in advance. I have a file that became too big due to phantom bloat, unused range saved by Excel and all that kind of reasons. Thank to previous posts on that board tackling that issue, I was able to find how to proceed to reduce the file back to its normal size.
But I did so in a beta file (test file). The real file has become so big (103MB!) that Excel cannot even open it anymore!
The file contains archive info that we do not have anywhere else. Is there anyway then to open the file or to reduce its size without opening it (through magics.)? I just honestly don't know how to retrieve that info before deleting that file. Thank you very much for your help.
Hi there, I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys! In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2.
If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2: =Sheet1!A3 (or whichever cell it is) That's fine, but I'd like to just have a formula that will reproduce the entire column (ie.
Without a fixed range, as new rows are going to be added to the column). If anyone could point me in the right direction, I'd be very very grateful.
Example (as VBA Function) The DATESERIAL function can only be used in VBA code in Microsoft Excel. Let's look at some Excel DATESERIAL function examples and explore how to use the DATESERIAL function in Excel VBA code: DateSerial(2004, 6, 30) Result: '6/30/2004' DateSerial(2004-1, 6, 30) Result: '6/30/2003' DateSerial(2004, 6-2, 14) Result: '4/14/2004' For example: Dim LDate As Date LDate = DateSerial(2004, 5, 31) In this example, the variable called LDate would now contain the value of '5/31/2004'.