• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA help to find and move text to a new worksheet

M

mohanyathish

New Member
Hi,


I have the following data in Sheet1


Content Number of Mentions

"Mary" is a wonderful name. 15

Mark was killed in an accident 12

Mark and Mary work together 8

Mary is married. 7

Mark has a doctorate in English 5

Honey is sweet. 4

Mary is happy. 3

"It is a wonderful day," said Mary. 2


I need help on a macro to find the "Top 3" number of mentions (In descending order) of "Mary" and "Mark", and move them into a new worksheet. The results will be as below


Content Number of Mentions

"Mary" is a wonderful name. 15

Mary is married. 7

Mary is happy. 3


Content Number of Mentions

Mark was killed in an accident 12

Mark and Mary work together 8

Mark has a doctorate in English. 5


Thanks in advance for your help.!
 
If you have a limited data or a one time task, you can use filter option, sort by numbers and filter by name....


With names listed you can use LARGE(Range,no) to get the top 3 results.


If you want to have some VBA involved, it would be better if you post a sample workbook...
 
mohanyathish,


When I read the first half of your post, things were very clear to me. But when I read your expected result part, I got little confused.


According to me the top 3 result should be:


"Mary" is a wonderful name. 15

Mark was killed in an accident 12

Mark and Mary work together 8


Could you plz explain why do you break the result into two parts, instead?


Kaushik
 
Hi Kaushik,


Thanks for the reply..


The first set is the top 3 results for "Mary"


The second set is the top 3 results for "Mark"


Hope I am making myself clear now...!
 
Hello everybody,


Recently at work we have been "upgraded" from XP to Win7 and from MS Office 2003 to MS Office 2010 but, as normal, not all users; this is made gradually since we are many.


Myself, I am working with an Excel file daily so I hit a bump:

there is a file that stores all that I work using a macro but it can not work on all files (something is changed due to the file being modified with 2003 version and then passed to 2010) so I have made myself another code that takes what I need from the file and puts it into a new & fresh file.


My need for your assistance is whether it can be simplified (thus me gaining some precious seconds - a lot of files per day = quite some time spared /or lost).


Here is my code:

"Sub TEST()

'

' TEST Macro

'


'open new file

Workbooks.Open Filename:= _"http:file_location"


'go to required sheet (my case "Form")

Sheets("Formulaire - Form").Select

'actual code to be shrinked

Rows("59:71").Select

Selection.EntireRow.Hidden = False

ActiveWindow.SmallScroll Down:=-72

Range("E9:G9").Select

ActiveWindow.ActivateNext

Range("E9:G9").Select

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E9:G9").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E11:G11").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E11:G11").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E13:G13").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E13:G13").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E15:G15").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E15:G15").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E17:G17").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E17:G17").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=12

ActiveWindow.ActivateNext

ActiveWindow.SmallScroll Down:=12

Range("E19:G19").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E19:G19").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E21:G21").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePreviousr />
Range("E21:G21").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

ActiveWindow.SmallScroll Down:=12

Range("E28:G28").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

ActiveWindow.SmallScroll Down:=6

Range("E28:G28").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E30:G30").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E30:G30").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E32:G32").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E32:G32").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E34:G34").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E34:G34").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E36:G36").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E36:G36").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=12

ActiveWindow.ActivateNext

Range("E38:G38").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.SmallScroll Down:=9

ActiveWindow.ActivatePrevious

Range("E38:G38").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

ActiveWindow.SmallScroll Down:=9

Range("E48:G48").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E48:G48").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E50:G50").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

ActiveWindow.SmallScroll Down:=9

Range("E50:G50").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E52:G52").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E52:G52").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E54:G54").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E54:G54").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

ActiveWindow.SmallScroll Down:=9

Range("E63").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

ActiveWindow.SmallScroll Down:=6

Range("E63").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivatePrevious

ActiveWindow.ActivateNext

ActiveWindow.ActivateNext

Range("G63").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("G63").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("C65").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("C65").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("D65").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("D65").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E65").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E65").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("F65").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("F65").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("G65").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("G65").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("E67:K67").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E67:K67").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("A72:F95").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.SmallScroll Down:=15

ActiveWindow.ActivatePrevious

ActiveWindow.SmallScroll Down:=15

Range("A72:F95").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

Range("G72:p95").Select

Application.CutCopyMode = False

Selection.Cut

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("G72:p95").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=15

ActiveWindow.ActivateNext

ActiveWindow.SmallScroll Down:=18

Range("E97:F97").Select

Application.CutCopyMode = False

Selection.Copy

ActiveWindow.ActivatePrevious

Range("E97:F97").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.ActivateNext

ActiveWindow.Close

End Sub"

Thank you in advance for your help !
 
You must log in or register to reply here.
Back
Top