Free Microsoft Excel 2013 Quick Reference

VBA Macro Code To Search For Text Then Copy Data From Its Row

I am new to excel VBA and need some help on gathering data from a table so I can compare it later. The sheet I am using has over 500 parts on it and the format is similar to the following excel document. I need the Sub to search for a specified "Serial Number" and get the data from that row, columns D through F (keep it as one array for later comparison) and the data in the exact locations just down one row until a new serial number is reached.

For example:

If I look up serial number "2222", I need this information:
Array 1: Outside 39 Square
Array 2: Outside 12 Triangle
Array 3: Inside 71 Circle

However, If I look up serial number "3333", I only need:
Array 1: Inside 7 Square

Hope that you can help me or send me in the right direction!

http://www.excelforum.com/excel-work...13#post2800013


Post your answer or comment

comments powered by Disqus

Related Results

VBA code to search for and delete Form Controls on an Excel 2007 spreadsheet

Hello,

I am looking to modify the code below to search for a TEXT string rather than the row range as it appears.

Sub GroupingbyvendorName()
'
' GroupingbyvendorName Macro
'
' Keyboard Shortcut: Ctrl+c

' this is the line I'd like to change to search for text string with wildcard rather than row/cell reference
    Range("B145:B166").Select
    Selection.Group
    ActiveSheet.PivotTables("PivotTable").PivotFields("Vendor Name2").PivotItems( _
        "Group1").Caption = " Accenture"
    Range("B168:B180").Select
    Selection.Group
    ActiveSheet.PivotTables("PivotTable").PivotFields("Vendor Name2").PivotItems( _
        "Group2").Caption = " Accordo "
    Range("B195:B208").Select
    Selection.Group
    ActiveSheet.PivotTables("PivotTable").PivotFields("Vendor Name2").PivotItems( _
        "Group3").Caption = " Action"

End Sub
I'm hoping to have this Macro search for "name*": any name that contains the text and group them.

I'm not sure if you can use * in VBA but any help on this work be highly appreciated!

Thank you,
dan

I would like to search for text across multiple worksheets and summarize the entire row in one worksheet. In my example, I am looking to find all instances where the words "change order" appears, and then copy the entire row into the "Summary" tab so that I can sum the change orders. The attached example is a simplified version, and the actual spreadsheet contains at least 50 different worksheets with various titles.

Any help would be greatly appreciated!

I use the following code to search for the next cell with a zero. But I found it stops if the value has zero contained such as 8.04, and I would like it to stop only when the value is "0".

The cell formula is an indirect reference such as =INDIRECT($A192&J$187) in which there is no data yet, so the cell shows 0

