Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 33

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

  1. #21
    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

    thanks..it is working perfectly..Thanks you!!

    but when i manually copy & paste #16 vba code in to a macro, while running macro, my excel has been reporting below issue on that particular line. strange.

    Runtime Error - 91

    Object variable or with block variable not set..

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

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

    My apologies. I just noticed a typo (in red) in my macro. Try the following version:
    Code:
    Sub writetoevv()
        Application.ScreenUpdating = False
        Dim LastRow As Long, rng As Range, val As Range, srcWS 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
        Columns("I").AutoFit
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #23
    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

    though i updated srcWS, srcWS still showing above error..i think i need to flush cache of excel..
    all other variable default values are good , except our friend srcWS..
    let me restart my machine too..

  4. #24
    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,

    You made my day..Thank you very much..

    Thanks & Regards
    Vishy

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

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

    You are very welcome.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

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

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

    Dear mumps,

    my excel file may contain either english or Telugu language.
    in MS excel workbooks/sheets, 0809 means English (U.K.), 0409 means English (U.S.), 044A to Telugu language.
    using our macro, copy pasting english language text has no issues.
    where as, if my content is telugu language, our macro could not able to copy content in a correct format.
    please see attached snapshot.

    https://www.dropbox.com/s/ov2ij6tnbb...oblem.jpg?dl=0

    any suggestion on this.

    Note:if u search for "Specifying a Language for the TEXT Function" in google, u may find a link for English (U.K.), 0409 means English (U.S.), 044A to Telugu language.
    Last edited by writetoevv; Jul 21st, 2019 at 03:29 PM.

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

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

    To be honest, I don't know why it's not working. The macro doesn't refer to any text in any language, it uses the cell color index. Can you upload a copy of your file in your language?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  8. #28
    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,

    here is the link to sample excel doc with content

    https://www.dropbox.com/s/2719tagw3w...uage.xlsx?dl=0

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

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

    Click here for your file. I colored some of cells and you can see that the macro worked properly.
    Code:
    Sub writetoevv()
        Application.ScreenUpdating = False
        Dim LastRow As Long, rng As Range, val As Range, srcWS 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("A2: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
        Columns("I").AutoFit
        Application.ScreenUpdating = True
    End Sub
    Last edited by mumps; Jul 22nd, 2019 at 08:03 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #30
    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,

    due to certain requirement of further process, i have been converted newly created workbook as excel csv format(Microsoft Office Excel Comma Separated Values File (.csv)). csv file has been created via vba code. issue has been coming because of that.

    if i save it as an excel file(Microsoft Office Excel Worksheet (.xlsx)), i donot see any issue.
    Last edited by writetoevv; Jul 22nd, 2019 at 03:20 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
  •