0

I have two lines of code the first works fine, the second does not "paste" any values in the defined range. I cannot see where the error is as I am certain both ranges in the second line span the same area?

Sheets("Loans").Range("A101:K101").Value = Sheets("Loan Backup").Range("N14:X14").Value

Sheets("Loans").Range("A2:K100").Value = Sheets("Loan Backup").Range(Cells((l - 1) * 99 + 2, 1), Cells(l * 99 + 1, 11)).Value
Community
  • 1
  • 1
user3737057
  • 121
  • 3
  • 12
  • Why You don't use fe: Range("N14:X14") instead Range(Cells((l - 1) * 99 + 2, 1), Cells(l * 99 + 1, 11)) ? Second form is't work because Range have different format. – starko Sep 03 '15 at 09:25
  • I switched the second line to Sheets("Loans").Range(Cells(2, 1), Cells(100, 11)).Value = Sheets("Loan Backup").Range(Cells((l - 1) * 99 + 2, 1), Cells(l * 99 + 1, 11)).Value and still no success – user3737057 Sep 03 '15 at 09:33
  • So as the range in my second line will be dynamic do you know what kind of code I need to be able to make it act like line 1? Preferably a method that is not copy/paste – user3737057 Sep 03 '15 at 09:38
  • @Starko - that is absolutely **not** true. You do however need to qualify the `Range` and `Cells` calls with the same worksheet: `Sheets("Loan Backup").Range(Sheets("Loan Backup").Cells((l - 1) * 99 + 2, 1), Sheets("Loan Backup").Cells(l * 99 + 1, 11)).Value` – Rory Sep 03 '15 at 09:46

2 Answers2

0

I read about Range and you can use cell in range like this: 

Sub SelectCols()

    Dim Col1 As Integer
    Dim Col2 As Integer

    Col1 = 2
    Col2 = 4

    Range(Cells(1, Col1), Cells(1, Col2)).Select

End Sub

I hope that's help you.

starko
  • 1,150
  • 11
  • 26
0

By default Cells(x,y) references the activesheet, so to refer to another sheet while using the Cells(x,y) syntax we must qualify it. i.e. the code from my questions should be: 

Sheets("Loans").Range("A2:K100").Value = Range(Sheets("Loan Backup").Cells((l - 1) * 99 + 2, 1), Sheets("Loan Backup").Cells(l * 99 + 1, 11)).Value

My source was this question.

Community
  • 1
  • 1
user3737057
  • 121
  • 3
  • 12
  • You should qualify the first `Range` call too. Also be aware that in a worksheet module, `Cells` without a qualifier refers to the sheet containing the code, not the active sheet (though they could of course be the same). – Rory Sep 03 '15 at 11:35