Cells.Find(What:="0", after:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
Is there a way to have it pass over 8.04 and stop at the cell that has only a zero contained?

I am new to excel VBA and need some help on gathering data from a table so I can compare it later. The sheet I am using has over 500 parts on it and the format is similar to the following excel document. I need the Sub to search for a specified "Serial Number" and get the data from that row, columns D through F (keep it as one array for later comparison) and the data in the exact locations just down one row until a new serial number is reached.

For example:

If I look up serial number "2222", I need this information:
Array 1: Outside 39 Square
Array 2: Outside 12 Triangle
Array 3: Inside 71 Circle

However, If I look up serial number "3333", I only need:
Array 1: Inside 7 Square

Hope that you can help me or send me in the right direction!

Hi there!
I would like to use VBA to search a folder and copy data from tabs within the excel files there. The data will be pasted to a tab of same name in the the main file. All the files are in the same format.
So far I have only managed to list the files in the folder using code I found on your site!
Can you help please?

Hi

Nice to meet you all here. I am a newbie in vb and also a new member to Excel Forum.
I have encountered some difficulties in creating macro in excel where I need to copy data from Sheet1 row A1:C3 into Sheet2 column A1:C3 as well. Which means Sheet1 is a table whereas Sheet2 is a form. The idea now is to copy the data horizontally(row) become vertically(column) in each wordsheet respectively. Attached is the sample showing the simple requirement. Appreciate helps from anyone. Thanks.

Hello,

I hate to have my first post on a forum be crying for help, but to be honest I know NOTHING about macros so here goes.

I want to copy a selected cell, search another page for that value/string, when found, copy the cell to its right, then paste that value/string to the right of the originally selected cell.

The problem I am getting is the code is telling Excel to search for "text" which is fine, but when i run the macro from another cell, it keeps that "text" from the previous and searches for that. Here is my code:
Sub test()
'
' test Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveCell.Select
    Selection.Copy
    Sheets("WWN's").Select
    Cells.Find(What:="10:00:00:00:C9:39:49:FC", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Edge_01").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
End Sub
The "What" is what needs to be changed somehow to whatever value is in the cell i select first then run the macro.

Thanks!

Hello, I am new to this forum, and am just starting to get involved heavily in Excel (business functions).
I am looking for VBA code to search a worksheet for a specific word, and then return corresponding values next to that text. I have attached a sample. I am new to VBA, and tried to google/search the coding, but to no avail. Please see the attached spreadsheet, I have added notes in it.
Thank you in advance.

Hello forum,
I'm hoping someone will be able to help me with this. I am a novice and havent used loops in vba before. I suspect using a for/next loop will be the solution to the problem I am about to describe.

It's a bit of a convoluted problem, but the data output from the Macro needs to be entered in a certain format in order for it to be useful in the program I intend to use it in.

I have attached a spreadsheet of my data to make description easy:

What I'd like the Macro to do is:
1) Extract the entry from sheet 1 cell C2 and D2, then paste it in C1 and F1 on sheet 2 (respectively)
2) Extract the entry on sheet 1 cell E2 and F2 and paste it in C2 and F2 on sheet 2
3) Fill sheet 2 cell D1 and G1 with 3 (number) and D2 and G2 on the same sheet with 0 (number)
4) Fill sheet 2 cell H1 and H2 with -1 (number)
5) Fill sheet 2 A1 with the text string MPC
6) If the number in sheet 2 cell C1/F1 begin with 5, B1 etc should be 101, otherwise, it should be 102

A Sample of the (one) final entry for each line is shown in the sheet “sample”; basically, I want the code to repeat for all values in sheet 1 and stop

Any help will be appreciated.

Thanks

Here is what Im trying to do: I have a VERY large excel file (15,000+ lines) that has groups of text (this is all in one column)

I want excel to search for the cells with "UID" and delete that row plus every non-blank row above it. (so it deletes the entire "group") Here is example layout of what Im working with. The end result should be only "groups" without the text "UID" following it.

ExampleCell1
ExampleCell2
ExampleCell3
UID = example

ExampleCell1
ExampleCell2
ExampleCell3

ExampleCell1
ExampleCell2
ExampleCell3
UID = example

Now Im thinking this may be impossible, but I've seen some crazy things done with excel macros and was really hoping someone can help me out. Otherwise Im doing this manually for 15,000 lines of text.

Any suggestions are GREATLY appreciated!!!!!!!

I will gladly transfer $30 to the first person to solve this and has a PayPal account!

I am not sure how to program a macro to search for a cell if that cell is a text cell, ie if the cell 00-DIV-00443 (a barcode)

The hope is to move towards autoreporting. The user inputs the barcode at the start of the process and the macro runs and pulls back the data that corresponds to that barcode from access.

I have attached a folder Data Pull.zip with a pretty Explanatory excel file and a database to practice on. The real database is much larger than the one in the folder but the code should be the same.

Any help would be appreciated.

Stephen

PS. The macro I am currently using to attempt to retrieve the barcode data, comes back with Error Copying data???

I want to have a command button in an independent excel worksheet which will execute a macro which in turn would search for a particular text string such as “Error” within a selection of independent excel files residing in a local directory such as C: and return a Pass/Fail value when it finds such a string in the collection of excel files.

Example:
Command button Cmd_1 in “Sheet1” of an excel sheet (template) will fire a macro to search for a text string such as “Error” in two independent files (Data1.xls, Data2.xls) and return True/False depending upon whether the string “Error” is found in either of them.

