0

I am trying to paste values from one worksheet, called "Economic Assumptions", to another, called "Economic Simulations".

To give a little context, rows 17, 20, 23 and 26 in "Economic Assumptions" generate random numbers, which then affect what comes out in row 14. I want to copy what is in row 14 to row 3 in "Economic Simulations", starting from column B.

It is also ideal for me to do this in a loop, as I need to repeat this 1000 times (i.e. I need to copy 1000 different row 14s).

My code is as follows:

Sub Macro1()

Sheets("Economic Assumptions").Activate

For i = 1 To 1000

    Range("B17:BL17") = WorksheetFunction.RandArray(1, 63)
    Range("B20:BL20") = WorksheetFunction.RandArray(1, 63)
    Range("B23:BL23") = WorksheetFunction.RandArray(1, 63)
    Range("B26:BL26") = WorksheetFunction.RandArray(1, 63)
    
    Sheets("Economic Simulations").Range(Cells(i + 2, 2), Cells(i + 2, 64)).Value = Sheets("Economic Assumptions").Range("B14:BL14").Value
    
Next i

End Sub

Everything works fine except for the line which says Sheets("Economic Simulations").Range(Cells(i + 2, 2), Cells(i + 2, 64)).Value = Sheets("Economic Assumptions").Range("B14:BL14").Value, which gives me "application-defined or object-defined error" when run. I have tried modifying the line by changing Sheets to Worksheets or even Application.Worksheets, but they all do not work.

Any suggestions as to how I can do this copy and pasting will be greatly appreciated! If it makes any difference, the range I am copying from is always fixed; it is the range I am pasting to that needs to increase by 1 row each time.

Ethan Mark
  • 293
  • 1
  • 9
  • Your `Cells` objects need to be qualified with the same sheet that you used for the `Range` - Without that, it assumes `ActiveSheet` - It does not assume the same sheet as many people think. – braX Oct 14 '21 at 08:55

1 Answers1

0

This is certainly a duplicate question and should be closed as such, but I can't find a definitive thread.

The problem is that you are not fully qualifying Cells with a sheet reference so they will refer to the active sheet which you have previously set as the other sheet. A With clause is a neat way of doing this.

Sub Macro1()

For i = 1 To 1000
    With Sheets("Economic Assumptions")
        .Range("B17:BL17") = WorksheetFunction.RandArray(1, 63)
        .Range("B20:BL20") = WorksheetFunction.RandArray(1, 63)
        .Range("B23:BL23") = WorksheetFunction.RandArray(1, 63)
        .Range("B26:BL26") = WorksheetFunction.RandArray(1, 63)
    End With
    
    With Sheets("Economic Simulations")
        .Range(.Cells(i + 2, 2), .Cells(i + 2, 64)).Value = Sheets("Economic Assumptions").Range("B14:BL14").Value
    End With
Next i

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • 1
    Thank you for your answer! I apologise if this has been asked before. I have tried your code and it works! May I know why, in `With Sheets("Economic Simulations")`, the "dots" are on `Cells` and not `Range`? – Ethan Mark Oct 14 '21 at 09:02
  • @braX - I don't think that's the case. The `Range` is defined by the `Cells` which refer to the sheet. Arguably, good practice to include it though. – SJR Oct 14 '21 at 09:02
  • @EthanMark - I have now added the extra dot. I have sketched out an answer above, but there is a thread somewhere which goes into detail and IIRC the conclusion was that the first dot is not required (but does no harm). – SJR Oct 14 '21 at 09:03
  • @SJR Right! And it seems that, while the dot is not necessary for `Range`, it is certainly required for `Cells` (I tried to remove the dots for `Cells` and it did not work). How strange (to me at least)! – Ethan Mark Oct 14 '21 at 09:05
  • That's because as I said above if you do not explicitly give a sheet reference, the active sheet is assumed and the active sheet is not "Economic Simulations". – SJR Oct 14 '21 at 09:06
  • @SJR Oh, I thought that was already handled by using `With`. In other words, I thought that by using `With`, the `With` worksheet becomes the active sheet. – Ethan Mark Oct 14 '21 at 09:11
  • No, `With` has no effect without the dots. – SJR Oct 14 '21 at 09:20