Excel 2011 :: Using Macros To Export / Archive
May 2, 2013
I'm using Excel 2011 on a MacBook Pro. Here's my situation:
I'm running a monthly budget report that's got three spreadsheets within the workbook. Spreadsheet #1 is summary sheet with a few pivot tables. Spreadsheet #2 is the data sheet, where all of the information is entered, and is what feeds the pivot tables on Spreadsheet #1. Spreadsheet #3 is just a list page that populates the drop-down list on the second sheet.
As this is a monthly budget report, I'd like to find some way to archive the first spreadsheet (the summary sheet) every month. Ideally, I'd like to be able to create a macro-driven button that, when clicked, does the following actions:
1) Creates a new spreadsheet, with each click, named for the current month and year.
2) Archives the exact contents of Spreadsheet #1 by making an exact copy of its current contents, and places this copy into the newly created spreadsheet.
3) Clears the data from Spreadsheet #1 AND Spreadsheet #2.
To summarize, I'm trying to use a button that, when clicked, runs a macro that archives the current month's data into a new spreadsheet, and then clears the data from Sheets #1 and #2 so that I can start each month with a clean slate. If there's a way to only clear the data from certain cell ranges (as opposed to a complete wipe of the sheet), that would be even better.
View 2 Replies
ADVERTISEMENT
Feb 27, 2012
My VBA which works fine on Excel 2007/2010 does not work on Excel for Mac 2011.
how I would convert this to work on Mac 2011?
Code:
Columns("AI:AI").Select
Selection.TextToColumns Destination:=Range("AI1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
[Code]....
View 1 Replies
View Related
Aug 15, 2013
I am running Excel 2011 on Mac OS X. I am trying to save a file (.xls) to a server (Autumn). I have tried several configurations to try to get the file saved properly. File name is "Testfile.xls"
1. folderpath = "Autumn:Tim:TestFolder:" & filename --Activeworkbook.Saveas filename:=folderpath
2. folderpath = "Tim:TestFolder:" & filename Activeworkbook.Saveas filename:=folderpath
3 Activeworkbook.Saveas folderpath
When I try this, I get an Error 1004 indicating that it cannot find the file. Do I need to eliminate the .xls and then specify that when I do the saveas?
View 1 Replies
View Related
Sep 26, 2013
For Mac Excel 2011, though I think my question would apply to any modern version of Excel.
This is a little odd. I have a sheet where, for various reasons (see below ### if interested), I need to copy a hyperlink, replace a substring, and then return the new hyperlink (with old friendly text) to the original cell. (I actually have to do this with an arbitrarily-long column of links.) This was my plan of attack:
* Pull the URL from the hyperlink cell.
* Use FIND and RIGHT to strip out what I need and replace it
* Use HYPERLINK to build a new cell with the original friendly text and the new hyperlink.
* (Manually) copy this new link (really, a column of links) back to the original cell (really, column of cells).
Here's where it breaks down:
a) If I simply cut-and-paste, the formula used to pull the hyperlink becomes circular (as do several others), and Excel complains.
b) If I cut-and-paste-special (values only), I get just the friendly text, no link info. Likewise if I try to reference the "fixed" hyperlink.
The erroneous hyperlinks are in column K. My calculated correct hyperlinks are in column S. (There are similar errors and fixes in L and T, respectively.)
View 1 Replies
View Related
Nov 23, 2011
I want to open all csv files in a folder and just copy and paste some columns into another file. I am having a bit of trouble with the following code which can be found at ozgrid and I have modified slightly so it looks in the current directory instead of a fixed path...
Code:
Sub RunCodeOnAllFiles()
Dim lCount As Long
Dim MyDir as string
[Code]....
For starters, if I don't use on error resume next I get a runtime error 445: object doesn't support this action. So when I use the on error resume next, then by the time it gets to the foundfiles line, there are no variables set, so on the next line (workbooks.open) nothing happens.
Is there an easier (more up to date?) method to open all csv files (or excel files) in a folder?
View 6 Replies
View Related
Dec 12, 2011
In MS Excel 2011 for Macintosh can I assign a macro to an object, such that if the user clicks the object it runs the macro? That is creating a button on the spreadsheet that will execute a macro.
View 2 Replies
View Related
Jan 25, 2012
So i am working on an attendance tracker here. I already have aligned on a separate tab all of the works aligned next to their manager
EX
Column A: Column B:
Worker Name Manager
What i would like to do is create a drop down menu with all of the Managers name listed to where someone can select that manager and when they do all of the workers names show up in Column A and then that Manager Listed next to them.
Is there a way to do this... I am using office 2011 btw. Once when i get this last part completed with this drop down menu i should be complete!
View 2 Replies
View Related
Jun 5, 2012
I have a friend using Excel for Macs 2011 and the column and row headings (ABC & 123) are not showing on the spreadsheets on the screen. How to turn them on. I fgure they got turned off somehow?????
View 5 Replies
View Related
Sep 15, 2012
I use mac and I have "Excel for Mac 2011".
I need to exact some data from a file. I can open the file, but can't see any data of it. I am afraid I need to do some setting so that I can see the data, but I don't know what should I do
View 1 Replies
View Related
Dec 5, 2013
How to insert a Calendar Dropdown for Mac Excel 2011? Also, do I have to readjust this (for lack-of-a-better-word) 'add-in' everytime the calendar year changes? This will be used for a payroll template.
View 1 Replies
View Related
Aug 2, 2014
My software is Excel for Mac 2011 version 14.4.3 (140616).
I am using the present value formula =PV(rate, nper, pmt, [fv], [type]), with the pmt expressed as a negative number, to determine the present value of a lump sum that would generate specific, equal, regular annual payments, exhausting itself in the process. I fill it in this way: rate is an annual rate, nper is the number of years the payments run, pmt is the annual amount paid each year, fv =0, type =1
I would like to be able to have a similar formula in Excel that would do the same thing except that it would take into account a time period where no payment is made, ie a "delay".
For example using the PV formula I know that if I have $49,019.96 invested at 1%, it will generate $10,000 per year for 5 years, leaving a net investment of 0. I would like to know the formula that would tell me how much I would need to invest at 1% to yield $10,000 per year for 5 years, again exhausting itself completely in the process, with the first $10,000 payment starting 3 years from now. I know it would be less because I would have the full amount earning interest for 3 years at 1% before any payments need to be made.
View 3 Replies
View Related
Feb 26, 2013
I am working on a spreadsheet for my Building company. I'm building a tool to price for Fascia, Soffits and Cladding.
I'm using Macbook Pro Microsoft Office for Mac 2011
Please see attached file
The first sheet holds all the lists of products, Category Headings list is in column A and then all the relevant products and prices are then from B to BI. I have defined each category with a name by selecting the cells and entering a name in the name box.
The second sheet is a Calculator in which I would like a to have a drop down list in the Category Column (Which I have worked out how to do myself - good old google) and then a drop down list in the second column which lets the user select from a list of results based on the selection from the previous column.
Example:
Category(B3) - 18mm Fascia/Replacement Board (Square White) - Drop down menu taken from sheet 1 A3:A33
Description (C3) - Drop down list containing all the options from D2:D15 Named "FasciaReplacementBoard18mmWhite"
So basically, whatever the user selects in Column B (from the category list) a drop down list would be available in Column C
The Value column would then show a value based on the options selected.
View 14 Replies
View Related
Jul 12, 2013
I am using Excel 2011 and we have a link data source to the outside SQL Server.
For the 1st step, I linked to the Database and retrieve the data.
P1.png
2nd step. Sort the data by using the column "Description" Descending. Add another column to the table and put the 1, 2, 3, etc in that column.
P2.png
3rd step. I clicked on "Refresh" and the new column values are not consistent. I mean they are moving. So, I added another row in the database to test and it becomes very obvious. It can be clearly seen at the following image. The value of "A" should always be "10", but it has been changed to "1" after the refresh.
P3.png
how I could retain the position of another column after the data refresh?
View 3 Replies
View Related
Aug 24, 2013
I have Excel MAC 2011 and my spreadsheet was created with multiple cell colors randomly throughout the spreadsheet.
I need to change all the royal blue colored cells to "no color" without changing the data contained in the cell. For example; Cells A5:A38 are blue, A39 is green, A40:A50 blue, A51:60 green, ... Cells B5:B15 are grey, B16:B20 are blue, B21 is yellow...
I can't find the "Advanced Options" for Find and Replace that might have allowed me to specify a color change.
View 4 Replies
View Related
Sep 14, 2013
I have switched from an ageing Mac Powerbook, Excel 2004 for Mac, and an out-of-date version of Firefox to an IMac with Excel 2011 for Mac and Safari as a browser. The only reason why I mention this is because I don't know if it was easier to do this on an older platform (one of those bizzare situations where going forwards is actually a step backwards).
In short I like doing things with Sports data in Excel. Usually I would hit my favourite website for pulling sports data (which incidentally I don't know if this has changed as I've been too busy to do much of this kind of thing for about 4 months - or if it is just a different format on a newer machine/different browser);
[URL] ....
The link should be showing NFL results for this year so far (I like the site as it has a handy amount of other sports etc.).
I would usually then go edit/paste special - unicode text and then hit the paste options, use the text import wizard, go delimited, tabs - and usually everything would come in on a row and then I could do what I need to do with it (e.g. use Excel's text functions to get the data - taking spaces out for example - to where I want it to do other things).
The problem I seem to be having now is that when I do the above - things no longer seem to come in on the same row (which makes life difficult for doing things easily with the data). Once things end up on different rows it becomes a pain to do basic things.
In short is there a way round this (or do I need to find another source for my data with tables laid out in a more friendly way to "borrow" data)?
View 9 Replies
View Related
Aug 16, 2014
I am building a code based on a command button in a row, which will check is a worksheet exists (message), if not create a worksheet from a template (from another worksheet specific to a on a cell value in the same row), rename the worksheet based on a cell value in the same row.
I am having some success for each task with exception to relative cell values .....
As the code will be specific to the row (one button per row) Questions:
how to i determine the location of the button that is clicked? (I assume once this is established i can use to pull values in the same sheet on certain columns....?)
View 2 Replies
View Related
Jan 2, 2012
I'm using Microsoft Excel for Mac 2011. I'm creating a drop down box from List within Data Validation. It's only letting my create a list of 17 names. My longest list is 63 names. What do I need to do to create a list that will support that?
View 3 Replies
View Related
Jan 30, 2012
I'm using Excel 2011 for MAC.
What I need is to clear and lock some cells based on a drop down list selection.
The case is like this:
In Cell [E14] is a drop down list : 0,1,2 or 3
Cells: (D19:E19), (D21:E21), (D23,E23) Merged are PART DESCRIPTION
Cells: [G19], [G21], [G23] are COST
So I need when 0 is selected in the dropdown list, all of the above cells are cleared and no input is permitted, Cells locked.
If 1 is selected, just the first row is allowed for data entry and the other 2 are cleared and locked as this values populate another worksheet for calculations.
So far I did this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [E14] = "0" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("D19:E19").Locked = True
ActiveSheet.Range("G19").Locked = True
ActiveSheet.Range("D21:E21").Locked = True
ActiveSheet.Range("G21").Locked = True
ActiveSheet.Range("D23:E23").Locked = True
[code]...
It works fine to lock the cells, but I can not fined the way to clear the values previously stored before locking the cells.
View 9 Replies
View Related
Feb 5, 2013
I am trying to sort a list of business development leads. When a lead is dead I enter the date in the column entitled "Closed". Therefore, the Closed cell for active leads is blank. Whilst I still wish to retain the information of dead leads, when sorting I would like them listed at the bottom of the list. However, whenever I sort on the Closed column, whether using newest to oldest or oldest to newest, the blank cells are always at the bottom, instead of the top.
How to get the sorted blank cells to the top?
I'm using Excel 2011 on Mac OS X 10.7.
View 2 Replies
View Related
Apr 3, 2013
I've been looking for a free checkbook register template that will automatically calculate the balance as you add or subtract various amounts.
I found one which seemed to be just what I wanted but once I had reached the end of the rows for the worksheet and tried to add more rows the automatic feature quit working.
I found another Checkbook Register which seemed even better than the first one as it had a notation at the end of the rows.
It said to " Insert new rows above this point" and it had the last row greyed out.
I tried adding a new row and tested it out by adding some amount but it did not continue with the automatic calculation either.
I'm using MS Excel for Mac 2011
View 4 Replies
View Related
Jul 26, 2014
I have a workbook with 2 sheets.
Sheet 1 will act as a summary for sheet 2, showing only the last 5 entries from sheet 2.
Sheet 2 is based on football results of a season which will have a total of 34 league games plus cup games.
Sheet 2 has 4 columns:
Game Number Home Team Result Away Team
1 United 2-0 City
2 Sportive 4-2 Uniao
3 Real 1-0 Ateltico
4 Villa 1-3 City
5 United 2-1 Real
6 Atletico 0-0 Villa
So sheet 1 will show the last 5 results as the season progress's and new data is added.
I have been able to extract column 'GAME NUMBER' using the following formula:
=INDEX('Back end 1'!A:A,MATCH(9.99999999999999E+307,'Back end 1'!A:A)-ROWS('Back end 1'!A2:A$6)+1)
I am struggling to find a way to pull the last 5 data that is text based.
I am using Excel Mac 2011.
View 3 Replies
View Related
Mar 28, 2014
I manage a number if individuals in the Army and as such, track their annual training requirements. Some requirements are semi-annual, annual, quadrennial, etc...
I have a spreadsheet to track the dates that the requirement was last completed (attached). I would like to use conditional formatting to turn dates that are within 30 days of being due (or blank) RED and dates that are between 31 days and 60 days of being due YELLOW.
I am using Excel MAC 2011.
View 3 Replies
View Related
Feb 15, 2014
I am creating a spreadsheet for results of a survey. I am trying to give numeric values to text fields so that I can place the cell values later into a SUM function. The text fields are entered into the spreadsheet via a drop down list in each cell I created by utilizing the validate button. The text field contains choices like The text field contains choices like “1 Very poor,” or “4 Very much.” The source for the list is on a separate sheet.
EX: If cell C5 equals the choice “1 Very poor,” and cell C8 equals “4 Very much” I want the cells to have numeric values of 1 and 4 respectively so that I can utilize an equation like =SUM(6-C5+6-C8) later in the spreadsheet to calculate aggregate scores.
I am using a 2011 for Mac version
View 5 Replies
View Related
Mar 18, 2014
Designing a proposal on MAC EXCEL 2011
1 workbook with:
1 page needs to print vertical
1 page needs to print landscape
Is this possible? (without reloading paper and printing a second time)
Right now I can only get it to print landscape OR portrait but not both ...
View 2 Replies
View Related
Oct 7, 2011
Named my data range using this formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
I was hoping that my pivot table would refresh with the new data that I added on sheet 1 but it is not working and yes I have refreshed my pivot table. The new data is not capturing.
Is this the correct formula for Mac Excel 2011?
View 1 Replies
View Related
Mar 15, 2012
I have looked and experimented on how to include multiple ranges on a pivot table in excel 2011, i have found how to to it with the wizard but i can not find the wizard.
How to separate the ranges in the selection box? They are going to be going across sheet is that makes any difference.
View 4 Replies
View Related
Oct 1, 2012
I have a from that work great in PC but if opened in a MAC, some of the functions will not work.
For example, There is one cell with Data validation that based on the value selected in another data validation cell, it should only display all possible options pertaining to value chosen. These are the values: a user select a customer based on customer name. The Data validation will the put the customer number in cell AF3. In another call with Data Validation I have this formula:
"=OFFSET(SHIPTO,MATCH(AF3,SHIPTOColumn,0)-1,2,COUNTIF(SHIPTOColumn,AF3),1)"
SHIPTO is the Name range for the tabs called SHIPTO with "Shipto!$A$1"
SHIPTOColumn is the Name Range for "SHIPTO!$A:$A"
Column A is where the customer numbers are.
This work fine in a PC but not in the MAC using excel 2011
View 1 Replies
View Related
Mar 27, 2013
I am using Excel 2011 for Mac.
I am creating a workbook with multiple worksheets, one overview sheet and then one sheet for each month of the year.
In the monthly sheets I have a Category column for which I have created a drop-down menu of expense categories. Then I have an Expense Amount column where I enter the amount spent.
The Category column will not necessarily remain sorted by category because I will be entering the expenses as they come up throughout the month so the categories will be all mixed up, unless I manually sort them.
What I want to do is somehow automatically calculate a running subtotal by Category (that will update with each new entry) and simultaneously automatically transfer the running subtotal for each Category to a specific cell on the overview sheet.
View 2 Replies
View Related
Jan 17, 2014
I have a pivot table which has a report filter. The report filter can have anywhere from 20 to 350 values. The user will be able to select multiple values from the listing to produce the table as needed. I would like to be able to display what values were selected in the report filter so that the user can see this information once the pivot table is rendered. Is there any way to display this information either above below or along side of the table itself?
I am using Excel 2011 for Mac, but I also have access to Excel 2011 for Windows. Either way will work. I have been able to get quite a bit of what works on Windows to work with the Mac.
View 1 Replies
View Related
Apr 17, 2014
I'm on Mac using Excel 2011. This means I don't have "slicers", which is all my Googling kept turning up.
I have a pivot table with 4 different value columns, and I want to be able to filter it the same way you would a normal table - i.e. remove everything below a specific number in one column, and filter for only specific strings in another column, etc.
View 9 Replies
View Related
