Search Results

Search found 4901 results on 197 pages for 'excel macros'.

Page 5/197 | < Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

• MS Excel - Macros for consolidating values from multiple sheets into a single sheet

  - by SpikETidE

  Hi everyone... Consider i have 4 workbooks with the following structure... 1. Main.xlsx Name Jan Feb Mar A B C 2. Jan.xlsx Name Jan A 3.3 B 6.4 C 5.3 3. Feb.xlsx Name Feb A 1.3 B 3.4 C 5.5 4. Mar.xlsx Name Mar A 1.3 B 3.4 C 5.5 I need to combine them like 1. Main.xlsx Name Jan Feb Mar A 3.3 1.3 1.3 B 6.4 3.4 3.4 C 5.3 5.5 5.5 And i need to automate the process... And i guess i can do this with macros...? Can anyone suggest some way with which i can proceed with the macro? Thanks for your time....

  Read the article

• Excel 2003 - How to build my own XLA?

  - by Justin

  How can you make the .xla file if you want to create your own xla? I have the code, classes, shapes, etc....what is the process to making an xla file to point to? I know that I have to put it in my program files folder, and then go through the steps to adding an "add-in" in xls...but i mean actually saving an xla file to point to.... can i simply write all this in xls and then save it as file type xla?? because i have tried to do that but I get an error that it is not a valid add in when I am in a spreadsheet trying to point to it? thanks!

  Read the article

• should I include VB macros in source control with my project?

  - by Sarah Vessels

  For a C# project, I make use of several Visual Basic macros in Visual Studio. I was just considering that these would be of use to other developers that work on the C# project. The macros so far include removing trailing whitespace on save, organizing using directives and removing unnecessary ones, and an override for Ctrl-M Ctrl-O that expands regions. Would it be reasonable for me to include this macro code with my C# project in Subversion? I don't know if it's even possible for macros to be made available/work in Visual Studio just because you open a particular Solution file, and that might be too invasive since some of the macros override existing VS behavior.

  Read the article

• Excel Functions

  - by dwyane

  =MAX(SUM(A1:A5)) How do i incorporate the above formula into =IF( AND( $H$14<F22, F22<=($H$14+$H$15) ), $I$15, IF( AND( $H$14+$H$15<F22, F22<($H$14+$H$15+$H$16) ), $I$16, IF( AND( $H$14+$H$15+$H$16<F22, F22<=($H$14+$H$15+$H$16+$H$17) ), $I$17, $I$14 ) ) ) It keeps running a circular reference error. Help! The sum value shouldnt exceed 150. If exceed, then replace the cell with zero value.

  Read the article

• How to copy a text array to a series of cells in Excel

  - by aSystemOverload

  I am dynamically creating a report, where I create a worksheet, bring in the records afresh. How can I easily type the field names and copy them to the cells. Without doing one cell per line, there are ~20 columns. I tried: dim fieldNames as variant fieldNames = ("'DS Date', 'A', 'B', 'A','S ASD', 'S','D S','D S', 'S','D S', 'SD', 'S','D'") Sheets("DATA").Range("C14:W14").Value = Application.WorksheetFunction.Transpose(fieldNames) But it just posts the whole thing in each cell? Any ideas?

  Read the article

• All hail the Excel Queen

  - by Tim Dexter

  An excellent question this past week from dear ol Blighty; actually from Brian at Nextgen Clearing Ltd in the big smoke (London). Brian was developing an excel template and wanted to be able to reference the data fields multiple times inside the Excel template. Damn good question and I of course has some wacky solutions, from macros and cell referencing in Excel to pre-processing the data with an XSL stylesheet to copy the data multiple times so it could be referenced multiple times. All completely outlandish, enter our Queen of Excel, Shirley from the development team. Shirley is singlehandedly responsible for the Excel templates, I put her through six months of hell a few years back, with a host of Excel template requirements. She was more than up to the challenge and has developed some great features. One of those, is the ability to use the hidden XDO_METADATA sheet to map the data to custom named fields so they can be used multiple times in the template. So simple and very neat! Excel template and regular Excel users will know that you can only use the naming function once ie the names have to be unique across the workbook so you can not reuse a cell/group name. To get around this you can just come up with as many cell names as you want and map them in the XDO_METADATA sheet to the data columns/fields in your XML data set:. For example: XDO_?DEPTNO_SUMMARY?  <?DEPTNO?> XDO_?DNAME_SUMMARY?  <?DNAME?> XDO_GROUP_?G_D_DETAIL? <xsl:for-each-group select=".//G_D" group-by="./DEPTNO"> XDO_?DEPTNO_DETAIL? <?DEPTNO?> As you can see DEPTNO has been referenced twice and mapped to different named values in the left hand column. These values can then be used to name individual cells in the Excel template. You'll also notice a mix of Publisher <? ...?> and native XSL commands. So the world is your oyster on the mapping and the complexity you might need for calculations or string manipulation. Shirley has kindly built out a sample Excel template, data and result here so you can see how it all hangs together. the XDO_METADATA sheet is hidden, just right click on the sheet names and use the Unhide command to show it.

  Read the article

