Braulio cordova
asked on
maro to copy and paste from one worksheet to another based on a condition
Hi Expert Team,
I need a macro that will copy range C6:C200 from "master" sheet and paste onto B21 in the "quotesheet" sheet but only if there is a value "yes" in range D6:D200. below is what I have so far and it works but I need it to only copy paste if there is a value "yes" in D6:D200 in the "master" sheet. I created a bottom called "import items" that runs the macro. Also attached is a sample file. In advance your help is greatly appreciated
Sub copyover()
Sheets("master").Select
Range("C6:C200").Select
Selection.Copy
Sheets("QouteSheet").Selec t
Range("B21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
I need a macro that will copy range C6:C200 from "master" sheet and paste onto B21 in the "quotesheet" sheet but only if there is a value "yes" in range D6:D200. below is what I have so far and it works but I need it to only copy paste if there is a value "yes" in D6:D200 in the "master" sheet. I created a bottom called "import items" that runs the macro. Also attached is a sample file. In advance your help is greatly appreciated
Sub copyover()
Sheets("master").Select
Range("C6:C200").Select
Selection.Copy
Sheets("QouteSheet").Selec
Range("B21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Perhaps.
Sub copyover()
With Sheets("master")
If ApplicationCountIf(.Range("D6:D200"), "Yes") > 0 Then
.Range("C6:C200").Copy
Sheets("QuoteSheet").Range("B21").PasteSpecial Paste:=xlPasteValues
End If
End With
End Sub
Hi,
pls try
pls try
Sub CopyOver()
Sheets("master").Activate
Set Rng = Nothing
For Each c In Range("C6:C200")
If LCase(c.Offset(, 1)) = "yes" Then
If Rng Is Nothing Then
Set Rng = c
Else
Set Rng = Union(c, Rng)
End If
End If
Next
Rng.Copy
Sheets("QuoteSheet").Range("B21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
Regards
ASKER
Hi Rgonzo1971,
I am getting a run-time erro "9": subscript out of range. the code is stopping at this point......
Sheets("QuoteSheet").Range ("B21").Pa steSpecial Paste:=xlPasteValues
Any Thoughts?
Thanks
Braulio
I am getting a run-time erro "9": subscript out of range. the code is stopping at this point......
Sheets("QuoteSheet").Range
Any Thoughts?
Thanks
Braulio
How is QuoteSheet written
QuoteSheet or QouteSheet
Could you send a dummy?
QuoteSheet or QouteSheet
Could you send a dummy?
ASKER
Hi Rgonzo1971,
Yes indeed I had it misspelled. The code works perfectly. One last question, can you please add a piece of code that after the macro finishes the copy and paste it sets focus on the "quotesheet" and not the "master" sheet?
Thanks
Braulio
Yes indeed I had it misspelled. The code works perfectly. One last question, can you please add a piece of code that after the macro finishes the copy and paste it sets focus on the "quotesheet" and not the "master" sheet?
Thanks
Braulio
If you want focus to stay on the 'master' sheet try this.
Sub CopyOver()
Dim rng As Range
Dim c As Range
With Sheets("master")
For Each c In .Range("D6:D200")
If LCase(c) = "yes" Then
If rng Is Nothing Then
Set rng = .Offset(, -1)
Else
Set rng = Union(.Offset(, -1), rng)
End If
End If
Next
End With
If Not rng Is Nothing Then
rng.Copy
Sheets("QuoteSheet").Range("B21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Norie,
I want the focus to stay on quotesheet not master. I tried the code and I got the following error
run-time error '438": "the object doesn't support this property or method.
Thank you in advance for the support
I want the focus to stay on quotesheet not master. I tried the code and I got the following error
run-time error '438": "the object doesn't support this property or method.
Thank you in advance for the support
ASKER
Thank you Rgonzo1971!
This is exactly while I need it.
Braulio
This is exactly while I need it.
Braulio
The code I posted will keep focus on whatever sheet was active when the code is run.
It also won't switch between sheets when running.:)
Anyway, here it is with the typos that caused the error(s) fixed.
It also won't switch between sheets when running.:)
Anyway, here it is with the typos that caused the error(s) fixed.
Sub CopyOver()
Dim rng As Range
Dim c As Range
With Sheets("master")
For Each c In .Range("D6:D200")
If LCase(c) = "yes" Then
If rng Is Nothing Then
Set rng = c.Offset(, -1)
Else
Set rng = Union(c.Offset(, -1), rng)
End If
End If
Next
End With
If Not rng Is Nothing Then
rng.Copy
Sheets("QuoteSheet").Range("B21").PasteSpecial Paste:=xlPasteValues
End If
Application.CutCopyMode = False
End Sub
Open in new window