Solved

maro to copy and paste from one worksheet to another based on a condition

Posted on 2016-10-17
11
70 Views
Last Modified: 2016-10-18
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
0
Comment
Question by:Braulio cordova
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41847640
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

0
 
LVL 34

Expert Comment

by:Norie
ID: 41847722
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

0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41847770
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Braulio cordova
ID: 41848184
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
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41848191
How is QuoteSheet written
QuoteSheet or QouteSheet
Could you send a dummy?
0
 

Author Comment

by:Braulio cordova
ID: 41848357
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
0
 
LVL 34

Expert Comment

by:Norie
ID: 41848481
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

0
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41848525
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
    Sheets("QuoteSheet").Activate
End Sub

Open in new window

0
 

Author Comment

by:Braulio cordova
ID: 41848529
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
0
 

Author Closing Comment

by:Braulio cordova
ID: 41848554
Thank you Rgonzo1971!

This is exactly while I need it.

Braulio
0
 
LVL 34

Expert Comment

by:Norie
ID: 41848730
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

0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question