Stop overwriting cells - Copy and paste macro

[juhaszp]

Hey. Noobie here. I'm trying to make a macro that copies and pastes data from one sheet to another without replacing the cells in the pasted area.

Sub cond()
Sheets("Master List").Select 'Start on this page
Dim r As Long, endRow As Long, pasteRowIndex As Long
endRow = 1000 ' of course it's best to retrieve the last used row number via a function
pasteRowIndex = 1
For r = 1 To endRow 'Loop through sheet1 and search for your criteria
If Cells(r, Columns("M").Column).Value = "Yes" And Cells(r, Columns("N").Column).Value = "Yes" Then 'Found
'Copy the current row
Rows(r).Select
Selection.Copy
'Switch to the sheet where you want to paste it & paste
Sheets("Call List").Select
Rows(pasteRowIndex).Select
ActiveSheet.Paste
'Next time you find a match, it will be pasted in a new row
pasteRowIndex = pasteRowIndex + 1
'Switch back to your table & continue to search for your criteria
Sheets("Master List").Select
End If
Next r
End Sub

Also could someone tell me what "Dim r As Long, endRow As Long, pasteRowIndex As Long " means
I am modifing a code from somewhere else and am trying to learn by just doing it, but some of this has thrown me for a loop.

Thanks guys

[AB33]

"Dim r As Long, endRow As Long, pasteRowIndex As Long "
You are simply declaring variables to use them in your code

Please Login or Register  to view this content.

[juhaszp]

"Dim r As Long, endRow As Long, pasteRowIndex As Long "
You are simply declaring variables to use them in your code

I figured as much. Was confused why everything said "...as Long".

Anyway solution to paste at the end of the last filled cells? I'd like it to not overwrite when it's run.

[AB33]

You do not overwrite existing data as you have pasteRowIndex = 1
and this increase with each loop if it is true.

Please Login or Register  to view this content.

You need to change the starting row of your sheet.

There are many types of data types in VBA. One of them is called long