How do I come up with a code for the above? What search functions could I use?

Thanks

Hello,

I'm a bit new to vba for excel but have a bit of experience with other programs. I am trying to search for text from Column A in Column B (which contains longer pieces of text) and if it hits a match copy the information from Column C and put it in the same Row as the search from Column A. I know I need to run a loop and I don't think a formula would solve my issue so VBA may be the answer.

I have attached a demo workbook that has all of the columns truncated at 500 rows. The real data goes to at least 6000 rows for columns B and C. I color coded the process from light blue to dark blue...if that helps.

Could someone please assist me in developing a macro to do what is explained below. Your assistance is greatly appreciated! Thanks so much in advance!!

Select Sheet Raw Data

Search for the first active cell in column M

Within that row containing the active cell in column M:
Copy and Paste the following cells onto a new sheet in the first blank row on Sheet “Research”

-Value in column A (from sheet Raw Data) gets pasted into column A on the first blank row (paste into sheet Research)
-Value in column F (from sheet Raw Data) gets pasted into column C on the first blank row (paste into sheet Research)
-Value in column M (from sheet Raw Data) gets pasted into column B on the first blank row (paste into sheet Research)

The macro would then loop and continue to search for values in column M and repeat the above steps

Thanks Again!

Hey guys,

Im a bit stuck here and need your very valuable help.

I am trying to write some code and im having difficulties. I would like to be prompted to give values (numbers), and then those values to be pasted in specific cells on a different specified workbook.
I want my original data kept and the new workbook to be saved under a different name.

Thank you in advance,

Any help would be greatly appreciated!

Giannis

I have a spreadsheet with several worksheets. I have created a macro that will run when a button is pressed that will remove two columns of data from each page, then move the focus back to the first page, and what I don't have working right now is some code to add to the macro that fun "File - Save as and then save the file in the same directory that the original file is in and just add " - Sanitized" to the end of the file name. I.e. If it was "file.xls" before, I want it to be "file - Sanitized.xls". Here is the code I have so far:

Sub Sanitizer()
'
' Sanitizer Macro
'
'
Sheets("xxx's lates").Select
Columns("B:C").Delete Shift:=xlToLeft
Sheets("xxx's lates").Select
Columns("B:C").Delete Shift:=xlToLeft
Sheets("xxx's lates").Select
Columns("B:C").Delete Shift:=xlToLeft
Sheets("xxx's lates").Select
Range("A1").Select

ActiveWorkbook.SaveAs Filename:= _
"M:Administrative Itemsxxxx-Sanitized.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

The first part works well, but I tried to record a macro to do the last part and it not going to work as soon as the current file location moves, or the name changes. So I just need it to append the current name and use the current directory.
Thanks in advance for your help. I did try to search for this but could not find it anywhere.

Hi
I am working on a hospital database and would like to search for a particular ID number that belongs to a doctor and print the ID number plus the Name the ID number belongs to after that it should prompt the user to update the record, at the moment I can only print out the ID number, here is the code:


	VB:
	
 cmdOK_Click() 
    Dim iRow As Long 
    Dim ws As Worksheet 
    Set ws = Worksheets("Doctors Information") 
     'Find first empty row in database
    iRow = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row 
    If WorksheetFunction.CountIf(ws.Range("E3", ws.Cells(iRow - 1, 1)), Me.TextBox1.Value) > 0 Then 
        MsgBox "ID number " & Me.TextBox1.Value & "with the name." 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


This is my first time post, so I wasn’t sure if this was the right place to post, sorry.

I have one Excel column (C) with cells containing known text that appears randomly throughout the column. I would like to search for these cells containing this text and then place three empty rows directly below them, in each instance.

Then if possible, after the three empty rows (so on the next row with text column C), I would like to insert a day of the week. I would like this day of the week to be inserted into column A. This column will only contain the days of the week. I would like the days to begin with Monday. So, the first search will place Monday into column A cell.

Thanks for your help

Morning folks

Am looking for a wee addition to a code, which should be really simple for you experts I hope? ;-)

