Excel VBA Macro coding/ data look upClosed

Question

Hello,  

I have a single workbook with multiple sheets. one of my sheets contains a list of information (ie date,name,owner,description, and a category). I have made other worksheets of where i want the information to go. basically, i have category in column E, and I want excel to look up column E and if it sees a value equal to, let's use 'red', to select that row, copy the whole row, and copy it into the 'red' worksheet. The spreadsheet with all the info contains roughly 300 entries to look though. 

Thanks in advance for any help!


Configuration: Windows XP / Internet Explorer 8.0

venkat1926 · Answer

main data is in sheet 1
the sheet red is also available.

then try this macrof 


Sub test()
Dim datarange As Range, filt As Range, crtra As String
With Worksheets("sheet1")
crtra = InputBox("type the criteria e.g. red")
Set datarange = .Range("A1").CurrentRegion
datarange.AutoFilter field:=Range("E1").Column, Criteria1:=crtra
Set filt = datarange.SpecialCells(xlCellTypeVisible)
filt.Copy
With Worksheets(crtra)
.Range("A1").PasteSpecial
End With
datarange.AutoFilter
End With
End Sub

keendiggz · Answer

Venkat, Thanks for your help, unfortunately this is not quite what i needed.

I'm not looking for any sort of user form, I want it to auto update from worksheets. Basically I have one sheet with all my info, and within that sheet there are different file types. Let's use the example of a column labeled 'file type'. within that column there will be values for each entry reports, to requisitions, to revised reports.. i will then have a corresponding sheet titled each of those values.

if an entry says "reports" in that column, i want the whole row to be copied and pasted into the "reports" worksheet WITHOUT overwriting previous data (ie. add new data beneath)

Thanks in advance to any insight you may have.

venkat1926 · Answer

there is no user form in my solution..  this is only a macro 

instead of pasting on range("A1") in worksheets(ctra)
use the statement 


With Worksheets(crtra)
.cells(rows.count.A"").end(xlup).offset(1,0).PasteSpecial
End With

this will  paste after the last row in sheet who name is defined by ctra.

Excel VBA Macro coding/ data look up

3 responses

Similar discussions

Subscribe To Our Newsletter!