Copy data from one sheet to other

[aakhan2011]

Data (i.e. 34864) in cell A110 of "Mod" sheet needs to be copied to Cell B9 of "Email Data" sheet.

Logic for copying is -
The VBA code will search for "COUNT(DISTINCTM.TRANS_ID)" keyword in "Mod" sheet and when found will move to next two row down and copy data(integer value 34864 / or any other value which is present) and paste in Cell B9 of "Email Data" sheet.

Sheet attached for your reference.

Worksheet.xlsm

Any help will be highly appreciated.

Button in "Email Data" sheet will do this function

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Cell As Range
    With Worksheets("Mod")
        Set Cell = .Cells.Find(What:="COUNT(DISTINCTM.TRANS_ID)", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
        If Not Cell Is Nothing Then
            Worksheets("Email Data").Range("B9").Value = Cell.Offset(2).Value
        End If
    End With
End Sub

 

[aakhan2011]

Try:

Sub Test()
    Dim Cell As Range
    With Worksheets("Mod")
        Set Cell = .Cells.Find(What:="COUNT(DISTINCTM.TRANS_ID)", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
        If Not Cell Is Nothing Then
            Worksheets("Email Data").Range("B9").Value = Cell.Offset(2).Value
        End If
    End With
End Sub

Works fine .

But When i use the same code for searching COUNT(*) , it doesn't does. May be (*) is causing the problem.

Can you please check, we have the COUNT(*) in cell A114 in "Mod" sheet as provided above.

Sub Test2()
Dim Cell As Range
   With Worksheets("Mod")
        Set Cell = .Cells.Find(What:="COUNT(*)", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
        If Not Cell Is Nothing Then
            Worksheets("Email Data").Range("D9").Value = Cell.Offset(2).Value
        End If
    End With

End Sub

 

[Andrew Poulsom]

The asterisk is a wildcard so you need to escape it with a tilde:

What:="COUNT(~*)"

 

[aakhan2011]

The asterisk is a wildcard so you need to escape it with a tilde:

What:="COUNT(~*)"

Even the ~ symbol doesn't work. Can you please check in the sheet i have attached in my first post and sent the file or let me know.

Sub Test2()
 Dim Cell As Range
    With Worksheets("Mod")
        Set Cell = .Cells.Find(What:="COUNT(~*)", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
        If Not Cell Is Nothing Then
            Worksheets("Email Data").Range("D9").Value = Cell.Offset(2).Value
        End If
    End With

End Sub

Waiting for the reply ..

 

[Andrew Poulsom]

It does work, but the COUNT(*) in B2 is found first. Try:

Set Cell = .Columns(1).Find(What:="COUNT(~*)", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)

 

[aakhan2011]

It does work, but the COUNT(*) in B2 is found first. Try:

Set Cell = .Columns(1).Find(What:="COUNT(~*)", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)

You are a wow, it works great.

 

[aakhan2011]

[Solved]Copy data from one sheet to other

This is solved now.