Posts

number format

  Selection.NumberFormat = "0.00" --------------- =OFFSET(G1094,-1,0)/OFFSET(G1094,-12,0)-1 ---------------- Sub test() ' to select all sheets in the summary workbook Dim i As Long Sheet1.Select For i = 2 To ThisWorkbook.Sheets.Count     If Sheets(i).Name <> "Summary" Then Sheets(i).Select Replace:=False Next i End Sub  --------------- Sub LastRowInOneColumn()  'Find the last used row in a Column: column F in this     Dim LastRow As Long     With ActiveSheet         LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row     End With     MsgBox LastRow End Sub --------------------------------- Sub tests() ' to place vlookup in each and every sheet other than summary Dim i As Long Sheet1.Select For i = 2 To ThisWorkbook.Sheets.Count     If Sheets(i).Name <> "Summary" Then Sheets(i).Select Replace:=False     a = Range("f7").End(xlDown).Row     a = a + 1     Range("f" & a)
Post a Comment
Read more

For time calculation in macro

Dim shtcount, mshtcount, i, counter As Integer 'for timer Dim StartTime As Double Dim SecondsElapsed As Double 'Remember time when macro starts   StartTime = Timer 'code  SecondsElapsed = Round(Timer - StartTime, 2) MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation ----- '1) to add all excels to summary workbook which contains yes in summary sheet Sub DoThis()     Dim shSummary As Worksheet         Dim c As Range, wbk As Workbook         Set shSummary = Sheets("Summary")         With Summary         For Each c In shSummary.Range("B1:" & shSummary.Range("B1").End(xlDown).Address)             If UCase(c.Value) = UCase("YES") Then                 If dir("\\s\d\Desktop\ALL indexs\" & c.Previous & ".xlsx") <> "" Then                     Sheets.Add after:=Sheets(Sheets.count)        
Post a Comment
Read more

send Mail by outlook from excel sheet

Sub outlookmailxlsheet()     Dim rng As Range     Dim OutApp As Object     Dim OutMail As Object       Set rng = Nothing     Set rng = Sheets("YourSheet").UsedRange     Set OutApp = CreateObject("Outlook.Application")     Set OutMail = OutApp.CreateItem(0)     On Error Resume Next     With OutMail         .To = "XX@gmail.com"         .CC = ""         .BCC = ""         .Subject = "This is the Subject line"         .HTMLBody = RangetoHTML(rng)         .Send   'or use .Display     End With     With Application         .EnableEvents = True         .ScreenUpdating = True     End With     Set OutMail = Nothing     Set OutApp = Nothing End Sub Function RangetoHTML(rng As Range)     Dim fso As Object     Dim ts As Object     Dim TempFile As String     Dim TempWB As Workbook     TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") &
Post a Comment
Read more

Hide sheet, filter data, fill formula, select visible cells

To add sheets: Sheets.Add after:=Sheets(Sheets.count) ---------------------------------------- Sub togetsheenames() Dim i As Integer Dim sh As String For i = 1 To Sheets.Count sh = Sheets(i).Name Sheets("ToDelete").Range("A" & i) = sh Next End Sub -------------------------- fill lookup formula  Range("D3").Select ActiveCell.Formula = "=VLOOKUP(A3,Exp!$B:$E,4,0)"     Range("d3").AutoFill Range("d3:d" & Range("a1").End(xlDown).Row) ------------------ Hide sheet Sheets("Sheet2").Visible = True unhide sheet  Sheets("Sheet2").Visible = Flase --------------------------------------------- For i = 2 To drcount mystng = Sheets("Counter").Range("A" & i).Value LR = Sheets("Rough").Range("A1").CurrentRegion.Rows.Count + 1 Sheets(2).Select Sheets(2).Rows("3:3").AutoFilter Field:=26, Criteria1:=m
Post a Comment
Read more

To know last rows, columns

Sub FindingLastRow() 'PURPOSE: Different ways to find the last row number of a range Dim sht As Worksheet Dim LastRow As Long Set sht = ActiveSheet 'Using Find Function (Provided by Bob Ulmas)   LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row 'Using SpecialCells Function   LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row 'Ctrl + Shift + End   LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 'Using UsedRange   sht.UsedRange 'Refresh UsedRange   LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row 'Using Table Range   LastRow = sht.ListObjects("Table1").Range.Rows.Count 'Using Named Range   LastRow = sht.Range("MyNamedRange").Rows.Count 'Ctrl + Shift + Down (Range should be first cell in data set)   LastRow = sht.Range("A1").CurrentRegion.Rows.Count End Sub ######################################################
Post a Comment
Read more

Find and copy the data

Sub CLE() 'ONEYEAR DATA Sheets("Sheet2").Select 'Range("j10: L110 ").ClearContents 'FSTR = InputBox("Enter the date.. You wanted to See !") lastrow = Sheets("sheet2").UsedRange.Rows.Count On Error GoTo sant For I = 6 To lastrow If Range("C" & I) <> "" Then fstr = Sheets("sheet2").Range("c" & I).Value Sheets("Sheet1").Select             Cells.Find(What:=fstr, After:=ActiveCell, _             LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _             SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate             'ActiveCell.CurrentRegion.Select A = ActiveCell.Row 'MsgBox A + 2 A = A + 2 Range("f" & A).Select     Selection.Copy     Sheets("Sheet2").Select     Range("e" & I).Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _      
Post a Comment
Read more

Filter Data

to filter the data Sub Analyst() Dim role As String role = Sheets(1).Cells(3, 2).Value Sheets(2).AutoFilterMode = False  Sheets(2).UsedRange.AutoFilter Field:=11, Criteria1:="=*" & role & "*" 'Operator:=xlAnd, Field:=9, Criteria2:="<>*" & role & "*" 'Sheets(2).UsedRange.AutoFilter Field:=11, Criteria1:="*"role, Operator:=xlAnd, Field:=9, Criteria2:="<>*role*" Sheets(2).UsedRange.AutoFilter Field:=9, Criteria1:="<>*" & role & "*" Sheets(2).UsedRange.Copy Sheets(1).Range("a11") Sheets(1).AutoFilterMode = False End Sub
Post a Comment
Read more