• Backing up Excel Files to a different Directory

  - by Joe Taylor

  In Excel 2007 in the Save As box there is an option to 'Create a Backup' which simply backs up the file whenever it is saved. Unfortunately it backs up the file to the same directory as the original. Is there a simple way to change this directory to another drive / folder? I have messed about with macros to do this, coming up with: Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Saves the current file to a backup folder and the default folder 'Note that any backup is overwritten Application.DisplayAlerts = False ActiveWorkbook.SaveCopyAs Filename:="T:\TEC_SERV\Backup file folder - DO NOT DELETE\" & _ ActiveWorkbook.Name ActiveWorkbook.Save Application.DisplayAlerts = True End Sub This creates a backup of the file ok the first time, however if this is tried again I get: Run-Time Error '1004'; Microsoft Office Excel cannot access the file 'T:\TEC_SERV\Backup file folder - DO NOT DELETE\Test Macro Sheet.xlsm. There are several possible reasons: The file name or path does not exist The file is being used by another program The workbook you are trying to save has the same name as a... I know the path is correct, I also know that the file is not open anywhere else. The workbook has the same name as the one I'm trying to save over but it should just overwrite. I have posted the question about the coding on Stack Overflow but wondered if there is an easier way to do this. Any help would be much appreciated. Joe

  Read the article

• Prevent Excel from resizing graphs

  - by Mehper C. Palavuzlar

  When a row or column is inserted around a graph in Excel, Excel automatically resizes (expands) the graph. This corrupts the dimensions I determine for a specific graph. How can I prevent Excel doing this resizing officiousness for all documents as default?

  Read the article

• Easiest way to open CSV with commas in Excel

  - by Borek

  CSV files are automatically associated with Excel but when I open them, all the rows are basically in the first column, like this: It's probably because when Excel thinks "comma-separated values", it actually searches for some other delimiter (I think it's semicolon but it's not important). Now when I have already opened this file in Excel, is there a button or something to tell it "reopen this file and use comma as a delimiter"? I know I can import the data into a new worksheet etc. but I'm asking specifically for a help with situation where I already have a CSV file with commas in it and I want to open it in Excel without creating new workbook or transforming the original file.

  Read the article

• Dynamically reference a Named Table Column via cell content in Excel

  - by rcphq

  How do I reference an Excel Table column dynamically in Excel 2007? ie: i wanna reference a named column of a named table and what table it is will vary with the value of a cell. I have a Table in Excel (Let's call it Table1). I want to reference one of its columns (Let's call it column1) dynamically from a value in another cell (A1) so that I can achieve the following result: When I change A1, the formula that counts Table1[DynamicallyReferencedColumnName] gets updated to the new reference. I tried using =Count(Table1[INDIRECT("$A$1")]) but Excel says the formula contains an error. Example: A1 = names then the formula would equal Count(Table1[names]). A1 = lastname then the formula would equal Count(Table1[lastname]).

  Read the article

• Excel - Avoid cell text to be shown onto next empty cell

  - by e-mre

  When you have text in an Excel cell that is too long to be shown in the visible area of a single cell and the cell next to the first cell (the one on the right) is empty, Excel lets the text to be printed onto the next cell. This is what I want to change. I want to avoid this text overflow. I know I can avoid this by enabling "word wrap" and adjusting row height. But that is not what I want. I want to change the DEFAULT behavior of Excel so it shows the value of each cell only in the visible area of that cell. No overflow, no word wrap. Is this possible? (I am using Excel 2010 by the way)

  Read the article

• Excel File Names not Displayed

  - by BiGXERO

  Problem: When excel windows are minimised, I can't see the file name, all I get is a rather unhelpful 'Microsoft Excel' proceeded by the file name. I have the taskbar docked on the left, and have second monitor that I use about 50% of the time, but can't work out what is causing the problem, or a setting that I can change that will remove the appended software name at the beginning of the file. Using Excel 2010 on Win 7 64 Bit Screenshot:

  Read the article

