Moving a row upwards

[fluffynicesheep]

Hi,

I currently have a macro that copies and pastes a number of rows from one workbook into a tab in another workbook.

Row 1 (Column A to PO) - is the header row
Row 2 and down (Column A to PO) is the data

This works great, and basically, other tabs in the excel document then look at the first line of data in Row 2 and inserts the required info from this row into a form ... which is then printed.

I then want to do the same thing for the data in Row 3, Row 4 .... and so on until the bottom row.


So what am I looking for?

Now the ideal thing for me to do would be to delete row 2, and then row 3 automatically moves up and the data automatically goes in, but as we know this then breaks the formula and we get a #REF error, so I'm looking for a script that does something like:


Click a button and everything in Row 3 downwards (until the final row with data in) moves up a row - so Row 3 becomes Row 2, Row 4 becomes row 3 etc .... every time you click the button whatever is in Row 2 disappears and is replaced by the line below.

This would continue until we reach the final row that contains data in it - where it won't let you move it up again , as I want to ensure that at the end of the process there is still a header row present and 1 row of data (so the formula's don't break in the other tabs!).

Not sure if this is the best way to approach this, so any help would be appreciated!

 

[Snakehips]

@fluffynicesheep

I don't know you sheet names nor whether you are wanting a button in sheet or on a form.
However, something like this should work as a command button.

Private Sub CommandButton1_Click()
Dim DataSheet As Worksheet
Dim MyRange As Range
Dim LastRow As Long


Set DataSheet = Sheets("YourSheetHere")
With DataSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If LastRow = 2 Then Exit Sub
Application.ScreenUpdating = False
.Range("A2:PO" & LastRow - 1).Value = .Range("A3:PO" & LastRow).Value
.Range("A" & LastRow & ":PO" & LastRow).ClearContents
Application.ScreenUpdating = True
End With
End Sub

Hope that helps.

 

[fluffynicesheep]

@snakehips

Absolutely perfect, that has done everything I needed - thanks so much!