VBA paste on next empty row

Question

I am trying to create a sheet that I can fill in, when i click the button it copies the information from the box and paste (and Transpose) to the Rawdata tab, then deletes the information from the Dashboard and saves the file.

I have recorded a simple macro to do this for me, but the problem is that I dont know how to get it to add the data to the next free row, it just replaces the information already there, this is the code i am using to try make it happen

Sub Macro5()
'
' Macro5 Macro
'

'
Range("C3:C8").Select
Selection.Copy
Sheets("RawData").Select
Cells(Range("C1000000").End(xlUp).Row + 1, 3).PasteSpecial Paste:=xlValues, 
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Dasboard").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("C3:C8").Select
Selection.ClearContents
End Sub

any help here would be much appreciated.

I need it to transpose paste in the A:F columns

thank you

@Luuklag - yeah, I actually left like that in my response below for that very reason. Maybe the op's cat walked on the keyboard during question composition. — , Oct 05 '18 at 10:04

Pᴇʜ · Answer 1 · 2018-10-05T09:39:04.023

I recommend to read How to avoid using Select in Excel VBA. Using .Select is a very bad practice and results in many errors soon or later:

A better technique is defining worksheets and ranges so you can access them directly with a variable:

Option Explicit

Public Sub CopyData()
    'define source range
    Dim SourceRange As Range
    Set SourceRange = ThisWorkbook.Worksheets("Dashboard").Range("C3:C8")

    'find next free cell in destination sheet
    Dim NextFreeCell As Range
    Set NextFreeCell = ThisWorkbook.Worksheets("RawData").Cells(Rows.Count, "A").End(xlUp).Offset(RowOffset:=1)

    'copy & paste
    SourceRange.Copy
    NextFreeCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    ThisWorkbook.Save
    SourceRange.ClearContents
End Sub

score 1 · Answer 2 · answered Oct 05 '18 at 09:32

1

Try a direct value transfer without Select.

Sub Macro5()

    dim ws as worksheet

    set ws = workSheets("RawData")

    with worksheets("Dasboard").Range("C3:C8")

        ws.cells(rows.count, "A").end(xlup).offset(1, 0).resize(.columns.count, .rows.count) = _
          application.transpose(.value)
        .clearcontents
        .parent.parent.save

    end with

End Sub

answered Oct 05 '18 at 09:32

What does `worksheet.parent.parent` end up referring to, the application? – Marcucciboy2 Oct 05 '18 at 11:23
It's not `worksheet.parent.parent`, it's `worksheet.Range("C3:C8").parent.parent.` The first parent refers to the cell range's worksheet, the second to the workbook. – Oct 05 '18 at 11:25

VBA paste on next empty row

2 Answers2

Linked