Sub EE_AddCalculatedColumn(rngColumn As range, strFormula As String, strNewHeading As String) Dim rng As range 'http://excelexperts.com/xla-routines-eeAddCalculatedColumn With rngColumn Set rng = range(.Cells(2), .Cells(EE_GetLast
Sub EE_CloseOtherWorkbooks(wbKeepOpen As Workbook) Dim wbk As Workbook 'http://excelexperts.com/xla-routines-eeCloseOtherWorkbooks For Each wbk In Application.Workbooks If wbk.Name <> wbKeepOpen.Name Then wbk.Close False End If Next wbk Set wbk = Nothing End Sub
Function EE_FileNameFromFilePath(strFilePath As String) As String 'http://excelexperts.com/xla-routines-eeFileNameFromFilePath EE_FileNameFromFilePath = Mid(strFilePath, InStrRev(strFilePath, Application.PathSeparator) + 1) End Function
Sub EE_ApplyPivotDataFormatting(pt As PivotTable, FormattingRange As range) '- takes a 2 col range, and applies the formatting specified in col 2 to the Data fields. Dim rngCell As range 'http://excelexperts.com/xla-routines-eeapplypivotdataformatting If FormattingRange.Rows.Count <> 2 Then Exit Sub For Each rngCell In FormattingRange.Rows pt.PivotFields(rngCell.Cells(, 1).value).NumberFormat = rngCell.Cells(, 2).value Next rngCell Set rngCell = Nothing End Sub
Function EE_GetLastPopulatedCell(wks As Worksheet) As range '- Works how specialcells (lastCell) SHOULD work '- Returns single cell range Dim lngCol As Long Dim lngMaxRow As Long Dim lngRow As Long Dim lngMaxCol As Long 'http://excelexperts.com/xla-routines-eeGetLastPopulatedCell For lngCol = 1 To wks.UsedRange.SpecialCells(xlCellTypeLastCell).Column 'wks.UsedRange.Columns.Count lngMaxRow = Application.WorksheetFunction.Max(lngMaxRow, wks.Cells(Rows.Count, lngCol)
Function EE_ReplaceSheet(strSheet As String) As Worksheet Dim wksNew As Worksheet Dim wbk As Workbook Dim blnDisplayAlerts As Boolean Set wbk = ActiveWorkbook On Error Resume Next Set wksNew = wbk.Worksheets.Add(after:=wbk.Worksheets(strSheet)) Err.Clear: On Error GoTo 0: On Error GoTo -1 If wksNew Is Nothing Then Set wksNe
Sub EE_DeleteSheets(ArrayOrRange) Dim blnDisplayAlerts As Boolean Dim arr Dim wbk As Workbook 'http://excelexperts.com/xla-routines-eedeletesheets Set wbk = ActiveWorkbook Select Case TypeName(ArrayOrRange) Case "Variant()", "String" arr = ArrayOrRange Case "Range" arr = Application.Transpose(ArrayOrRange.SpecialCells(xlCellTypeConstants))
Public lngCalc As Long Sub EE_End() 'http://excelexperts.com/xla-routines-eeend Application.EnableEvents = True Application.DisplayAlerts = True Application.Calculation = lngCalc End Sub
Public lngCalc As Long Sub EE_Start() 'http://excelexperts.com/xla-routines-eestart Application.EnableEvents = False Application.DisplayAlerts = False lngCalc = Application.Calculation Application.Calculation = xlCalculationManual End Sub
Please help me. I am working in vba last two years and now i got some development tool for my friend. He asked me to create employee database application in excel. so i decide to create in excel user form and database with ms access.
can store huge data in msaccess and excel form will be reliable. help me.
i want to appear expire date in day.., question is "sheet2=mly or qly or sly or yly, sheet3=act_date, sheet4=expired date" i need to appear if sheet2=mly then add 30 days, and appear how many days remaining to expired in sheet4 automatically from today, and if sheet2=qly + 90 days, and appear how many days remaining to expired like that,,, and sly=180 days and yly=360 days, so how can i make this report,,,,
Stamp Duty Changes
I need to analyse the expenses month over month as attached in the sheet.Please let me know some ways in excel available
I have many rows of data in sheet 2 of the workbook which is made up of 4 columns of text and numerical data.
What I would like is for the operator to select a record using a drop-down menu in a cell and all of the data appertaing to that record be copied onto sheet 1, is this possible?
My blank workbook is now so huge when I open it up. What happened?...and how do I fix it?
have gotten very frustrated with a workbook/page issue in what i call a WOTC (Waste Of Time Challenge )... so here i go...
on the left column i have 60 different names with 10 more columns named differently, say A to J labeled from date 'net'. then there are 12 pages total to set up the workbook. then what i want to be able to do is to pull out one of the 60 names and 'stack' that row from each of the 12 pages to make a page of summary for that row name... so to say, an individual worksheet for that line...
to WOTC or not to WOTC...
that is my question...
Can somebody help me with the following query... This code will find last row of a sheet..but do not want to repeat for every sheet.
Dim lrow as long
Lrow=range ("a"& rows.count).end (xlup).row
Please help me to create function which can be used for any sheet..Thanking you in Advance.
Hi, could anybody please write a vba code for creating text format?
ex: the cell should be of 11 digits which first 4 digits should be alphabet followed by 0 and rest of the digits are numeric (SBIN0123456). If anything typed wrong should be alerted with error message.
Mansion Tax Analysis in Excel - Labour's nationalisation of London homes
Today, we'll use Excel to look at the impact of Labour's Proposal for Mansion Tax for properties over 2 million,and demonstrate some interesting findings.
The Labour party is proposing a tax on properties over 2 million. They label this a "Mansion Tax". The idea is that wealth is redistributed from the "Mansion" owners, and used to fund the government's overspending.
I want formulas that will calculate a summary of averages from the timesheet attached(actual spreadsheet has over 50,000 rows with 12 employee). My aim is to be able to see, when I expand each subtotal/month, the average hours worked per day/week/month for the month/subtotal and in the same manner as the number of counts for all employees - summarised below the table (highlighted yellow).
In other words, each subtotal will have its summary of averages below the table when I collapse it. Cells without data should be ignored in the formula
I know that Excel doesn't allow you to freeze one tab in the first position (i.e. making sure that a user cannot insert/move another sheet to the first position). Are you able to offer any suggestions on some VBA code that will do this for me please? I'm stuck!