I have a spreadsheet which I am cataloguing entries on, by using keyword tags in column E. I have the script below which opens an input box, allows a search string to be input, searches Sheet1 column E and copies (the whole line) of any instances of the keyword that it finds. However. I have hit upon a problem, when there is more than one keyword tag in the cell. It finds neither? Is there a way I can have multiple entries in this column and have the search code find them individually? ie if column E has "Soul; Rap" entered in it, and the search term is "Rap" - it will find this as well as the instances where "Rap" is the only entry in the column?

Code is below:

Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    Dim LSearchValue As String

    On Error GoTo Err_Execute
    
Application.ScreenUpdating = False
    
    Sheets("Search Results").Select
    Range("A2:E1500").Clear
    
    Sheets("Sheet1").Select
    LSearchValue = InputBox("Please enter the tag you wish to search for.", "Enter value")

    'Start search in row 4
    LSearchRow = 4

    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 2

    While Len(Range("A" & CStr(LSearchRow)).Value) > 0

        'If value in column E = LSearchValue, copy entire row to Sheet2
        If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then

            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy

            'Paste row into Sheet2 in next row
            Sheets("Search Results").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste

            'Move counter to next row
            LCopyToRow = LCopyToRow + 1

            'Go back to Sheet1 to continue searching
            Sheets("Sheet1").Select

        End If

        LSearchRow = LSearchRow + 1

    Wend

    'Position on cell A3
    Application.CutCopyMode = False
        Sheets("Sheet1").Select
    Range("A4").Select
    Sheets("Search Results").Select
    Range("A2").Select

    MsgBox "All matching data has been copied."

    Exit Sub
Any help would be much appreciated

Hey guys.

I working on a macro which will search for files which contains terms which I have in a list in my sheet. The macro so far is a slight modification of one I found on this forum.

So I got the searching bit of the macro working fine, but I cannot figure out how to make the macro copy the file it has found to a new location on my HD.

For the copying, it would be great if it could use the file path inserted into any given cell (lets say A1) and use the original file name.

Any help would be greatly appreciated

Sub Button6_Click()

' Uses DIR rather than FileSearch which was dropped in Excel 2007

Dim myFile As String
Dim myPath As String
Dim myFilter As String

' set default folder

myPath = "C:Excel file"

' pick up search string from active cell

myFilter = "*" & ActiveCell.Value & "*" & ActiveCell.Offset(0, 1).Value &
"*.xls"

' look for file

myFile = Dir(myPath & myFilter)

' open file if found

If myFile <> "" Then

    Workbooks.Open (myFile)

Else

    MsgBox "No file found!", vbInformation

End If

End Sub


I've got my vba/macro code running correctly in one workbook. What is the best way to add/attach that code to many workbooks? (Use a template?)

The code is linked to a button and I don't want to add the code and button manually to many workbooks.

Any ideas are greatly appreciated. Thank you.

Hi,

I have set up some code to search for a value, in this case a number, copy all matching data to another sheet, edit it then copy some values to another sheet. I then want to go back to the original sheet and search for a different value and do similar actions then onto the third and fourth.
The code i have works fine for the first value then does nothing for the rest.
I have tried searching and i can't find anything on this does anyone have any ideas??

the code i have is

	VB:
	
Sheets("SAP data").Select 
 'to copy 30010's time
Dim LSearchRow As Integer 
Dim LCutToRow As Integer 
 'Start search in row 1
LSearchRow = 1 
 'Start copying data to row 2 in Sheet1 (row counter variable)
LCutToRow = 1 
While Len(Range("A" & CStr(LSearchRow)).Value) > 0 
     'If value in column A = "00030010", copy entire row to Sheet1
    If Range("A" & CStr(LSearchRow)).Value = "00030010" Then 
         'Select row in SAP data to copy
        Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select 
        Selection.Cut 
         'Paste row into Sheet1 in next row
        Sheets("Sheet1").Select 
        Rows(CStr(LCutToRow) & ":" & CStr(LCutToRow)).Select 
        ActiveSheet.Paste 
         'Move counter to next row
        LCutToRow = LCutToRow + 1 
         'Go back to SAP data to continue searching
        Sheets("SAP data").Select 
    End If 
    LSearchRow = LSearchRow + 1 
