Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
2

I already spent hours on this problem, but I didn't succeed in finding a working solution.

Here is my problem description:

I want to loop through a certain range of cells in one workbook and copy values to another workbook. Depending on the current column in the first workbook, I copy the values into a different sheet in the second workbook. When I execute my code, I always get the runtime error 439: object does not support this method or property.

My code looks more or less like this:

Sub trial()

Dim Group As Range
Dim Mat As Range
Dim CurCell_1 As Range
Dim CurCell_2 As Range

Application.ScreenUpdating = False

Set CurCell_1 = Range("B3") 'starting point in wb 1

For Each Group in Workbooks("My_WB_1").Worksheets("My_Sheet").Range("B4:P4")
    Set CurCell_2 = Range("B4") 'starting point in wb 2
    For Each Mat in Workbooks("My_WB_1").Worksheets("My_Sheet").Range("A5:A29")
        Set CurCell_1 = Cells(Mat.Row, Group.Column) 'Set current cell in the loop
        If Not IsEmpty(CurCell_1)
            Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).CurCell_2.Value = Workbooks("My_WB_1").Worksheets("My_Sheet").CurCell_1.Value 'Here it break with runtime error '438 object does not support this method or property
            CurCell_2 = CurCell_2.Offset(1,0) 'Move one cell down
        End If
    Next
Next

Application.ScreenUpdating = True

End Sub

I've done extensive research and I know how to copy values from one workbook to another if you're using explicit names for your objects (sheets & ranges), but I don't know why it does not work like I implemented it using variables. I also searched on stackoverlow and -obviously- Google, but I didn't find a similar problem which would answer my question.

So my question is: Could you tell me where the error in my code is or if there is another easier way to accomplish the same using a different way?

This is my first question here, so I hope everything is fine with the format of my code, the question asked and the information provided. Otherwise let me know.

share|improve this question

2 Answers

up vote 5 down vote accepted

5 Things...

1) You don't need this line

Set CurCell_1 = Range("B3") 'starting point in wb 1

This line is pointless as you are setting it inside the loop

2) You are setting this in a loop every time

Set CurCell_2 = Range("B4")

Why would you be doing that? It will simply overwrite the values every time. Also which sheet is this range in??? (See Point 5)

3)CurCell_2 is a Range and as JohnB pointed it out, it is not a method.

Change

Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).CurCell_2.Value = Workbooks("My_WB_1").Worksheets("My_Sheet").CurCell_1.Value

to

CurCell_2.Value = CurCell_1.Value

4) You cannot assign range by just setting an "=" sign

CurCell_2 = CurCell_2.Offset(1,0)

Change it to

Set CurCell_2 = CurCell_2.Offset(1,0)

5) Always specify full declarations when working with two or more objects so that there is less confusion. Your code can also be written as (UNTESTED)

Option Explicit

Sub trial()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim Group As Range, Mat As Range
    Dim CurCell_1 As Range, CurCell_2 As Range

    Application.ScreenUpdating = False

    '~~> Change as applicable
    Set wb1 = Workbooks("My_WB_1")
    Set wb2 = Workbooks("My_WB_2")

    Set ws1 = wb1.Sheets("My_Sheet")
    Set ws2 = wb2.Sheets("Sheet2") '<~~ Change as required

    For Each Group In ws1.Range("B4:P4")
        '~~> Why this?
        Set CurCell_2 = ws2.Range("B4")
        For Each Mat In ws1.Range("A5:A29")
            Set CurCell_1 = ws1.Cells(Mat.Row, Group.Column)
            If Not IsEmpty(CurCell_1) Then
                CurCell_2.Value = CurCell_1.Value
                Set CurCell_2 = CurCell_2.Offset(1)
            End If
        Next
    Next

    Application.ScreenUpdating = True
End Sub
share|improve this answer
Thank you very much! Your answer helped me a lot! I changed my code according to your suggestions and it run immediately as it should! Concering your point 2): Actually you're right, the sheet is missing. If I understood you right, then a Range refers to a specific cell, a sheet and a workbook, not only a cell. But since I move this range using Offset in every inner loop, I have to reset it in the outer loop. – Patrick May 28 '12 at 12:57
If I understood you right, then a Range refers to a specific cell, a sheet and a workbook, not only a cell. No :) A Range represents a cell, a row, a column, or a selection of cells that contains one or more contiguous blocks of cells IN a Sheet. – Siddharth Rout May 28 '12 at 13:11
up vote 1 down vote 
Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).CurCell_2.Value

This will not work, since CurCell_2 is not a method of Worksheet, but a variable. Replace by

Workbooks("My_WB_2").Worksheets(CStr(Group.Value)).Range("B4").Value
share|improve this answer
Thank you for answer! Sounds very logical to me. So now I clearly understand why the error message occurs. But how would you write the code if the range at the end is not always Range("B4"), but rather a changing range defined by the variable CurCell_2? – Patrick May 28 '12 at 10:14
CurCell_2.Value = ... ? Or something like ...Range (aVariableContainingAStringDefiningTheRange).Value = ... – JohnB May 28 '12 at 11:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.