VBA code for moving one row of data in a sheet to another based on criteria in a column

[CSUMMITT]

I need to be able to move a row from one sheet in a workbook to another based on the data in the first column to another sheet in the workbook. The workbook will have multiple sheets (each with a unique name) so I will need to be able to apply it to each sheet to move data to the base sheet for the desired data reporting. The following is what the headers of each column look like that we are using. the data that we are needing to use to pull the row is column 1 (status) to the base sheet that has specified named. Any help would be great as I need to have this up and running by the end of the month. Chris

status	arrival date	po#	req#	wo#	job	manifest	item	desc	heat	qty	boxes	pallets	weight	loc	est	date	complete	notes	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20

<tbody>
</tbody>

 

[davesexcel]

Re: Need help with a VBA code for moving one row of data in a sheet to another based on criteria in a column

What part of the code do you need help with?

 

[CSUMMITT]

Re: Need help with a VBA code for moving one row of data in a sheet to another based on criteria in a column

What part of the code do you need help with?

I know nothing of VBA so help in doing the right steps would be great. I've tried using a code in a tutorial but it just errors out and have no clue what I did wrong for it.

This is the code I had found and was tweaking for my use. The status that I need the move is anything tagged "excess". Sheet1 is where the row needs to be copied to and sheet2 (which would be changed for which ever sheet i'm currently on) is the one the original data is from.

-----------------------------------------------------------------
Private Sub CommandButton1_Click()
a = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a

If Worksheets("Sheet2").Cells(a, 3).Value = "EXCESS" Then

Worksheets("Sheet2").Rows(a).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet2").Activate

End If
Next

Aplication.CutCopyMode = False

ThisWorkbook.Worksheets("Sheet2").Cells(1, 1).Select

End Sub
-------------------------------------------------------------------------------

Thanks for any help you can provide

 

[davesexcel]

Hi,
What column has the sheet names?

Your original question mentions column A, your code is using column C.

Here is a code that goes through column A and finds EXCESS and copies/pastes to the next sheet.

Sub Button1_Click()
    Dim sh As Worksheet, ws As Worksheet
    Dim LstRw As Long, rng As Range, c As Range

    Set sh = Sheets("Sheet1")
    Set ws = Sheets("Sheet2")

    With sh

        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A2:A" & LstRw)

        For Each c In rng.Cells

            If UCase(c) = "EXCESS" Then

                c.EntireRow.Copy ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)

            End If

        Next c

    End With


End Sub

 

[CSUMMITT]

Hi,
What column has the sheet names?

Your original question mentions column A, your code is using column C.

Here is a code that goes through column A and finds EXCESS and copies/pastes to the next sheet.

Sub Button1_Click()
    Dim sh As Worksheet, ws As Worksheet
    Dim LstRw As Long, rng As Range, c As Range

    Set sh = Sheets("Sheet1")
    Set ws = Sheets("Sheet2")

    With sh

        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A2:A" & LstRw)

        For Each c In rng.Cells

            If UCase(c) = "EXCESS" Then

                c.EntireRow.Copy ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)

            End If

        Next c

    End With


End Sub

The Excess sheet doesn't have the names of the other sheets in the workbook.
The workbook has roughly 75 sheets that I will be coping the excess data rows from to the one sheet. I was thinking of having the code on each of the sheets so i would have to run it from each sheet. But if there is a way of doing it from the Excess sheet looking at all the others that would be great.

I will try this version today and see how it works.

 

[CSUMMITT]

The Excess sheet doesn't have the names of the other sheets in the workbook.
The workbook has roughly 75 sheets that I will be coping the excess data rows from to the one sheet. I was thinking of having the code on each of the sheets so i would have to run it from each sheet. But if there is a way of doing it from the Excess sheet looking at all the others that would be great.

I will try this version today and see how it works.

I have tried it and getting a "runtime error "9" subscript out of range" message when i try an test it in the VBA screen.
I have also taken the time to create a column in the excess sheet listing all the names of the sheets.
I have in the workbook sheets 2 - 86 but not all have data that i need to pull data from.

The sheets use from column A - AL of info in the rows to be copied.

 

[davesexcel]

Subscript out of range would probably be the sheet names are in correct in the code