Pasting results of an automated calculation into a different sheet

[Bamh1]

Hello,

I have a sub called abc which does some calculations and returns the results in the range b2:i2 of Sheet1. I want to run this sub multiple times, and each time paste the results in Sheet2 such that next results will be pasted in the row below the previous one. I have written the following code, but it doesn't work:

Sub abc_sim ()
Dim i As Integer, c As Integer
Dim rs1 As Range, rs2 As Range

c = 1
rs1 = Worksheets("Sheet1").Range("b2:i2")
rs2 = Worksheets("Sheet2").Range("b2:i2")

For i = 1 To 100
Call abc
rs1.Copy
rs2.Offset(c, 0).PasteSpecial Paste:=xlPasteValues
c = c + 1
Next i

End Sub


Could someone please help fixing this code.


Thank you,


Shawn

 

[Osvaldo Palmeiro]

Hello, Shawn.
Please try this version.

Sub abc_sim()
 Dim i As Long
  Application.ScreenUpdating = False
  For i = 1 To 100
   Call abc
   Sheets("Sheet1").[B1:I1].Copy
   Sheets("Sheet2").Cells(Rows.Count, 2).End(3)(2).PasteSpecial xlValues
  Next i
End Sub

 

[Bamh1]

Hello, Shawn.
Please try this version.

Sub abc_sim()
 Dim i As Long
  Application.ScreenUpdating = False
  For i = 1 To 100
   Call abc
   Sheets("Sheet1").[B1:I1].Copy
   Sheets("Sheet2").Cells(Rows.Count, 2).End(3)(2).PasteSpecial xlValues
  Next i
End Sub

Hi Osvaldo, Thank you for your response. I tried it, but I get a runtime error (script out of range), and when I debug the line that has the paste values is highlighted. Any suggestions?

Thank you,

 

[Osvaldo Palmeiro]

Please, make sure your sheet name is spelled exactly as Sheet2, like in the code, with no spaces before or after that name.

 

[Bamh1]

Please, make sure your sheet name is spelled exactly as Sheet2, like in the code, with no spaces before or after that name.

It worked. Thank you so much.
To understand this code better, could you be kind enough to tell me what those two parameters after .end (2)(3) do?

 

[MARK858]

The (3) is a shorthand method of referring to the xlDirection (as far as I know it is undocumented), used rather than the official enumeration (see the table below)

Shorthand Enumeration	Constant	Official enumeration
1	xlToLeft	-4159
2	xlToRight	-4161
3	xlUp	-4162
4	xlDown	-4121

The (2) refers to the Range.Item property. See the article in the link below by Alan Beban (republished by Chip Pearson) which explains it a bit better than Microsoft's page does.
I'd read it all but the single indexing method section is what is being used here to Offset the cell by one row.

Cells Within Ranges

 

[Osvaldo Palmeiro]

The (2) is a shorthand method of referring t...

The (3) refers to the Range.Item property. ...

Related to my code it seems that the explanations about (2) and (3) are inverted one with each other.

 

[MARK858]

about (2) and (3) are inverted one with each other.

Oops they are sorry. Edited now

 

[Bamh1]

The (3) is a shorthand method of referring to the xlDirection (as far as I know it is undocumented), used rather than the official enumeration (see the table below)

Shorthand Enumeration	Constant	Official enumeration
1	xlToLeft	-4159
2	xlToRight	-4161
3	xlUp	-4162
4	xlDown	-4121

The (2) refers to the Range.Item property. See the article in the link below by Alan Beban (republished by Chip Pearson) which explains it a bit better than Microsoft's page does.
I'd read it all but the single indexing method section is what is being used here to Offset the cell by one row.

Cells Within Ranges

Thank you Mark for your detailed explanation. I read the follow up comment as well.

 

[Bamh1]

Related to my code it seems that the explanations about (2) and (3) are inverted one with each other.

Thanks again