Transpose data that will be added to an archive sheet-CodePudding

So I've used VBA to take store and use data when an event (button press) occurs. I need to copy in the raw data vertically but have it copy to the archive horizontally so I can better filter it.

Private Sub CommandButton1_Click()

Dim Data As Range

Set Data = Range("C2:C21")

ThisWorkbook.Worksheets("Input").Range("C2:C21").Copy

ThisWorkbook.Worksheets("Calculator").Range("C2:C21").PasteSpecial Paste:=xlPasteValues

Dim dataInput As Worksheet
Dim dataArchive As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Set dataInput = ThisWorkbook.Worksheets("Input")
Set dataArchive = ThisWorkbook.Worksheets("Archive")
    
lCopyLastRow = dataInput.Cells(dataInput.Rows.Count, "A").End(xlUp).Row
    
lDestLastRow = dataArchive.Cells(dataArchive.Rows.Count, "A").End(xlUp).Offset(1).Row

dataInput.Range("C2:C21" & lCopyLastRow).Copy _
dataArchive.Range("A" & lDestLastRow)

End Sub

CodePudding user response：

Private Sub CommandButton1_Click()

Dim dataInput As Worksheet
Dim dataArchive As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Set dataInput = ThisWorkbook.Worksheets("Input")
Set dataArchive = ThisWorkbook.Worksheets("Archive")
    
lDestLastRow = dataArchive.Cells(dataArchive.Rows.Count, "A").End(xlUp).Offset(1).Row
dataInput.Range("C2:C21").Copy
dataArchive.Range("A" & lDestLastRow).PasteSpecial Transpose:=True

End Sub