Store a Row Location Before Pasting and then Paste to that Location.

theteerex

Board Regular
Joined
Mar 2, 2018
Messages
102
Hi All,

I am trying to copy a cell from 1 sheet and paste it in another sheet After pasting a template.
The problem is, I am using the paste after last row command but this mean that if I ever change the template dimensions, I would have to change the code.
So I am trying to solve this problem by defining what the last row is prior to pasting and then pasting to the next row, as opposed to finding the new last row, and then counting backwards, which was my original solution.

My code:

Code:
[COLOR=#0000ff]Sub[/COLOR] FindProj()
[COLOR=#0000ff]Dim[/COLOR] Lastrow As [COLOR=#0000ff]Long[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] Newproj As [COLOR=#0000ff]Long[/COLOR]
Lastrow = Sheets("Historical").Cells(Rows.Count, "B").End(xlUp).Row
Newproj = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Offset(1)
AddProj [COLOR=#008000]'This copies the template and pastes it to the first empty row[/COLOR]
Sheets("Historical").Cells(Lastrow, "B").Copy Sheets("Data").Range(Newproj, "A")
[COLOR=#0000ff]End Sub[/COLOR]

Any help is appreciated.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
It is important to understand that "Rows.Count" returns the number of available rows on sheet, not the number of populated rows.
That is why you typically see range reference using it followed by ".End(xlUp)" to get to the last populated cell in a column (like in your code in post #9 ).
 
Upvote 0
My code doesn't work in the way you're describing though.
It really does count just the rows in the column A that exist in the table. It does not count all the way down to the end.
Is that what you're implying?
Either way, that part of my code is working very well.
 
Upvote 0
My code doesn't work in the way you're describing though.
It really does count just the rows in the column A that exist in the table. It does not count all the way down to the end.
Is that what you're implying?
Either way, that part of my code is working very well.
Is there a spot where I can put a message box to see what the code sees? This way I can see what you're talking about with my code.
 
Upvote 0
Try this:
Code:
Sub Worksheet_Calculate()
'Detects when new row is added

Dim X As Range
Set X = LastCell
[COLOR=#ff0000]    MsgBox Rows.Count[/COLOR]
    If Sheet10. Range("A" & Rows.Count).Value < X.Value Then
        X.Value = Sheet10.Range("A" & Rows.Count).Value
        AddProj
    End If

End Sub
 
Upvote 0
Yeah, that tells me how many rows there are in the column.

I guess what I'm after is this in code form:
Code:
Sub FindProj() 'Finds project name in Historical Worksheet and pastes it in Data Worksheet
Dim Lastrow As Long
Dim Newproj As Long
Lastrow = Sheets("Historical").Cells(Rows.Count, "B").End(xlUp).Row
Newproj = Sheets("Data").Cells(Rows.Count, "C").End(xlUp).Row
Sheets("Historical").Cells(Lastrow, "B").Copy [B]Sheets("Data").Cells(Newproj - 30, "C") [/B]<--I want this here in bold to change. See below


End Sub

Sheets("Data").Cells(Newproj - Master + 1, "C")
As in, I want the information to be pasted to the last row of the table before the table became longer due to the "Master" template being pasted.
So, if the last row was 100, the table has 32 rows. This means the new last row is 132.
Currently, my code only pastes in the correct row because I calculated that I have to paste the value 30 rows prior in 101 with math.
If ever I change my template dimensions, I would have to go into the code and change the "30" to "31" for example or "35", depending on the new number of rows.

I don't want it hardwired. I want it to change depending on the variables, just like the rest of my code.
 
Upvote 0
What is the sheet name for sheet codename Sheet1?
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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