Wend 
 'Position on cell A3
Application.CutCopyMode = False 
Range("A3").Select 
 'to edit their data
Sheets("Sheet1").Select 
 'convert time
Columns("J:J").Select 
Application.CutCopyMode = False 
Selection.Insert Shift:=xlToRight 
Range("J1").Select 
ActiveCell.FormulaR1C1 = "=RC[-1]/24" 
Selection.AutoFill Destination:=Range("J1:J100"), Type:=xlFillDefault 
Columns("J:J").Select 
Selection.NumberFormat = "h:mm" 
 'remove index from part number
Columns("F:F").Select 
Application.CutCopyMode = False 
Selection.Insert Shift:=xlToRight 
Columns("F:F").Select 
Selection.NumberFormat = "General" 
Range("F1").Select 
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1,LEN(RC[-1])-4)" 
Selection.AutoFill Destination:=Range("F1:F100"), Type:=xlFillDefault 
 'combine material and op numbers
Range("G1").Select 
Do While ActiveCell  "" 'Loops until the active cell is blank
    ActiveCell.Offset(0, 7).Formula = _ 
    ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0) 
    ActiveCell.Offset(1, 0).Select 
Loop 
Columns("B:I").Select 
Selection.Delete Shift:=xlToLeft 
Columns("D:E").Select 
Selection.Delete Shift:=xlToLeft 
Rows("1:1").Select 
Selection.Insert Shift:=xlDown 
Range("A1").Select 
ActiveCell.FormulaR1C1 = "CC" 
Range("B1").Select 
ActiveCell.FormulaR1C1 = "OT" 
Range("C1").Select 
ActiveCell.FormulaR1C1 = "NT" 
Range("D1").Select 
ActiveCell.FormulaR1C1 = "PON" 
Range("D2").Select 
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
"Sheet1!R1C1:R101C4").CreatePivotTable TableDestination:= _ 
"'[T5 matrix1.xls]Sheet1'!R1C6", TableName:="PivotTable3", DefaultVersion:= _ 
xlPivotTableVersion10 
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _ 
"PivotTable3").PivotFields("NT"), "Count of NT", xlCount 
With ActiveSheet.PivotTables("PivotTable3").PivotFields("PON") 
    .Orientation = xlRowField 
    .Position = 1 
End With 
Range("F1").Select 
ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of NT").Function = _ 
xlSum 
Range("G3:G30").Select 
Selection.NumberFormat = "h:mm" 
Columns("A:D").Select 
Selection.Delete Shift:=xlToLeft 
 
 
Dim rngCell As Range 
 
Sheets("Sheet1").Select 
On Error Resume Next 
Set rngCell = _ 
Cells.Find(What:="915833 0010", After:=ActiveCell, LookIn:=xlFormulas, _ 
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
MatchCase:=False, SearchFormat:=False) 
Err.Clear: On Error Goto -1: On Error Goto 0 
If Not rngCell Is Nothing Then 
    rngCell.Activate 
    ActiveCell.Offset(0, 1).Copy 
    Sheets("Skill matrix").Select 
    Cells.Find(What:="30010", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
    False, SearchFormat:=False).Activate 
    ActiveCell.Offset(5, 0).Activate 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:= _ 
    False, Transpose:=False 
    Set rngCell = Nothing 
End If 
 
 
Sheets("Sheet1").Select 
On Error Resume Next 
Set rngCell = _ 
Cells.Find(What:="915833 0011", After:=ActiveCell, LookIn:=xlFormulas, _ 
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
MatchCase:=False, SearchFormat:=False) 
Err.Clear: On Error Goto -1: On Error Goto 0 
If Not rngCell Is Nothing Then 
    rngCell.Activate 
    ActiveCell.Offset(0, 1).Copy 
    Sheets("Skill matrix").Select 
    Cells.Find(What:="30010", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
    False, SearchFormat:=False).Activate 
    ActiveCell.Offset(8, 0).Activate 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:= _ 
    False, Transpose:=False 
    Set rngCell = Nothing 
