Hi - I am getting a refresher on building macros. Below is a list of steps that I am trying to automate. I am wondering if someone would be able to show me what is an efficient macro to use to complete these steps.

I am happy to send the file over, if it makes it easier to follow.

Sheet1 is where the results will be generated based on a single Input Cell (A2), and it has formulas in it that generate the results in column F (F2 thru F40, though ideally the macro would allow flexibility to make the range shorter or longer, such as F2 thru F10, or F2 thru F100. But the only cell that changes in this sheet will be Cell A2.

Sheet2 is simply a list of input values in column A, in the range of A1:A2473. These values in sheet 2 will be pasted into sheet1!A2, but one at a time, generating different results in column F of Sheet1 (due to formulas in that sheet). The values will go into Sheet1!A2, one at a time.

Sheet3! is where the results will be pasted from Sheet1! A list of resulting values will be appended to in sheet3 (i.e., a long list in column A), with the results that are generated in Sheet1, column F.

So the exact list of steps is as follows:

1. Copy the value in Sheet2!A1
2. Paste Result of that cell into Sheet1!A2
3. Copy any cells that contain values in Sheet1!F2:F40 (though preferably allow flexible range of more or less values)
4. Paste (Special Values) those results from Sheet1!F2:F40 into Sheet3!A1:A39

//BEGIN TO REPEAT THE PROCESS, but moving down one cell in Sheet2 to the next value in the list

5. Copy the value in Sheet2!A2 (i.e., offset down 1 cell from step 1, etc..)
6. Paste Result of that cell into Sheet1!A2 (this cell stays the same, and continues to receive the results for each value that is in Sheet2, one by one)
7. Copy any cells that contain values in Sheet1!F2:F40 (though preferably allow flexible range of more or less values)
8. Paste (Special Values) those results from Sheet1!F2:F40 into Sheet3!A40:A79 (i.e., Jump to end of last paste that occurred and continue to append this list with each successive iteration.

Thanks for any assistance! Just let me know if those steps are unclear.

Hi Fred,

Am Sun, 10 May 2015 08:57:46 -0700 (PDT) schriebst du in
microsoft.public.excel.programming:

1. Copy the value in Sheet2!A1
2. Paste Result of that cell into Sheet1!A2
3. Copy any cells that contain values in Sheet1!F2:F40 (though preferably allow flexible range of more or less values)
4. Paste (Special Values) those results from Sheet1!F2:F40 into Sheet3!A1:A39

try:

Sub Test()
Dim LRow1 As Long, LRow2 As Long, LRow3 As Long
Dim i As Long

Application.ScreenUpdating = False
With Sheets("Sheet2")
LRow2 = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LRow2
Sheets("Sheet1").Range("A2") = .Cells(i, 1)
LRow1 = Sheets("Sheet1").Cells(Rows.Count, "F").End(xlUp).Row
LRow3 = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Sheet1").Range("F2:F" & LRow1).Copy _
Destination:=IIf(LRow3 = 1, Sheets("Sheet3").Range("A1"), _
Sheets("Sheet3").Cells(LRow3, 1)(2))
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks very much for the response Claus.

It seems to have worked as intended...except for one exception. The values didn't seem to PasteSpecialvalues.

Instead, each of the results in Sheet3 reads: =#REF!&#REF!&#REF!&#REF!&#REF!

This may be because I have formulas in Sheet1!F2:F40. Do you happen to know if I can make a minor tweak to the code so that the values show up?

Hi Fed,

Am Mon, 11 May 2015 12:10:10 -0700 (PDT) schrieb :

It seems to have worked as intended...except for one exception. The values didn't seem to PasteSpecialvalues.

then change the code to:

Sub Test()
Dim LRow1 As Long, LRow2 As Long, LRow3 As Long
Dim i As Long
Dim dest As Range

Application.ScreenUpdating = False
With Sheets("Sheet2")
LRow2 = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LRow2
Sheets("Sheet1").Range("A2") = .Cells(i, 1)
LRow1 = Sheets("Sheet1").Cells(Rows.Count, "F").End(xlUp).Row
LRow3 = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
Set dest = IIf(LRow3 = 1, Sheets("Sheet3").Range("A1"), _
Sheets("Sheet3").Cells(LRow3, 1)(2))
Sheets("Sheet1").Range("F2:F" & LRow1).Copy
dest.PasteSpecial xlPasteValues
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Sheets("Sheet1").Range("F2:F" & LRow1).Copy
dest.PasteSpecial xlPasteValues

Could also be...

dest.Value = Sheets("Sheet1").Range("F2:F" & LRow1).Value

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Mon, 11 May 2015 15:56:34 -0400 schrieb GS:

Sheets("Sheet1").Range("F2:F" & LRow1).Copy
dest.PasteSpecial xlPasteValues

Could also be...

dest.Value = Sheets("Sheet1").Range("F2:F" & LRow1).Value

dest is a single cell. You had to resize it:

dest.Resize(LRow1 - 1).Value = Sheets("Sheet1").Range("F2:F" & LRow1).Value


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Mon, 11 May 2015 15:56:34 -0400 schrieb GS:

Sheets("Sheet1").Range("F2:F" & LRow1).Copy
dest.PasteSpecial xlPasteValues

Could also be...

dest.Value = Sheets("Sheet1").Range("F2:F" & LRow1).Value

dest is a single cell. You had to resize it:

dest.Resize(LRow1 - 1).Value = Sheets("Sheet1").Range("F2:F" &
LRow1).Value


Regards
Claus B.

Ah.., didn't notice!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sunday, May 10, 2015 at 10:57:49 AM UTC-5, wrote:
Hi - I am getting a refresher on building macros. Below is a list of steps that I am trying to automate. I am wondering if someone would be able to show me what is an efficient macro to use to complete these steps.

I am happy to send the file over, if it makes it easier to follow.

Sheet1 is where the results will be generated based on a single Input Cell (A2), and it has formulas in it that generate the results in column F (F2 thru F40, though ideally the macro would allow flexibility to make the range shorter or longer, such as F2 thru F10, or F2 thru F100. But the only cell that changes in this sheet will be Cell A2.

Sheet2 is simply a list of input values in column A, in the range of A1:A2473. These values in sheet 2 will be pasted into sheet1!A2, but one at a time, generating different results in column F of Sheet1 (due to formulas in that sheet). The values will go into Sheet1!A2, one at a time.

Sheet3! is where the results will be pasted from Sheet1! A list of resulting values will be appended to in sheet3 (i.e., a long list in column A), with the results that are generated in Sheet1, column F.

So the exact list of steps is as follows:

1. Copy the value in Sheet2!A1
2. Paste Result of that cell into Sheet1!A2
3. Copy any cells that contain values in Sheet1!F2:F40 (though preferably allow flexible range of more or less values)
4. Paste (Special Values) those results from Sheet1!F2:F40 into Sheet3!A1:A39

//BEGIN TO REPEAT THE PROCESS, but moving down one cell in Sheet2 to the next value in the list

5. Copy the value in Sheet2!A2 (i.e., offset down 1 cell from step 1, etc...)
6. Paste Result of that cell into Sheet1!A2 (this cell stays the same, and continues to receive the results for each value that is in Sheet2, one by one)
7. Copy any cells that contain values in Sheet1!F2:F40 (though preferably allow flexible range of more or less values)
8. Paste (Special Values) those results from Sheet1!F2:F40 into Sheet3!A40:A79 (i.e., Jump to end of last paste that occurred and continue to append this list with each successive iteration.

Thanks for any assistance! Just let me know if those steps are unclear.

thanks to both of you!