Hi. Need to copy/cut rows from sheet to another at top of dataset

[Ndleo1]

Hi. First post so apologies if not the right format.

I’m trying desperately to achieve the following via macro

Sheet 1 (Input) data from manual input and validated drop downs, only populated row/rows need to be copied/cut each time but the number of rows for each ‘transfer’ can range from 1 row to 20 rows (starting at A2 and spanning to M2, A3:M3 ... and so on if more rows required.

i then need to have this data appear on Sheet 2 (Tracker) at the top, again from Row A2 and for the copied / cut amount of rows (this is the part I just cannot get) pushing the previous data down the sheet/database as opposed to overwriting it each time.

Then returning the Sheet 1, A2 ready to go again... essentially an order ticket on sheet 1 and a database on Sheet2

any help greatly received and will save what hair I have left! 😬🙏

[p45cal]

It depends on several things:

Do you want to copy or move (cut and paste)?
When you paste into the Tracker sheet, do you want just cells in columns A:M to move down, or the entire rows of that sheet to move down?
When you cut from the Input sheet, do you want in that sheet for:

only cells in columns A:M to move up
entire rows to move up
neither, just clear the contents.



Best attach a workbook.

[Dave]

Hi Ndleo1. I see that p45cal has more insight than I. At it's literal simplest...

Private Sub Test()Dim Lastrow As Integer, RowsToAdd As Integer
With Sheets("sheet1")
Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
RowsToAdd = Lastrow - 1
Sheets("Sheet2").Range("A1").Rows("1:" & RowsToAdd).EntireRow.Insert
Sheets("Sheet1").Range(Sheets("Sheet1").Cells(2, "A"), Sheets("Sheet1").Cells(Lastrow, "M")).Copy _
Destination:=ThisWorkbook.Sheets("Sheet2").Range("A2")
Application.CutCopyMode = False
End Sub
Dave

[Ndleo1]

Thanks for replying.
1) move
2) entire rows to move down
3) clear contents

thanks again for your assistance

[Ndleo1]

I will check if this works as soon as I’m back in front of my pc. Thanks ��

[Ndleo1]

Thanks for replying.
1) move
2) entire rows to move down
3) clear contents

thanks again for your assistance

[Dave]

Seems like change "copy" to "cut" in the posted code would do it for U. Dave

[p45cal]

Best attach a workbook.
This might do it:

Sub blah()
With Sheets("Input")
LastRow = .Range("A:M").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If LastRow > 1 Then
Set rngSce = Range(.Range("A2"), .Cells(LastRow, "M"))
With Sheets("Tracker")
.Rows(2).Resize(rngSce.Rows.Count).Insert
rngSce.Copy .Cells(2, "A")
rngSce.ClearContents
End With
End If
End With
End Sub

The line:

rngSce.Copy .Cells(2, "A")
will copy everything, dropdowns included, maybe you don't want that.

Instead,

.Cells(2, "A").Resize(rngSce.Rows.Count, rngSce.Columns.Count).Value = rngSce.Value
will copy ONLY values, no formatting.
and

.Cells(2, "A").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
will copy values and number formats, but no fill colours etc.

[Ndleo1]

Many thanks - option 1 works just fine. really appreciate the assistance :yes

[Ndleo1]

Seems like change "copy" to "cut" in the posted code would do it for U. Dave

going to try this one also in an effort to educate myself more - many thanks Dave