End If 
 
 'to copy 30013's time
Sheets("SAP data").Select 
 'Start search in row 1
LSearchRow = 1 
 'Start copying data to row 2 in Sheet1 (row counter variable)
LCutToRow = 1 
While Len(Range("A" & CStr(LSearchRow)).Value) > 0 
     'If value in column A = "00030013", copy entire row to Sheet1
    If Range("A" & CStr(LSearchRow)).Value = "00030013" Then 
         'Select row in SAP data to copy
        Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select 
        Selection.Cut 
         'Paste row into Sheet1 in next row
        Sheets("Sheet1").Select 
        Rows(CStr(LCutToRow) & ":" & CStr(LCutToRow)).Select 
        ActiveSheet.Paste 
         'Move counter to next row
        LCutToRow = LCutToRow + 1 
         'Go back to SAP data to continue searching
        Sheets("SAP data").Select 
    End If 
    LSearchRowz = LSearchRow + 1 
Wend 
 'Position on cell A3
Application.CutCopyMode = False 
Range("A3").Select 
 'to edit their data
Sheets("Sheet1").Select 
 'convert time
Columns("J:J").Select 
Application.CutCopyMode = False 
Selection.Insert Shift:=xlToRight 
Range("J1").Select 
ActiveCell.FormulaR1C1 = "=RC[-1]/24" 
Selection.AutoFill Destination:=Range("J1:J100"), Type:=xlFillDefault 
Columns("J:J").Select 
Selection.NumberFormat = "h:mm" 
 'remove index from part number
Columns("F:F").Select 
Application.CutCopyMode = False 
Selection.Insert Shift:=xlToRight 
Columns("F:F").Select 
Selection.NumberFormat = "General" 
Range("F1").Select 
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1,LEN(RC[-1])-4)" 
Selection.AutoFill Destination:=Range("F1:F100"), Type:=xlFillDefault 
 'combine material and op numbers
Range("G1").Select 
Do While ActiveCell  "" 'Loops until the active cell is blank
    ActiveCell.Offset(0, 7).Formula = _ 
    ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0) 
    ActiveCell.Offset(1, 0).Select 
Loop 
Columns("B:I").Select 
Selection.Delete Shift:=xlToLeft 
Columns("D:E").Select 
Selection.Delete Shift:=xlToLeft 
Rows("1:1").Select 
Selection.Insert Shift:=xlDown 
Range("A1").Select 
ActiveCell.FormulaR1C1 = "CC" 
Range("B1").Select 
ActiveCell.FormulaR1C1 = "OT" 
Range("C1").Select 
ActiveCell.FormulaR1C1 = "NT" 
Range("D1").Select 
ActiveCell.FormulaR1C1 = "PON" 
Range("D2").Select 
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
"Sheet1!R1C1:R101C4").CreatePivotTable TableDestination:= _ 
"'[T5 matrix1.xls]Sheet1'!R1C6", TableName:="PivotTable3", DefaultVersion:= _ 
xlPivotTableVersion10 
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _ 
"PivotTable3").PivotFields("NT"), "Count of NT", xlCount 
With ActiveSheet.PivotTables("PivotTable3").PivotFields("PON") 
    .Orientation = xlRowField 
    .Position = 1 
End With 
Range("F1").Select 
ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of NT").Function = _ 
xlSum 
Range("G3:G30").Select 
Selection.NumberFormat = "h:mm" 
Columns("A:D").Select 
Selection.Delete Shift:=xlToLeft 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am looking for a way to search for text with an input search box. Then when each instance of the word/s is found I want to extract the whole row and dump it to a new sheet. The word/s that I will search for will not be in any specific row or column but scattered around on the sheet.
Any ideas how I can get this started will be greatly appreciated. I have been playing with input boxes but have no idea how to use the data that is inputted to it. I was thinking this was my first step.


No luck finding an answer? You could always try Google.