VBA help with copy pasting rows

[vbahelpneeded15]

Hi,
I have been trying to set up a VBA code that helps me move rows with the word Sam in column A to the bottom of the sheet (the number of rows with this word changes everyday). I have managed to set up a code that copies the data but it always pastes it in the same row (i.e. row 540) even if it is not the next empty row. I was wondering if I could get some help with it so it pastes in the next empty row instead.
Here's my code:

Sub Step3 ()
Dim r As Range
Dim s As String
Dim firstRowWithS As Long, lastRowWithS As Long, destinationRow As Long

s = "Sam"
destinationRow = 540

For Each r In Sheets("sheetname").Range("A373:A398")
'find first row where we see string s
If r.value = s And r.Offset(-1, 0).value <> s Then
firstRowWithS = r.Row
End If

'find last row where we see string s
If r.value <> s And r.Offset(-1, 0).value = s Then
lastRowWithS = r.Offset(-1, 0).Row
End If
Next

'copy and paste rows to destination
Sheets("sheetname").Range(373 & ":" & 398).Copy Destination:=Sheets("sheetname").Range("A" & destinationRow)
End Sub

Thank you for the help!

 

[Fluff]

Hia
Try changing this

destinationRow = 540

to

Sheets("sheetname").Range(373 & ":" & 398).Copy Destination:=Sheets("sheetname").Range("A" &  Rows.Count).End(xlUp).Row

 

[vbahelpneeded15]

Hi! Thanks for your help. It gives a range error when I put it in though :/ any idea how I can fix that?

 

[Joe4]

In pasting, you typically only need to designate the first cell of the paste range.
Do you have any merged cells in either range (from/to)?

Also, I am not sure why you are using this:

Range(373 & ":" & 398)

instead of just using this:

Range("373:398")

Seems a bit unnecessary if that is a hard-coded range.

 

[vbahelpneeded15]

Hi! I don't have any merged cells. That's what I did earlier to paste, but the issue is that the cell I want to paste in changes everyday (I want it to paste it in the first blank row that day). I'm not entirely sure how to write a code that does that.

 

[Fluff]

Apologies it should read

Sheets("sheetname").Range(373 & ":" & 398).Copy Destination:=Sheets("sheetname").Range("A" &  Rows.Count).End(xlUp).Offset(1)

This will however paste all rows 373 to 398 regardless.
should this line be

Sheets("sheetname").Range(firstRowWithS & ":" & lastRowWithS).Copy  Destination:=Sheets("sheetname").Range("A" &   Rows.Count).End(xlUp).Offset(1)

 

[Joe4]

So, you want to determine the destination row dynamically, by selecting the first blank cell in column A after the last row of data?
You can do that like this:

[COLOR=#333333]destinationRow = [/COLOR][COLOR=#333333]Sheets("sheetname").Cells(Rows.Count,"A").End(xlUp).Row+1[/COLOR]

 

[vbahelpneeded15]

Thank you! This worked. What would I do if I wanted to cut the rows instead?

 

[Joe4]

Just change the .Copy in your VBA code to .Cut

 

[vbahelpneeded15]

Thank you for the help!