Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |