Please look at the code

[bforster1]

I am trying to take 500 rows of data and put each row in a model and move to the next row. Unfortunately the rows of data have formulas and I am trying to do Special Paste code to convert to a value. Here is the code

Sub Model()
iTarget = 6
For i = 6 To Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Working Data").Cells(i, "A").Resize(, 30).Select
Selection.Copy
Worksheets("Model").Cells(6, "B").Select
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("Output").Cells(i + 6, "A") = Worksheets("Model").Cells(17, "B")
Worksheets("Output").Cells(i + 6, "B") = Worksheets("Model").Cells(18, "B")
Worksheets("Output").Cells(i + 6, "C") = Worksheets("Model").Cells(8, "B")
iTarget = iTarget + 1
Next i
End Sub

[crazybass2]

b,

Not sure what you are trying to accomplish with the "Output" = "Model"
routines, but I guess it's not for me to question. The trouble you've been
having is that you are copying data from a selection within one sheet and
trying to paste to a selection in a different sheet without activating that
sheet first. You can add Worksheets("your worksheet name").activate before
each select line, or you can modify the code as below. You will also need to
change the Cells(6,"B") to Cells(i,"B") if you want the data to fill down the
cells rather than overwriting the previous data.

Sub Model()
iTarget = 6
For i = 6 To Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Working Data").Cells(i, "A").Resize(, 30).Select
Selection.Copy
Worksheets("Model").Cells(i, "B").PasteSpecial Paste:=xlPasteValues
Worksheets("Output").Cells(i + 6, "A") = Worksheets("Model").Cells(17, "B")
Worksheets("Output").Cells(i + 6, "B") = Worksheets("Model").Cells(18, "B")
Worksheets("Output").Cells(i + 6, "C") = Worksheets("Model").Cells(8, "B")
iTarget = iTarget + 1
Next i
End Sub


"bforster1" wrote:

>
> I am trying to take 500 rows of data and put each row in a model and
> move to the next row. Unfortunately the rows of data have formulas and
> I am trying to do Special Paste code to convert to a value. Here is the
> code
>
> Sub Model()
> iTarget = 6
> For i = 6 To Cells(Rows.Count, "A").End(xlUp).Row
> Worksheets("Working Data").Cells(i, "A").Resize(, 30).Select
> Selection.Copy
> Worksheets("Model").Cells(6, "B").Select
> Selection.PasteSpecial Paste:=xlPasteValues
> Worksheets("Output").Cells(i + 6, "A") =
> Worksheets("Model").Cells(17, "B")
> Worksheets("Output").Cells(i + 6, "B") =
> Worksheets("Model").Cells(18, "B")
> Worksheets("Output").Cells(i + 6, "C") =
> Worksheets("Model").Cells(8, "B")
> iTarget = iTarget + 1
> Next i
> End Sub
>
>
> --
> bforster1
> ------------------------------------------------------------------------
> bforster1's Profile: http://www.excelforum.com/member.php...o&userid=11771
> View this thread: http://www.excelforum.com/showthread...hreadid=563367
>
>

[bforster1]

The "activate" guidance worked now the problem I am having is that the macro stops half way through my 500 rows of data.

As for the output = model code...I am taking the results of the financial model for each row of data and putting it in seperate rows on the output sheet.

Here is what I have ... any suggestions why the macro stops half way through my 500 rows of data?

Sub Model()
iTarget = 6
For i = 6 To Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Working Data").Activate
Cells(i, "A").Resize(, 30).Select
Selection.Copy
Worksheets("Model").Activate
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("Output").Cells(i, "A") = Worksheets("Model").Range("B17")
Worksheets("Output").Cells(i, "B") = Worksheets("Model").Range("B18")
Worksheets("Output").Cells(i, "C") = Worksheets("Model").Range("B8")
iTarget = iTarget + 1
Next i
End Sub

[crazybass2]

B,

I suspect that you may be running this macro from a standard module or a
sheet module other than "Working Data." What this will do is set the value
Cells(Rows.Count, "A").End(xlUp).Row equal to the number of used rows of the
currently activated sheet.

Use the following in place of you code:

Sub Model()
iTarget = 6
Worksheets("Working Data").Activate
For i = 6 To Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Working Data").Cells(i, "A").Resize(, 30).Copy
Worksheets("Model").Cells(i, "B").PasteSpecial Paste:=xlPasteValues
Worksheets("Output").Cells(i + 6, "A") = Worksheets("Model").Cells(17, "B")
Worksheets("Output").Cells(i + 6, "B") = Worksheets("Model").Cells(18, "B")
Worksheets("Output").Cells(i + 6, "C") = Worksheets("Model").Cells(8, "B")
iTarget = iTarget + 1
Next i
End Sub

Mike



"bforster1" wrote:

>
> The "activate" guidance worked now the problem I am having is that the
> macro stops half way through my 500 rows of data.
>
> As for the output = model code...I am taking the results of the
> financial model for each row of data and putting it in seperate rows on
> the output sheet.
>
> Here is what I have ... any suggestions why the macro stops half way
> through my 500 rows of data?
>
> Sub Model()
> iTarget = 6
> For i = 6 To Cells(Rows.Count, "A").End(xlUp).Row
> Worksheets("Working Data").Activate
> Cells(i, "A").Resize(, 30).Select
> Selection.Copy
> Worksheets("Model").Activate
> Range("B6").Select
> Selection.PasteSpecial Paste:=xlPasteValues
> Worksheets("Output").Cells(i, "A") =
> Worksheets("Model").Range("B17")
> Worksheets("Output").Cells(i, "B") =
> Worksheets("Model").Range("B18")
> Worksheets("Output").Cells(i, "C") =
> Worksheets("Model").Range("B8")
> iTarget = iTarget + 1
> Next i
> End Sub
>
>
> --
> bforster1
> ------------------------------------------------------------------------
> bforster1's Profile: http://www.excelforum.com/member.php...o&userid=11771
> View this thread: http://www.excelforum.com/showthread...hreadid=563367
>
>