Application defined error while copying a range of cells from a workbook to the workbook i have created in the code

Question

I got the error at the starred lines. I have browsed a lot I found many answers i copied the same but still am getting this error . I am not sure of what wrong i am doing.

Please help. Thanks in advance.

Sub splitIntoCsv()
    Dim wbIn
    Dim wbIn1 As Workbook
    Dim header, ranges, range_lower, range_upper, rangeCopy As Variant
    Dim rangeVariable As String
    Dim commacheck, rows, columns As Integer

    Set wbIn = CreateObject("Excel.Application")
    wbIn.Workbooks.Add
    wbIn.Worksheets(1).Name = "TestData"
    Set wbIn1 = Workbooks.Open(Sheet1.Range("B1").Value, True, True)

    rows = wbIn1.Sheets(1).UsedRange.rows.Count
    columns = wbIn1.Sheets(1).UsedRange.columns.Count
    header = Split(ThisWorkbook.Sheets(1).Range("B2").Value, ",")
    rangeVariable = ThisWorkbook.Sheets(1).Range("B3").Value
    commacheck = InStr(rangeVariable, ",")

    If commacheck = 0 Then
        rangeVariable = rangeVariable & ","
    End If

    ranges = Split(rangeVariable, ",")
    For i = LBound(ranges) To UBound(ranges) - 1
        For j = LBound(header) To UBound(header)
            wbIn.Worksheets(1).Cells(1, j + 1).Value = header(j)
        Next j

        range_lower = Split(ranges(i), "-")(0)
        range_upper = Split(ranges(i), "-")(1)

        With wbIn1.Sheets(1)
           rangeCopy = .Range(.Cells(1 + range_lower, 1), .Cells(1 + range_upper, columns)).Value
        End With

        With wbIn.Worksheets(1)  
          *********        
            .Range(.Cells(1 + range_lower, 1), .Cells(1 + range_upper, columns)).Value = rangeCopy
          *********
        End With

    Next i

    wbIn1.Close SaveChanges:=True

    wbIn.DisplayAlerts = False
    wbIn.Worksheets(1).SaveAs Filename:="D:\RaghuDev\raghu.csv", FileFormat:=xlCSV, CreateBackup:=False
    wbIn.Quit        
End Sub

what is your error number and/or description?? Which application do you start it from?? — Kazimierz Jawor, Apr 18 '13 at 12:47
what are values of all variables in error line?? `range_lower`, `range_upper`, `columns` ?? — Kazimierz Jawor, Apr 18 '13 at 12:56
me neither- no error. the only idea I have at the moment- change your variable name from `Columns` into `col` in whole subroutine. — Kazimierz Jawor, Apr 18 '13 at 13:09
no issues just my question was irrelevant. I see no issues as you get past the with statement ok, there for the sheet is working ok. please try `wbIn.Worksheets(1).Range(wbIn.Worksheets(1).Cells(1 + range_lower, 1), wbIn.Worksheets(1).Cells(1 + range_upper, columns)).Value = wbIn1.Sheets(1).Range(wbIn1.Sheets(1).Cells(1 + range_lower, 1), wbIn1.Sheets(1).Cells(1 + range_upper, columns)).Value` — glh, Apr 18 '13 at 13:22
also try discretely setting your variables `Dim wbIn as workbook` — glh, Apr 18 '13 at 13:24
what excel do you have? and what was the exact error, e.g. `Runtime error 1004: Paste method of worksheet class failed.` also see http://support.microsoft.com/kb/905164 — glh, Apr 18 '13 at 13:37
excel 2007.run time error 1004 application-defined or object defined error. — Raghu Dev, Apr 18 '13 at 13:41

glh · Answer 1 · 2013-04-18T13:43:28.673

0

I don't get the same error but can you try the following test in a new excel report? I've tried to recreate the critical area of you problem.

Just be sure to put in some sample data and select a range of 1 by 3.

Sub test()

    Dim wbIn
    Set wbIn = CreateObject("Excel.Application")
    wbIn.Workbooks.Add
    wbIn.Worksheets(1).Name = "TestData"

    Dim r As Variant, columns As Integer

    r = Selection.Value
    columns = 3

    With wbIn.Worksheets(1)
        .Range(.Cells(1, 1), .Cells(1, columns)).Value = r
    End With

End Sub

Also try to define the variants:

ReDim rangeCopy(1 to (range_upper - range_lower + 1), 1 to columns) as Variant

With wbIn1.Sheets(1)
   rangeCopy = .Range(.Cells(1 + range_lower, 1), .Cells(1 + range_upper, columns)).Value
End With

With wbIn.Worksheets(1)  
    .Range(.Cells(1 + range_lower, 1), .Cells(1 + range_upper, columns)).Value = rangeCopy
End With

edited Apr 18 '13 at 13:43

answered Apr 18 '13 at 13:33

glh

4,900
3
23
40

I have tried your code and it didnt give any error. what should i get as output. – Raghu Dev Apr 18 '13 at 13:38
I think you need to define the `rangeCopy` better using `redim`, I've had this before. I'll update – glh Apr 18 '13 at 13:48
Thanks for sparing this much time for my issue. – Raghu Dev Apr 18 '13 at 13:57
your welcome. just also be sure the range_lower, range_upper and columns don't exceed excels limits... – glh Apr 18 '13 at 13:58
hmm i made sure that they wont exceed the limits. – Raghu Dev Apr 18 '13 at 14:00
I think you know where the issue is. the `rangecopy` is the key. try alternat ways to transfer the data over, .copy, .range=.range, etc.... – glh Apr 18 '13 at 14:02
.Range(.Cells(1 + range_lower, 1), .Cells(1 + range_upper, columns)).Value = rangeCopy in this statement if i replace rangeCopy by a sample string say "hello" it works fine.Hope it would help to resolve or add sthg. – Raghu Dev Apr 18 '13 at 14:03
Hi i found the issue. whats happening is that in the cells am trying to copy has strings of very huge size . when i replaced the cells with sample strings say "hello" the entire code is working fine . but when the strings have huge size the second with statement fails. can u explain me the reason for that? – Raghu Dev Apr 19 '13 at 06:19
the only thing that comes to mind is the system or pc your using has hit its ram size limit reached causing an error when you paste these values to the second workbook. – glh Apr 19 '13 at 06:58
K so i cannot the group of cells. As a work around i will copy individual cells . Thanks – Raghu Dev Apr 19 '13 at 07:06
Please keep me posted.. :) – glh Apr 19 '13 at 07:11
Hi am sry for replying this late . How can i check if the ram size is exceeding.. – Raghu Dev Apr 24 '13 at 06:27

Application defined error while copying a range of cells from a workbook to the workbook i have created in the code

1 Answers1