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

writetoevv

Board Regular
Joined
Mar 9, 2012
Messages
57
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..
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,184
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, [COLOR="#FF0000"]srcWS[/COLOR] 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
 

writetoevv

Board Regular
Joined
Mar 9, 2012
Messages
57
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..:biggrin:
let me restart my machine too..
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,184
You are very welcome. :)
 

writetoevv

Board Regular
Joined
Mar 9, 2012
Messages
57
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/ov2ij6tnbbmhrd3/Language translate problem.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:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,184
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?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,184
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:

writetoevv

Board Regular
Joined
Mar 9, 2012
Messages
57
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,333
Messages
5,486,215
Members
407,537
Latest member
Frank Nunez

This Week's Hot Topics

Top