Moving rows from one sheet to another based upon the value of one cell. (1 Viewer)

[donbettis]

I am using Excel 2007 - most of the company is still on Excel 2000.

I can do this rather easy in Access however, the Boss man wants it in excel and I have basically zero knowledge of excel.

Anyways what I need to do is this. If you look at the attached sample you will see a sheet called WHLSINFO…this data is imported. Once it is imported I need to move rows to different sheets based upon column “J “.

If column “J” contains “2209CBN” I need to copy that row to the sheet called “2209 CBN”. If column “J” contains “2209 DIA” I need to move that row to the sheet called “2209 DIA” etc….

If at all possible I need this to be an automatic process or a auto as possible that takes place upon the import of WHLSINFO - there are about 35 different items that could show up in column “J”. The target sheets will exist with data in them…Would really be nice if it could remove the old data before importing the new.

This may be really easy - I just don't know. I am not an excel person. The one thing I have learned is that if you enter VBA like that of access - excel says HUH...LOL

Any and all assistance would be great…

Don

 

[donbettis]

After playing around just about all day with this thing...I figured it out...

And to think - in access I could have create 1 query, 1 report, done some copy and pasting and change a few little things here and there and I could have had it finished in less then one hour...

I still have a ton of typing to do...but at least i now know how to do it...42 items can showup in col "J"...

Below is how I got it to work...(it's a macro)

Sub MoveRowToCorrectSheet()
Dim LastRowMain As Long
Dim LastRowNewOrd As Long
Dim LastRowNewOrd3 As Long
Dim LastRowNewOrd4 As Long
Dim LastRowNewOrd5 As Long
Dim i As Long
Application.ScreenUpdating = False
LastRowMain = Worksheets("WHLSINFO").Range("A" & Rows.Count).End(xlUp).Row
With Worksheets("WHLSINFO") '
For i = 2 To LastRowMain Step 1
Select Case Cells(i, "J").Value
' This copies 11A2CBN to Sheet2 Case "11A2CBN"
LastRowNewOrd = Worksheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row
Rows(i).Copy Worksheets("sheet2").Range("A" & LastRowNewOrd + 1)
' This copies 11A2DIA to Sheet3
Case "11A2DIA"
LastRowNewOrd3 = Worksheets("sheet3").Range("A" & Rows.Count).End(xlUp).Row
Rows(i).Copy Worksheets("sheet3").Range("A" & LastRowNewOrd3 + 1)
' This copies 11V5DIA to Sheet4
Case "11V5DIA"
LastRowNewOrd4 = Worksheets("sheet4").Range("A" & Rows.Count).End(xlUp).Row
Rows(i).Copy Worksheets("sheet4").Range("A" & LastRowNewOrd4 + 1)
' This copies 11V9CBN to Sheet5
Case "11V9CBN"
LastRowNewOrd4 = Worksheets("sheet4").Range("A" & Rows.Count).End(xlUp).Row
Rows(i).Copy Worksheets("sheet4").Range("A" & LastRowNewOrd4 + 1)
' This leaves anything else behind
Case Else
' leave blank so will perform no action
End Select
Next i
End With
Application.ScreenUpdating = True
End Sub