• Excel 2007 save import steps on csv file?

  - by Chris Marisic

  I have a csv file that constantly needs opened into Excel and then have the data copied over to a separate workbook. I find the process of having to click through all of the dialogs, setting the text identifier, setting the columns to all be text extremely tedious. In many actions with data like this in regards to MSSQL or Access the program will ask you if you wish to save these steps however Excel doesn't readily ask that. Is there any way to get a comparable usage with Excel?

  Read the article

• Excel controls not visible for certain users

  - by Nossidge

  One of the users of an Excel program I've written is having a weird problem. None of the control objects (Command Button, ComboBox, etc.) are visible to him when he opens the file on his laptop. He is using Excel 2003, the same version I used to create the program, and enables macros using the pop-up when the file loads. I have Googled this, and have found these people who seem to be having the exact same problem, with various versions of Excel. Unfortunately, none of their questions were answered. I can't really explain it any better than this user: If I enter design mode and pull a control from the control toolbar onto a sheet all I see are the drag handles. When not in design mode I have to feel around with the mouse and can click the button which executes the button click code correctly and opens another sheet where again I have to feel around for the buttons to return me to the original sheet. The button I managed to click is now visible but as soon as I click anywhere on the sheet it disappears. I have verified that the visible property of the buttons is set and that the Show All Objects on the Options View tab is selected. If I pull buttons from the Forms toolbar onto a sheet they are visible. If I try to find Objects using F5 when not in design mode Excel reports no objects on the sheet. So, Super Users, can you help? UPDATE: Thanks for your replies, but much like the person in the ozgrid link, the problem has gone away. Not sure why it went, but I can confirm that the user rebooted again and also started up other Excel files that didn't contain controls in the interim. Perhaps that fixed it, or maybe it'll be back again. I'll keep udating with progress, and close if the problem doesn't reoccur for the next few days. Thanks again.

  Read the article

• Excel - Disable AutoFormatting on Import

  - by Philip Wales

  How can I stop Microsoft Excel from auto formatting data when imported from a text file? Specifically, I want it to treat all of the values as text. I am auditing insurance data in excel before it is uploaded to the new database. The files come to me as tab delimited text files. When loaded, Excel auto-formats the data causing leading 0's on Zip Codes, Routing Numbers and other codes, to be chopped off. I don't have the patience to reformat all of the columns as text and guess how many zeros need to be replaced. Nor do I want to click through the import wizard an specify that each column is text. Ideally I just want to turn off Excel's Auto-Formatting completely, and just edit every cell as it were plain text. I don't do any formula's or charts, just grid plain text editing.

  Read the article

• Excel Help: Fill Tool - Drag to the side (across columns) but increase the formula by Row Number.

  - by B-Ballerl

  There are answers out there to this question, but all of them have been under explianed so hence to difficult to coprehend and use them to my advantage. I want to do the seemingly simple (but not) task of Draging a Formula (Filling a series) across Column's while increasing the formula row number relativley. For Example to drag this formula: | =A1 | =A2 | =A3 Some other notes, Transposing by copy paste has proven too difficult for the amount of data. Offset and Indirect has been used by other people to do this but I don't get how they work at all so when I attempt to use them I don't know how to format it to my range. Here's a example photo Idealy we want the dragged section to continue on to fill the formula.

  Read the article

• Need an excel macro to produce a formatted text file

  - by user139238

  I am just learning how to make macros and I found a macro that nearly does what I need it to do, which is output a text file from Excel. What I need it to do is output this in a .mhd format, which I have done, and then take all the data written in the #fnum cells and place a return after each in the Excel file. Essentially I just need all the data to have their a specific line in the text file. I am certain there is an elegant way to go about this, but I can't seem to get it. Sub CreateFile() Do While Not IsEmpty(ActiveCell.Offset(0, 1)) MyFile = ActiveCell.Value & ".mhd" 'set and open file for output fnum = FreeFile() Open MyFile For Output As fnum 'use Print when you want the string without quotation marks Print #fnum, ActiveCell.Offset(0, 5); " " & ActiveCell.Offset(0, 6); " " & ActiveCell.Offset(0, 7); " " & ActiveCell.Offset(0, 8); " " & ActiveCell.Offset(0, 9); " " & ActiveCell.Offset(0, 10); " " & ActiveCell.Offset(0, 11); " " & ActiveCell.Offset(0, 12); " " & ActiveCell.Offset(0, 13); " " & ActiveCell.Offset(0, 14); " " & ActiveCell.Offset(0, 15); " " & ActiveCell.Offset(0, 16); " " & ActiveCell.Offset(0, 17); " " & ActiveCell.Offset(0, 18); " " & ActiveCell.Offset(0, 19); " " & ActiveCell.Offset(0, 20); " " & ActiveCell.Offset(0, 21); " " & ActiveCell.Offset(0, 22); " " & ActiveCell.Offset(0, 23); " " & ActiveCell.Offset(0, 24); " " & ActiveCell.Offset(0, 25); " " & ActiveCell.Offset(0, 26) Close #fnum ActiveCell.Offset(1, 0).Select Loop End Sub

  Read the article

