Please look at the code
 

I am trying to take 500 rows of data and put each row in a model an
move to the next row. Unfortunately the rows of data have formulas an
I am trying to do Special Paste code to convert to a value. Here is th
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 Su

--
bforster
-----------------------------------------------------------------------
bforster1's Profile: http://www.excelforum.com/member.php...fo&userid=1177
View this thread: http://www.excelforum.com/showthread.php?threadid=56336

Please look at the code
 

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

Please look at the code
 

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

Please look at the code
 

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