Link to home
Start Free TrialLog in
Avatar of Braulio cordova
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").Select
    Range("B21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


End Sub
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try use: WorksheetFunction.CountIf in your macro codes, like:

Sub copyover()

    Sheets("master").Select

If WorksheetFunction.CountIf(Range("D6:D200"), "yes") >= 1 Then

    Range("C6:C200").Select
    Selection.Copy
    Sheets("QouteSheet").Select
    Range("B21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

end if 

End Sub 

Open in new window

Avatar of Norie
Norie

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

Open in new window

Hi,

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

Open in new window

Regards
Avatar of Braulio cordova

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").PasteSpecial Paste:=xlPasteValues

Any Thoughts?

Thanks
Braulio
How is QuoteSheet written
QuoteSheet or QouteSheet
Could you send a dummy?
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
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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Thank you Rgonzo1971!

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.
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