run this code line with error. pls comment

Question

Not working

Range("C5:C" & iTotalRows + 1).Value = src.Worksheets("Loan").Range(Cells(4, 2), Cells(12, 2)).Value

is working

Range("D5:D" & iTotalRows + 1).Value=src.Worksheets("Loan").Range("C4:C" & iTotalRows).Value

Answer 1

You need to fully qualify your Range and Cells - try one of these

ActiveSheet.Range("C5:C" & iTotalRows + 1).Value = src.Worksheets("Loan").Range(src.Worksheets("Loan").Cells(4, 2), src.Worksheets("Loan").Cells(12, 2))
'Or
With src.Worksheets("Loan")
    ActiveSheet.Range("C5:C" & iTotalRows+1).Value = .Range(.Cells(4, 2), .Cells(iTotalRows, 2)).Value
End With

If we simplify this, your current code is effectively this:

ActiveSheet.Range("B2:B3").Value = Sheet1.Range(ActiveSheet.Cells(1,1),ActiveSheet.Cells(2,1)).Value

Which will only work if Sheet1 is the ActiveSheet - otherwise, Excel can't find the cell ActiveSheet.Cells(1,1) in worksheet Sheet1

Answer 2

Understanding how to create and use workbook and worksheet variables will save you from lots of trouble.

You should always explicitly refer to the workbooks and worksheets to whitch the objects or collections you want to use, belong.

In this case you are using the .cells collection.

If you don't specify which worksheet this collection belongs to, excel will try to figure it out on it's own which could lead to unwanted behavior or even errors. Errors are actually the good scenario because they are easily traceable.

Using ActiveSheet references is a bad practice as well.

I would do it like so:

Dim src As Workbook 'source workbook
Dim dest As Workbook 'destination workbook
Dim srcWs As Worksheet 'source worksheet
Dim destWs As Worksheet 'destination worksheet
Set src = Application.Workbooks("Name of the source Workbook")
Set dest = Application.Workbooks("Name of the destination Workbook")
Set srcWs = src.Worksheets("Loan") 'assign the worksheet named "Loan", which belongs to the source workbook, to the srcWs variable
Set destWs = dest.Worksheets("Name of the destination Worksheet")

destWs.Range("C5:C" & iTotalRows + 1).Value = srcWs.Range(srcWs.Cells(4, 2), srcwsCells(12, 2)).Value

Also a very useful way to refer to the workbook in which the code is located is ThisWorkbook.

This way, you can get access to your workbook/worksheet properties and objects just by using the corresponding reference followed by a dot and then VBE's Intellisence will help you find what you're looking for.

Finally, I am not sure what you're trying to achieve exactly, but you should keep in mind that this:

destWs.Range("C5:C" & iTotalRows + 1).Value = srcWs.Range(srcWs.Cells(4, 2), srcwsCells(12, 2)).Value

will only assign as many values from the source range to the destination range, as the iTotalRows + 1 , will allow. So for example if iTotalRows=9 then you would have the following result: