Announcement

Collapse
No announcement yet.

Copy values from one worksheet to another based on one condition

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Copy values from one worksheet to another based on one condition



    Hello

    I need a code that will copy values from certain columns (columns D, F) ("worhsheet A") to another worksheet columns X,Z (of "worksheet B") whenever in the source worksheet value of column C is empty.

  • #2
    Hello and Welcome to the Forum

    Attaching a sample file would make everything easier ... for everybody
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Hello,

      Since you seem quite reluctant to attach a sample file ... you can build your solution by your own thanks to Advanced Filter ...

      see Debra's explanation :

      https://www.contextures.com/xladvfilter01.html

      Hope this will help
      If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

      Comment


      • #4
        Originally posted by Carim View Post
        Hello and Welcome to the Forum

        Attaching a sample file would make everything easier ... for everybody
        Hi Carim

        Thanks for your answer. I havent answer cause i was quite busy but thanks for chasing. The way I have fixed it is with the following code: Probably not the most efficient but it works for my needs

        Code:
        Sub Boleta_Clientes_Import()
        
        
        Dim Cols
        Dim i As Long, lRow As Long
        Dim coluRng As Range
        
        Application.ScreenUpdating = False
        Set coluRng = Columns("A:AJ").Cells
        lRow = coluRng(Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
        Cells.Find(What:="*", SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column).Row
        Sheets("BOLETA").Range("A1:AJ" & lRow).AutoFilter
        ActiveSheet.Range("A1:AJ" & lRow).AutoFilter Field:=3, Criteria1:=""
        ActiveSheet.Range("A1:AJ" & lRow).AutoFilter Field:=29, Criteria1:="#N/A"
        Cols = Array("c", "i", "j", "k", "l", "m", "n", "o", "p", "ad", "ae", "af", "ag", "ah", "ai", "aj")
        For i = LBound(Cols) To UBound(Cols)
        Range(Cols(i) & 1, Cols(i) & lRow).Copy Worksheets("BOLETAadj").Cells(1, i + 1)
        Next
        Worksheets("BOLETAadj").Columns.AutoFit
        Application.ScreenUpdating = True
        
        
        End Sub
        
        Sub Boleta_Clients_Import_Step2()
        
        Worksheets("Customer").Range("B4").Formula = "=BOLETAadj!C2"
        Worksheets("Customer").Range("D4").Formula = "=BOLETAadj!D2"
        Worksheets("Customer").Range("F4").Formula = "=BOLETAadj!H2"
        Worksheets("Customer").Range("I4").Formula = "=BOLETAadj!J2"
        Worksheets("Customer").Range("K4").Formula = "=0"
        Worksheets("Customer").Range("L4").Formula = "=BOLETAadj!K2"
        Worksheets("Customer").Range("P4").Formula = "=BOLETAadj!L2"
        Worksheets("Customer").Range("S4").Formula = "=BOLETAadj!M2"
        Worksheets("Customer").Range("U4").Formula = "=BOLETAadj!N2"
        Worksheets("Customer").Range("W4").Formula = "=BOLETAadj!B2"
        Worksheets("Customer").Range("X4").Formula = "=false"
        Worksheets("Customer").Range("Y4").Formula = "=BOLETAadj!K2"
        Worksheets("Customer").Range("AA4").Formula = "=BOLETAadj!G2"
        Worksheets("Customer").Range("AB4").Formula = "=BOLETAadj!I2"
        Worksheets("Customer").Range("AD4").Formula = "=BOLETAadj!O2"
        Worksheets("Customer").Range("AF4").Formula = "=0"
        Worksheets("Customer").Range("AG4").Formula = "=BOLETAadj!P2"
        Worksheets("Customer").Range("AI4").Formula = "=A4"
        Worksheets("Customer").Range("AJ4").Formula = "=A4"
        
        Worksheets("Customer").Range("Z4").Formula = "=VLOOKUP(AA5,PostCodes!B:E,4,FALSE)"
        
        
        End Sub
        As the step 2 copy data into a table formulas get copied into the last row of the table so no need to have code that copies the values until the last row with data.

        Anyway Carim, thanks a lot for your help!!!
        Last edited by royUK; 5 days ago.

        Comment


        • #5
          Glad you could fix your problem

          Thanks for your Thanks
          If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

          Comment


          • #6


            Why would you need VBA to set up formulas?

            Set up a template sheet in the workbook is the simplest way.

            If you really only have one criteria and the data is in a table format then AutoFilter can be automated to do this and will be the most efficient way.

            Note: I have added Code Tags to your previous post, please use them in future because they make your code easier to read and copy. All this is explained in the Forum Rules which you really ought to read.
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment

            Working...
            X