Correct Code to Move Information Around

theteerex

Board Regular
Joined
Mar 2, 2018
Messages
102
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Bumping this thread for help because my boss wants to use my workbook for his own project and hence, my time is running out!
 
Upvote 0
Hopefully, I understood correctly. Assuming that the newly added row is the last row on "Sheet5", try this code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top