Vba Code Help

[WODNEYj]

Vba Code Help.....please please help

hello, i am having problems coding a procedure that use's the users input in a msgbox to look up a matching record set in a seperate worksheet then selects all matching records in that worksheet and copys them to a new summary worksheet. any help would be greatly apprciated!!!

 

[caveman]

Maybe try using AutoFilter to find all matching records, then copy visible records to your summary worksheet.

Here's a simple example:

Option Explicit
 
 Sub Copy1()
 Dim InputTxt As String
 Dim LastRow As Long
 
 With ThisWorkbook
 
 	Application.ScreenUpdating = False
 	InputTxt = InputBox("Search for ...")
 	With .Worksheets("Sheet1")
 		.Range("A1").Select
 		LastRow = .UsedRange.Rows.Count
 		LastRow = LastRow + .UsedRange.Row - 1
 	End With
 	   
 	On Error GoTo ErrorHandler
 	Selection.AutoFilter Field:=1, Criteria1:=InputTxt
 	.Worksheets("Sheet1").Range("A" & 2, "H" & LastRow).SpecialCells(xlCellTypeVisible).Select
 	Selection.Copy .Worksheets("Sheet2").Range("A2")
 	.Worksheets("Sheet1").Range("A1").Select
 	Selection.AutoFilter Field:=1
 End With
 	Exit Sub
 	
 ErrorHandler:
 	MsgBox "Error - bad criterion"
 	
 End Sub

Everything depends on your workbook's structure.