Paste Down Until Last Row

[iSoleil]

Hi,

I'm a newbie to excel and was wondering if someone could help me with this.

I have a sheet "Template" where I want to copy range C2:C6 and paste transposed to another sheet "Archive".
On "Archive", I have a table with already prefilled data on columns A:G from this code:

Sub attendees()

    Range("B13").Select 'from sheet "Template"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    With Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=False
    End With
    
End Sub

What I want to do is populate the rows from H:L with same data from C2:C6 paste transposed values and number formats only.
I managed to work out the paste transposed part but cannot seem to figure out the paste down until last row to match up on the A:G data for the life of me.

Sub topics()

    Range("C2:C6").Select  'from sheet "Template"
    Selection.Copy

    With Sheets("Archive").Range("H" & Rows.Count).End(xlUp).Offset(1)
        .PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
    End With
    
End Sub

This is ideally what I want to do, where Date Discussed - Facilitator will just be the same entry for all items copied under the same code/batch:

User Email	Join Time	Leave Time	Duration (Minutes)	Guest	Name	L1	Date Discussed	Batch	Audience	Topic	Facilitator/s
abc@sgadfa.com	10/20/2022 14:51​	10/20/2022 15:34​	43​	No	asfdjgk	;kadglsv	10/20/2022	3	asfdfa	asfdfa	asfdfa
fjk@ladjgr.com	10/20/2022 14:59​	10/20/2022 15:34​	35​	No	jdgsl	khglsdgl	10/20/2022	3	asfdfa	asfdfa	asfdfa
htoak@akhdg.com	10/20/2022 15:00​	10/20/2022 15:34​	34​	No	adgijg;	ljgst	10/20/2022	3	asfdfa	asfdfa	asfdfa
adgjldngs@isutslg.com	10/20/2022 14:51​	10/20/2022 15:34​	43​	No	fdjh	khdgsfv	10/20/2022	3	asfdfa	asfdfa	asfdfa

Any inputs will be highly appreciated. Thank you!

 

[John_w]

Try this:

Public Sub Paste_Rows()

    Worksheets("Template").Range("C2:C6").Copy

    With Worksheets("Archive")
        .Range("H2:L" & .Range("A" & .Rows.Count).End(xlUp).Row).PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
    End With
    Application.CutCopyMode = False
    
End Sub

 

[Alex Blakenburg]

Leveraging off John's code, I believe you are after both the start and end of the range.
(You can do it without variables but it will get very long)

Public Sub topics()

    Dim startRow As Long, endRow As Long

    Worksheets("Template").Range("C2:C6").Copy

    With Worksheets("Archive")
        startRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1
        endRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("H" & startRow & ":L" & endRow).PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
        'OR
        '.Range(.Cells(startRow, "H"), .Cells(endRow, "L")).PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
    End With
    Application.CutCopyMode = False
    
End Sub

 

[iSoleil]

Leveraging off John's code, I believe you are after both the start and end of the range.
(You can do it without variables but it will get very long)

Public Sub topics()

    Dim startRow As Long, endRow As Long

    Worksheets("Template").Range("C2:C6").Copy

    With Worksheets("Archive")
        startRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1
        endRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("H" & startRow & ":L" & endRow).PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
        'OR
        '.Range(.Cells(startRow, "H"), .Cells(endRow, "L")).PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
    End With
    Application.CutCopyMode = False
   
End Sub

Thanks, Alex! This was what I needed. May your day be as bright as mine! :D

 

[iSoleil]

Try this:

Public Sub Paste_Rows()

    Worksheets("Template").Range("C2:C6").Copy

    With Worksheets("Archive")
        .Range("H2:L" & .Range("A" & .Rows.Count).End(xlUp).Row).PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
    End With
    Application.CutCopyMode = False
   
End Sub

Thanks for the immediate reply, John!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.