• Macro to open excel hyperlinks

  - by admintech

  I would like to know if anyone can help me do a macro to open a list of hyper links. I have a list of about 600 hyper links all in a column in separate rows, i have to open each one to see if the link works, yet it is boring and tedious as i have to wait sometimes for excel to catch up i would much rather leave it doing 100 at a time while i do other things

  Read the article

• Make Excel Defined Names within a worksheet to be global

  - by idazuwaika

  Hi, I wrote Powershell script to copy a worksheet from a workbook A to another workbook B. The worksheet contains define names for ranges within that sheet. Originally, the defined names are global in workbook A, ie. can be referenced from any worksheets within workbook A. But now, after copy to worksheet B, the defined names are limited to that worksheet only. How to I programmatically (via Powershell script preferably) make all those named range global i.e. can be referenced from all worksheets within workbook B. Some codes for clarity. #Script to update SOP from 5.1 to 5.2 $missing = [System.Type]::missing #Open files $excel = New-Object -Com Excel.Application $excel.Visible = $False $excel.DisplayAlerts = $False $newTemplate = "C:\WorkbookA.xls" $wbTemplate = $excel.Workbooks.Open($newTemplate) $oldSop = "C:\WorkbookB.xls" $wbOldSop = $excel.Workbooks.Open($oldSop) #Delete 'DATA' worksheet from old file $wsOldData = $wbOldSop.Worksheets.Item("DATA") $wsOldData.Delete() #Copy new 'DATA' worksheet to old file $wbTemplate.Worksheets.Item("DATA").Copy($missing,$wbOldSop.Worksheets.Item("STATUS")) #Save $wbOldSop.Save() $wbOldSop.Close() #Quit Excel $excel.Quit()

  Read the article

• Script/ Macro in Excel, by clicking on a cell

  - by Noob Doob

  There's something I want to do in Excel 2010. Specifically, I want to be able to make the open sheet load data from a text file, by clicking -important- on a cell of the current open worksheet. So my specific needs are: If it is possible in some way, to start a macro/script by clicking on a cell. About the script/macro: What would be the preferable, by your opinion, way of implementation. And more specifically, how to automatically import the data needed, only by clicking, without having to continually specify the desirable .txt file each time, only 1 time at max, at start or by using a custom "initalization/configuration" file.

  Read the article

• MS Excel Vba/Macro equivalent in LibreCalc or OpenOfficeCalc

  - by ReggieCL

  is there an equivalent macro/vba in libre calc that does this routine; - Read/open xls files in a path and do a batch import/copy of read sheets and merge it with the current open workbook. Here's the vba I used in MS Excel. Thanks in advance Sub Consolidate_Sheets() 'Folder Path to read the xlsx files from Path = "F:\WIP2\Below 25\" filename = Dir(Path & "*.xlsx") Do While filename <> "" Workbooks.Open filename:=Path & filename, ReadOnly:=True For Each sheet In ActiveWorkbook.Sheets 'import/copy sheets from to read xlsx files sheet.Copy After:=ThisWorkbook.Sheets(1) Next sheet Workbooks(filename).Close filename = Dir() Loop End Sub

  Read the article

• excel autocomplete combo-box with on-selection event

  - by IttayD

  Hi, I have an excel sheet for groceries. One column is the name, another is whether to buy it or not (checkbox) and another is the amount. I'd like to have a widget in the top row so that I start typing an item's name and it shows a list of matching items that I can select from, or if I continue to type and there's only one item, completes its name. When the last item is selected, other widgets show the amount, which I can edit and clicking 'check' will check the item in the list. I know this is kind of very specific, but am hoping someone can at least get me started. Thank you, Ittay

  Read the article

