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!

 

[Joe4]

You are welcome.

 

[vbahelpneeded15]

VBA Cut paste Excel

Hi,
I have been working on a VBA code that allows me to move a group of rows that contain the word "Sam" in column A to the bottom of the page. While I've been successful in moving it in one file, the number and location of the rows containing this changes. I made a code with a range for that specific file but tomorrow, this range will have different numbers and I can't figure out how to set it up so that it moves the rows with "Sam" in them every time. Here is 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 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each r In Sheets("Sheet1").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
'cut and paste rows to destination
Sheets("Sheet1").Range(373 & ":" & 398).Cut Destination:=Sheets("Sheet1").Range("A" & destinationRow)
End Sub

Any help so that I can move a changing number of rows (with changing locations as well) using VBA would be great, Thank you very much!

 

[onlyadrafter]

Re: VBA Cut paste Excel

Hello,

this code will move all rows with Sam in Column A to the bottom of the spreadsheet.

Sub MOVE_SAM()
    For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("A" & MY_ROWS).Value = "Sam" Then
            Rows(MY_ROWS).Cut Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    Next MY_ROWS
End Sub

Is this anything close to your expectations?

 

[vbahelpneeded15]

Re: VBA Cut paste Excel

Hi! Thanks for the code. Unfortunately, nothing happened on my file.

 

[vbahelpneeded15]

Hi! Sorry to be back again. While the code you gave me worked yesterday, today the rows with "Sam" were a different range. As a result, the range that moved was not the one I wanted. Is there any way I can set it up so that it only moves rows with "Sam" instead of just the specific range of rows each time. (for instance, yesterday "Sam" was from row 373 to 423 but today, it's from 440 to 456)

 

[Joe4]

Looks like you already started a new thread on this question.

Please do not post the same question multiple times. Per forum rules, posts of a duplicate nature will typically be locked or deleted (rule #12 here: Forum Rules).

The general rule of thumb is this:
- If it is a follow-up question that is dependent upon the previous question/answer, continue posting in the same thread.
- If it is a brand new question where knowledge of the previous question is not necessary (even if it is on the same project), post it to a new thread.

However, you need to pick one or the other, but not do both. I will merge your two threads together to this one. Posts 12-14 are from the other thread that was merged into here.

 

[vbahelpneeded15]

Oh okay! Sorry about that.

 

[Joe4]

Any help so that I can move a changing number of rows (with changing locations as well) using <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> would be great

Please provide the specifics/rules regarding how VBA should determine which rows/locations to search.

 

[vbahelpneeded15]

So basically I want it to locate the rows that have the word "Sam" in column A. The number of rows that have this word and their location on the sheet changes everyday. I want to copy and paste these rows to the bottom of my sheet (i.e. the first available blank row). For instance, one day these rows could be from A45-A90 and on another day they could be from A400-A405.

 

[Joe4]

But how is the code supposed to know to look through A45-A90 or A400-A405?
What exactly tells the code that?
What is the logic the code can use to determine which ranges to look through?