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 _
        :=False, Transpose:=False
    ActiveWorkbook.Save
    'Range("L11") = fstr
sant:
    End If
    Sheets("sheet2").Select
    Next
   
   
End Sub

Comments

Post a Comment

Popular posts from this blog

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 ...
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 Fun...
Read more