Move data one spreadsheet to another

[Emoncada]

I have data from cells B:H. I have an update button on the spreadsheet.
I would like for when they click the update button for it to look at Column(8) and anything that says "Available Depot" for it to grab that row of data "B:H" move everything else up and paste that data into sheet2 same range B:H but at the bottom of that list. How can I make this happen.

[Emoncada]

I can use the delete row option the only thing is Column A is a count so I would need some how to regenerate that. Example if row B9 is deleted A9 = "1" and i would need to make that number comeback.

[herzberg]

Once a row is deleted, I don't think it's possible to get the data back. A simple workaround would be to store whatever numbers you want on a different sheet first prior to deleting the rows.

That said, below is how I would do it, without consideration of what is stored in column A. It's based on 2 assumptions: 1, the rows in the both sheets are continuous, i.e. no empty rows in between the filled rows; 2, the data in the first sheet has column headers.

[vba] Public Sub MoveMe()

Dim TargetSheet As Worksheet, SourceSheet As Worksheet
Dim Copyrg As Range
Dim NextRow As Long, RowStart As Long, RowEnd As Long

Set SourceSheet = ThisWorkbook.Sheets("Sheet1")
Set TargetSheet = ThisWorkbook.Sheets("Sheet2")

With TargetSheet
.Activate
NextRow = .Cells(1, 1).End(xlDown).Row + 1
End With

With SourceSheet
.Activate
.Cells(1, 1).AutoFilter Field:=1, Criteria1:="Available Depot"

With .AutoFilter.Range
RowStart = .Offset(1, 0).SpecialCells(xlCellTypeVisible).Row
RowEnd = .Offset(1, 0).SpecialCells(xlCellTypeVisible).End(xlDown).Row

Set Copyrg = .Range(Cells(RowStart, 2), Cells(RowEnd, 8))
Copyrg.copy TargetSheet.Cells(NextRow, 2)

Copyrg.Delete Shift:=xlShiftUp
End With

.AutoFilterMode = False

End With

Application.CutCopyMode = False

Set TargetSheet = Nothing
Set SourceSheet = Nothing
Set Copyrg = Nothing

End Sub[/vba]

[Aussiebear]

Can we have a look at your worksheet please?

[Emoncada]

It seems to work but only if Column headers are in row 1 and then data in row 2 down, which is not the way i have it set up. I am attaching the spreadsheet hope that helps. Also when it goes to sheet 2 if there is a value already in a row it replaces it instead of going to the bottom of the list.

[herzberg]

What I've written is pretty general, so I've amended the code slightly to suit your layout. It always helps to be able to see how the actual layout is like.