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

[theteerex]

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:

[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.

 

[theteerex]

I guess that is the case.

 

[Joe4]

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 ).

 

[theteerex]

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.

 

[theteerex]

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.

 

[Joe4]

Try this:

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

 

[theteerex]

Yeah, that tells me how many rows there are in the column.

I guess what I'm after is this in code form:

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.

 

[Fluff]

What is the sheet name for sheet codename Sheet1?