Copying cells works but not on another sheet?

Sdwd76

New Member
Joined
May 17, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
So I have the below code working on another sheet where it will copy and paste where a word if found in a row. So if a word is on 4 rows - Then 4 rows data is copied.

So i have tried to use the same on other sheets but it only copies one row (The last one so not including other rows) I am unsure why this is? Any help would be grateful. I have checked modules names and sheet names are all different

Sub Removetwo()

Application.ScreenUpdating = False

Dim ts1 As Worksheet, ts2 As Worksheet
Set ts1 = Sheets("Remove")
Set ts2 = Sheets("Leave")
Dim lr, lr2, r

lr = ts1.Range("A" & Rows.Count).End(xlUp).Row

For r = 1 To lr


If ts1.Cells(r, "Y") = "Scrapped" Then
lr2 = ts2.Range("A" & Rows.Count).End(xlUp).Row
ts1.Cells(r, "E").Copy
ts2.Range("C" & lr2).Offset(1).PasteSpecial Paste:=xlPasteValues




End If

Next r




Application.ScreenUpdating = True

End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,468
Office Version
  1. 365
Platform
  1. Windows
I have checked modules names and sheet names are all different
If the names used are correct to the workbook then the only possible cause would be a mismatch in the data, possibly leading / trailing spaces in the cells with "Scrapped"

Also, note that If in vba is case sensitive, meaning that "Scrapped" and "scrapped" are different.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,591
Office Version
  1. 365
Platform
  1. Windows
You are using col A to determine the last used row on sheet Leave, but then pasting data into col C. Therefore your lr2 value will never change.
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,468
Office Version
  1. 365
Platform
  1. Windows
Good spot @Fluff, I missed that one.

That said, I did have to go over the code more than once to even find the lr2 definition. I'm gonna blame lack of formatting :unsure:
 

Sdwd76

New Member
Joined
May 17, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
Thanks gents in my other one i was copying to A in this one to C so changed this line - "C"
lr2 = ts2.Range("C" & Rows.Count).End(xlUp).Row

works a dream thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,591
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,147,498
Messages
5,741,504
Members
423,663
Latest member
kaveh87rsh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top