Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: VBA script for conditional copy, format & paste from workbook1 to workbook2

  1. #1
    Board Regular
    Join Date
    Mar 2012
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question VBA script for conditional copy, format & paste from workbook1 to workbook2

    In workBook1, i have five columns (A,B,C,D,E) with data. Here, each row is a separate record & is going have unique data.
    sample data is given below.

    A B C D E
    1 Q1 A1 A2 A3 A4
    2 Q2 B1 B2 B3 B4

    In workBook1, when i execute a VBA script , the following needs to done sequentially.
    1. First, create a new workbook (for example workbook2). and, steps 2 to 6 should be repeated for all rows of workbook 1.
    2. value Q1 of workbook1 should be copied to B2 in workbook2
    3. if value A1 cell is in green color, then value A1::1;; should be add/appended to I2 column in workbook2. Else, append A1::0;; to I2 column of workbook2.
    4. if value A2 cell is in green color, then value A2::1;; should be appended to I2 column of workbook2. Else, append A2::0;; to I2 column of workbook2.
    5 if value A3 cell is in green color, then value A3::1;; should be appended to I2 column of workbook2. Else, append A3::0;; to I2 column of workbook2.
    6. if value A4 cell is in green color, then value A4::1;; should be appended to I2 column of workbook2. Else, append A4::0;; to I2 column of workbook2.
    7. Save workbook2 & close workbook2.

    Example: for your reference, I2 Column Workbook2 should be like below.
    chandu::1;;Shekar::0;;Raju::0;;Gopal::0;;
    Venkat::0;;Gopi::1;;Stephen::0;;minda::0;;

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,262
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA script for conditional copy, format & paste from workbook1 to workbook2

    Is the whole cell colored green or only the font colored green? How is the green color applied to the cells? Is it done manually or by conditional formatting?
    Last edited by mumps; Jul 17th, 2019 at 02:58 PM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Mar 2012
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA script for conditional copy, format & paste from workbook1 to workbook2

    Quote Originally Posted by mumps View Post
    Is the whole cell colored green or only the font colored green? How is the green color applied to the cells? Is it done manually or by conditional formatting?
    appreciates for your quick response. manually select a cell of a record & by using fill color feature.

    Actually, either way by fill color or font color is fine to me.

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,262
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA script for conditional copy, format & paste from workbook1 to workbook2

    Make sure you manually color the cells green. It's important that you select the proper shade of green whose colorindex=43. Try:
    Code:
    Sub writetoevv()
        Application.ScreenUpdating = False
        Dim LastRow As Long, rng As Range, val As Range, scrWS As Worksheet, desWB As Workbook
        Set srcws = ThisWorkbook.Sheets("Sheet1")
        LastRow = srcws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set desWB = Workbooks.Add
        For Each rng In srcws.Range("A1:A" & LastRow)
            Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = rng
            For Each val In srcws.Range("B" & rng.Row & ":E" & rng.Row)
                If val.Interior.ColorIndex = 43 Then
                    Cells(Rows.Count, val.Column + 1).End(xlUp).Offset(1, 0) = Cells(Rows.Count, val.Column + 1).End(xlUp).Offset(1, 0) & val & "::1;;"
                Else
                    Cells(Rows.Count, val.Column + 1).End(xlUp).Offset(1, 0) = Cells(Rows.Count, val.Column + 1).End(xlUp).Offset(1, 0) & val & "::0;;"
                End If
            Next val
        Next rng
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Board Regular
    Join Date
    Mar 2012
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA script for conditional copy, format & paste from workbook1 to workbook2

    hi

    when i tried, i got "runtime error 91" on this line
    LastRow = srcws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    object variable or with block variable not set..

    would it be feasible to change it to any color of "fill color".
    Last edited by writetoevv; Jul 17th, 2019 at 05:47 PM.

  6. #6
    Board Regular
    Join Date
    Mar 2012
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA script for conditional copy, format & paste from workbook1 to workbook2

    i myself fixed runtime error 91 by commenting "LastRow" & took static value of 10 to test the macro.

    macro is working, how ever not matching with expected result.
    macro just pasting ::0;; in C column to F column from 2nd row(record) to 6th row(record).

    expected result is 'i2' cell of Workbook2 should contain the value "xddd::1;;ddd::0;;dddd::0;;adal::0;;"

  7. #7
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,262
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA script for conditional copy, format & paste from workbook1 to workbook2

    I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  8. #8
    Board Regular
    Join Date
    Mar 2012
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA script for conditional copy, format & paste from workbook1 to workbook2

    here is the link

    https://www.dropbox.com/s/fjky1l4xjy...aste.xlsm?dl=0

    simple explanation is also provided.

  9. #9
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,262
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA script for conditional copy, format & paste from workbook1 to workbook2

    Try:
    Code:
    Sub writetoevv()
        Application.ScreenUpdating = False
        Dim LastRow As Long, rng As Range, val As Range, scrWS As Worksheet, desWB As Workbook, x As Long: x = 2
        Set srcws = ThisWorkbook.Sheets("Sheet1")
        LastRow = srcws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set desWB = Workbooks.Add
        For Each rng In srcws.Range("A1:A" & LastRow)
            Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = rng
            For Each val In srcws.Range("B" & rng.Row & ":E" & rng.Row)
                If val.Interior.ColorIndex = 43 Then
                    Cells(x, 9) = Cells(x, 9) & val & "::1;;"
                Else
                    Cells(x, 9) = Cells(x, 9) & val & "::0;;"
                End If
            Next val
            x = x + 1
        Next rng
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #10
    Board Regular
    Join Date
    Mar 2012
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA script for conditional copy, format & paste from workbook1 to workbook2

    Hi mumps

    i got this output on ith column of new book. it seems it is always going to 'else' part of code & not copying content of cells.
    also, Q11111, Q22222,Q33333,Q4444 needs to be copied on B Coloumn in new workbook.
    Output on ith Column
    ::0;;::0;;::0;;::0;;
    ::0;;::0;;::0;;::0;;
    ::0;;::0;;::0;;::0;;
    ::0;;::0;;::0;;::0;;
    Last edited by writetoevv; Jul 18th, 2019 at 04:07 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •