PDA

View Full Version : Move data one spreadsheet to another



Emoncada
01-15-2008, 06:55 AM
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
01-15-2008, 07:43 AM
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
01-15-2008, 07:14 PM
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.

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

Aussiebear
01-16-2008, 03:20 AM
Can we have a look at your worksheet please?

Emoncada
01-16-2008, 07:03 AM
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
01-16-2008, 11:36 PM
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.