Add same amount of rows as in another column

0
votes

I've created a button to copy and paste table from one worksheet to another, to the first blank cell in a column. I'd like to add date (cell A19) and name (cell A5) to the other column, with same amount of lines which have been add.

Scheme of the table has been added as a pic, down below is my piece of code. Tried with the offset but didnt work for me

Dim TblToSave As Range
Dim RangeToPaste As Range

Set TblToSave = ThisWorkbook.Worksheets("Sheet1").Range("D22", Range("O22").End(xlDown))

TblToSave.Copy

ThisWorkbook.Worksheets("db").Activate

Set RangeToPaste = ThisWorkbook.Worksheets("Db").Range("C" & Rows.Count).End(xlUp).Offset(1)

RangeToPaste.PasteSpecial xlPasteValues

enter image description here

I managed to figure it out, this one was known by me but I dont know if it is the best way, the most optimal, not burden for workbook

Dim Lastrow As Integer
Lastrow = ThisWorkbook.Worksheets("db").Cells(Rows.Count, 3).End(xlUp).Row

For Each cell In ThisWorkbook.Worksheets("db").Range("C2:C" & Lastrow)
    If Not IsEmpty(cell) Then
        cell.Offset(, -1).Value = ThisWorkbook.Worksheets("Sheet1").Range("A5").Value
        cell.Offset(, -2).Value = ThisWorkbook.Worksheets("Sheet1").Range("A20").Value
    End If
Next
vba
ThisWorkbook.Worksheets("Db").Range("A" & Rows.Count).End(xlUp).Offset(1).resize(TblToSave.rows.count).value=ThisWorkbook.Worksheets("Sheet1").range("A19").value ? – SJR
Remember to fully qualify all your ranges. – SJR
Close but not quite. It gives me same amount of lines but at the End(xlUp) so the last available line, where I need these amount of lines from the first pasted cell (so for first use lets say from C2 to C11), but for next use I need these to be inserted from C12 to C21 and so on – lifeofthenoobie
Is this the same as stackoverflow.com/questions/70259455/… ??? – Nathan_Sav
@SJR ive edited my query with the kind of solution, please tell me what do you think about it – lifeofthenoobie