Correct Code to Move Information Around

[theteerex]

Please be patient with me as part of this code is gibberish, but meant to illustrate what I'm trying to do:
First code finds last row:
Sub FindLastRow()


Dim LastRow As Long
LastRow = Sheet1.Range("C2").End(xlDown).Row
MsgBox Prompt:="Last Row is" & LastRow

End Sub

Second code copies a cell from another sheet to new pasted template on active sheet. Template is 32 rows tall and Y is the value of the rows before anything is pasted:
Sub Copy_Cell()


Dim Y As Range
Set Y = LastRow
When Sheet1.Range("C" & Rows.Count).Value > Y+32.Value Then
Y+1.Value = LastCell

End If

End Sub

3rd code is the LastCell referenced above:
Function LastCell() As Range
With Sheet5
Set LastCell = .Cells(Rows.Count, 1).End(xlUp)
End With
End Function

 

[mumps]

I think it might be easier to follow if you explained what you want to do in words. Be as detailed as possible using a few examples from your data and referring to specific cells, rows, columns and worksheets.

 

[theteerex]

I have a function that copies and pastes a template on Sheet1 once a new row is added on Sheet5.
Sheet5 has information in rows with the first column containing the project names.
The template is a table that is 32 rows in height.
I want the functions above to copy the cell with the project name in Sheet5 and paste that value in Sheet1 in the newly pasted template.
That value has to be pasted in first cell of the first column of the template for the template to do is job of copying the information from Sheet5 for the specific project.

Sheet 5
[TABLE="width: 1134"]
<colgroup><col span="14"></colgroup><tbody>[TR]
[TD]Project[/TD]
[TD]Type[/TD]
[TD]DC[/TD]
[TD]Fab[/TD]
[TD]Unit[/TD]
[TD]Size[/TD]
[TD]Units[/TD]
[TD]Date[/TD]
[TD]Fabric[/TD]
[TD]FabCon[/TD]
[TD]Design[/TD]
[TD]Dcon[/TD]
[TD]Ship[/TD]
[TD]Scon[/TD]
[/TR]
[TR]
[TD]BAC[/TD]
[TD]Proposal[/TD]
[TD]LA[/TD]
[TD]SK[/TD]
[TD]Isom[/TD]
[TD="align: right"]5000[/TD]
[TD]KBPSD[/TD]
[TD="align: right"]3/1/2010[/TD]
[TD] $ 5,002,500[/TD]
[TD] $ 1,100,000[/TD]
[TD] $ 1,250,000[/TD]
[TD] $ - [/TD]
[TD] $ 150,000[/TD]
[TD] $ -


[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 1
[TABLE="width: 810"]
<colgroup><col span="10"></colgroup><tbody>[TR]
[TD]Project[/TD]
[TD]Type[/TD]
[TD]DC[/TD]
[TD]Fab[/TD]
[TD]Unit[/TD]
[TD]Size[/TD]
[TD]Units[/TD]
[TD]SoldDate[/TD]
[TD]Category[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]BAC[/TD]
[TD]Proposal[/TD]
[TD]LA[/TD]
[TD]SK[/TD]
[TD]Isom[/TD]
[TD="align: right"]5000[/TD]
[TD]KBPSD[/TD]
[TD="align: right"]3/1/2010[/TD]
[TD]Fabric[/TD]
[TD] $ 5,002,500[/TD]
[/TR]
[TR]
[TD]BAC[/TD]
[TD]Proposal[/TD]
[TD]LA[/TD]
[TD]SK[/TD]
[TD]Isom[/TD]
[TD="align: right"]5000[/TD]
[TD]KBPSD[/TD]
[TD="align: right"]3/1/2010[/TD]
[TD]FabCon[/TD]
[TD] $ 1,100,000[/TD]
[/TR]
[TR]
[TD]BAC[/TD]
[TD]Proposal[/TD]
[TD]LA[/TD]
[TD]SK[/TD]
[TD]Isom[/TD]
[TD="align: right"]5000[/TD]
[TD]KBPSD[/TD]
[TD="align: right"]3/1/2010[/TD]
[TD]Design[/TD]
[TD] $ 1,250,000[/TD]
[/TR]
[TR]
[TD]BAC[/TD]
[TD]Proposal[/TD]
[TD]LA[/TD]
[TD]SK[/TD]
[TD]Isom[/TD]
[TD="align: right"]5000[/TD]
[TD]KBPSD[/TD]
[TD="align: right"]3/1/2010[/TD]
[TD]Dcon[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]BAC[/TD]
[TD]Proposal[/TD]
[TD]LA[/TD]
[TD]SK[/TD]
[TD]Isom[/TD]
[TD="align: right"]5000[/TD]
[TD]KBPSD[/TD]
[TD="align: right"]3/1/2010[/TD]
[TD]Ship[/TD]
[TD] $ 150,000[/TD]
[/TR]
[TR]
[TD]BAC[/TD]
[TD]Proposal[/TD]
[TD]LA[/TD]
[TD]SK[/TD]
[TD]Isom[/TD]
[TD="align: right"]5000[/TD]
[TD]KBPSD[/TD]
[TD="align: right"]3/1/2010[/TD]
[TD]Scon[/TD]
[TD] $ - [/TD]
[/TR]
</tbody>[/TABLE]


I want the BAC in Blue from Sheet 5 to be copied and pasted to Sheet 1 in the cell with Red lettering.

 

[mumps]

Is the BAC in Blue from Sheet 5 in column A of the newly added row? Is this newly added row the bottom row in Sheet5?

 

[theteerex]

Yes, BAC is the newly added row in Sheet 5. This event triggers the table in Sheet 1 to be created.
If I can then copy the text from Sheet 5 to Sheet 1, the table will auto-populate and you will end up with what I have above in Sheet 1.

 

[theteerex]

Bumping this thread for help because my boss wants to use my workbook for his own project and hence, my time is running out!

 

[mumps]

Hopefully, I understood correctly. Assuming that the newly added row is the last row on "Sheet5", try this code:

Sub test()
    Dim LastRow As Long
    LastRow = Sheets("Sheet5").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet5").Cells(LastRow, 1).Copy Sheets("Sheet1").Cells(1, 1)
End Sub

Change the sheet names to suit your needs.