Copy and Paste VBA

Eve05

New Member
Joined
Sep 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Below is a code where by i am copying data to another sheet but also need it to copy to the original sheet the data is on just in a different place. The code works to copy to the other sheet but not when it should be copying to the same page. The range it needs to be copied into is C18:N25. Can anyone help and tell me where I am going wrong. Many thanks


Private Sub CommandButton1_Click()
Dim lr As Long, lr2 As Long, r As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Application.ScreenUpdating = False

Set ws1 = Sheets("Interface")
Set ws2 = Sheets("Email")

lr = ws1.Cells(Rows.Count, "d").End(xlUp).Row

For r = lr To 2 Step -1
lr2 = ws2.Cells(Rows.Count, "c").End(xlUp).Row + 1

If ws1.Range("c" & r).Value = "Donna" Or _
ws1.Range("c" & r).Value = "Laura" Or _
ws1.Range("c" & r).Value = "Sam" Or _
ws1.Range("c" & r).Value = "Sophie" Or _
ws1.Range("c" & r).Value = "Yvonne" Or _
ws1.Range("c" & r).Value = "Matt" Or _
ws1.Range("c" & r).Value = "Dan" Then

ws1.Rows(r).Copy Destination:=ws2.Range("a" & lr2)

If ws2.Range("c" & r).Value = "Donna" Or _
ws2.Range("c" & r).Value = "Laura" Or _
ws2.Range("c" & r).Value = "Sam" Or _
ws2.Range("c" & r).Value = "Sophie" Or _
ws2.Range("c" & r).Value = "Yvonne" Or _
ws2.Range("c" & r).Value = "Matt" Or _
ws2.Range("c" & r).Value = "Dan" Then

ws2.Rows(r).Copy Destination:=ws1.Range("C" & lr2)

End If
End If
Next r
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,708
Please use the code window for pasting your code. < / > >_

So where does the code 'breakdown' in the copy paste routine ?
You state " ... it needs to be copied into is C18:N25 " but where in your
code is "C18:N25" ?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,931
Messages
5,545,087
Members
410,652
Latest member
Zot
Top