• Excel Single column into rows, VBA script insight

  - by Sanityvoid

  Okay, so much similiar to the below link but mine is a bit different. Paginate Rows into Columns in Excel I have a lot of data in column A, I want to take every 14 to 15 rows and make them a new row with multiple columns. I'm trying to get it into a format where SQL can intake the data. I figured the best way was to get them into rows then make a CSV with the data. So it would like like below: (wow, the format totally didn't stick when posting) column A column B C D etc 1 1 2 3 x 2 16 17 a b 3 x y z 15 16 17 a b c I can clarify if needed, but I'm stumped on how to get the data out of the single column with so many rows in the column. Thanks for the help!!!

  Read the article

• excel autocomplete combo-box with on-selection event

  - by IttayD

  I have an excel sheet for groceries. One column is the name, another is whether to buy it or not (checkbox) and another is the amount. I'd like to have a widget in the top row so that I start typing an item's name and it shows a list of matching items that I can select from, or if I continue to type and there's only one item, completes its name. When the last item is selected, other widgets show the amount, which I can edit and clicking 'check' will check the item in the list. I know this is kind of very specific, but am hoping someone can at least get me started. Thank you, Ittay

  Read the article

• Excel 2010: dynamic update of drop down list based upon datasource validation worksheet changes

  - by hornetbzz

  I have one worksheet for setting up the data sources of multiple data validation lists. in other words, I'm using this worksheet to provide drop down lists to multiple other worksheets. I need to dynamically update all worksheets upon any of a single or several changes on the data source worksheet. I may understand this should come with event macro over the entire workbook. My question is how to achieve this keeping the "OFFSET" formula across the whole workbook ? Thx To support my question, I put the piece of code that I'm trying to get it working : Provided the following informations : I'm using such a formula for a pseudo dynamic update of the drop down lists, for example : =OFFSET(MyDataSourceSheet!$O$2;0;0;COUNTA(MyDataSourceSheet!O:O)-1) I looked into the pearson book event chapter but I'm too noob for this. I understand this macro and implemented it successfully as a test with the drop down list on the same worksheet as the data source. My point is that I don't know how to deploy this over a complete workbook. Macro related to the datasource worksheet : Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) ' Macro to update all worksheets with drop down list referenced upon ' this data source worksheet, base on ref names Dim cell As Range Dim isect As Range Dim vOldValue As Variant, vNewValue As Variant Dim dvLists(1 To 6) As String 'data validation area Dim OneValidationListName As Variant dvLists(1) = "mylist1" dvLists(2) = "mylist2" dvLists(3) = "mylist3" dvLists(4) = "mylist4" dvLists(5) = "mylist5" dvLists(6) = "mylist6" On Error GoTo errorHandler For Each OneValidationListName In dvLists 'Set isect = Application.Intersect(Target, ThisWorkbook.Names("STEP").RefersToRange) Set isect = Application.Intersect(Target, ThisWorkbook.Names(OneValidationListName).RefersToRange) ' If a change occured in the source data sheet If Not isect Is Nothing Then ' Prevent infinite loops Application.EnableEvents = False ' Get previous value of this cell With Target vNewValue = .Value Application.Undo vOldValue = .Value .Value = vNewValue End With ' LOCAL dropdown lists : For every cell with validation For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation) With cell ' If it has list validation AND the validation formula matches AND the value is the old value If .Validation.Type = 3 And .Validation.Formula1 = "=" & OneValidationListName And .Value = vOldValue Then ' Debug ' MsgBox "Address: " & Target.Address ' Change the cell value cell.Value = vNewValue End If End With Next cell ' Call to other worksheets update macros Call Sheets(5).UpdateDropDownList(vOldValue, vNewValue) ' GoTo NowGetOut Application.EnableEvents = True End If Next OneValidationListName NowGetOut: Application.EnableEvents = True Exit Sub errorHandler: MsgBox "Err " & Err.Number & " : " & Err.Description Resume NowGetOut End Sub Macro UpdateDropDownList related to the destination worksheet : Sub UpdateDropDownList(Optional vOldValue As Variant, Optional vNewValue As Variant) ' Debug MsgBox "Received info for update : " & vNewValue ' For every cell with validation For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation) With cell ' If it has list validation AND the validation formula matches AND the value is the old value ' If .Validation.Type = 3 And .Value = vOldValue Then If .Validation.Type = 3 And .Value = vOldValue Then ' Change the cell value cell.Value = vNewValue End If End With Next cell End Sub

  Read the article

< Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >