VBA script for conditional copy, format & paste from workbook1 to workbook2
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 32

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

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

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

    also, in new workbook , Q11111, Q22222,Q33333,Q4444 needs to be copied on B Coloumn of respective rows . [Pardon me, though i mentioned it in initial post, i missed this part in dropbox excel file. i will update.]

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

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

    Quote Originally Posted by writetoevv View Post
    also, in new workbook , Q11111, Q22222,Q33333,Q4444 needs to be copied on B Coloumn of respective rows . [Pardon me, though i mentioned it in initial post, i missed this part in dropbox excel file. i will update.]
    with bit of hands on, copying Q11111, Q22222,Q33333,Q4444 in B Coloumn of new workbook has been implemented & done.

    however, ith column of new worksheet issue still exist.
    Output on ith Column
    ::0;;::0;;::0;;::0;;
    ::0;;::0;;::0;;::0;;
    ::0;;::0;;::0;;::0;;
    ::0;;::0;;::0;;::0;;

    Thanks & Regards.
    Last edited by writetoevv; Jul 19th, 2019 at 03:37 AM.

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

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

    1 obersationi noticed

    code always going to else part of if ..else, because "val.Interior.ColorIndex returning -4142" & we are checking whether it is equal to 43.
    as it is not matching or equal, it is always going to else part of code.

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

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

    As I mentioned on Post #4 , you have to make sure that the colorindex of the shade of green you use in your cells matches the colorindex in the code. Run the following macro and change the range (in red) to a cell that is colored in green. This will display the colorindex of the shade of green you are using. Replace the "43" in the code with the displayed colorindex. That should fix the problem.
    Code:
    Sub test()
        MsgBox Range("E2").Interior.ColorIndex
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

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

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

    As you said ,i used test(), got 14 as colorindex & used colorIndex as 14 in the macro you provided.
    However, still the o/p remains same. it seems the value of 'val' is nothing on debug..i think it is the problem. further, it always going else part..pls check o/p.
    Output on ith Column
    ::0;;::0;;::0;;::0;;
    ::0;;::0;;::0;;::0;;
    ::0;;::0;;::0;;::0;;
    ::0;;::0;;::0;;::0;;

    Meanwhile, i tried a different approach..it is producing result which is 80% of my expectations. few small issues still exist. it needs to be fixed. i shared macro below.

    O/P:--
    A1111::0;;
    B1111::1;;
    C1111::0;;
    D1111::0;;
    A2222::0;;
    B2222::0;;
    C2222::1;;
    D2222::0;;
    A3333::0;;
    B3333::0;;
    C3333::0;;
    D3333::1;;
    A4444::1;;
    B4444::0;;
    C4444::0;;
    D4444::0;;

    Macro:
    Sub writetoevv_Ultimate_Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long, val As Range, scrWS As Worksheet, desWB As Workbook, x As Long: x = 2
    Set srcws = ThisWorkbook.Sheets("Sheet1")

    Dim rng As Range: Set rng = Application.Range("B1:E4")
    Dim cel As Range
    For Each cel In rng.Cells
    With cel
    For Each val In srcws.Range("B" & rng.Row & ":E" & rng.Row)
    If cel.Interior.ColorIndex = 14 Then
    Cells(x, 9) = cel & "::1;;"
    Else
    Cells(x, 9) = cel & "::0;;"
    End If
    Next val
    x = x + 1
    End With
    Next cel

    Application.ScreenUpdating = True


    End Sub
    Last edited by writetoevv; Jul 19th, 2019 at 09:01 AM.

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

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

    Click here to download your file. It seems to be working properly for me.

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

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

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

    https://www.dropbox.com/s/m5yfw9e7g7...pshot.jpg?dl=0

    please find attached snapshot.. it did not work for me..

    donot know whether it is an issue with excel version..my excel version is 2007.
    Last edited by writetoevv; Jul 19th, 2019 at 12:17 PM.

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

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

    This is what I got.
    Sheet1

     BCDEFGHI
    2Q11111      A1111::0;;B1111::1;;C1111::0;;D1111::0;;
    3Q22222      A2222::0;;B2222::0;;C2222::1;;D2222::0;;
    4Q33333      A3333::0;;B3333::0;;C3333::0;;D3333::1;;
    5Q4444      A4444::1;;B4444::0;;C4444::0;;D4444::0;;

    Excel Tabellen im Web darstellen - Excel Jeanie Html 4
    Practice makes perfect. I'm very far from perfect so I'm still practising.

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

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

    yes, that is correct format..appreciates..

    donot know why im getting w/o values..i used ur vba script as it is except commenting following line..' LastRow = srcws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ...instead i used a static value of 5/10..but it didnt worked for me

    could you share ur macro enabled excel sheet with me..pls

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

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

    Click the link in Post #16 for the file. You shouldn't have to change any line in the macro.
    Last edited by mumps; Jul 19th, 2019 at 12:53 PM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

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
  •