Excel VBA Macro coding/ data look up

Closed
keendiggz - Jun 27, 2011 at 01:47 PM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
- Jun 28, 2011 at 08:35 PM
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!


3 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Jun 27, 2011 at 10:31 PM
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
0
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.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Jun 28, 2011 at 08:35 PM
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.
0