Trying to paste to the next available line that is empty

[Howey]

Hi all,

New to Mr Excel and hoping you can help with the below, i have recorded a macro to copy values from from one tab and paste them into a row "A" on the next tab and i found a string which is supposed to paste all the data to the next available row when the macro runs but it is pasting to the first row each time,
can some one help,

Sheets("Matrix").Select
ActiveSheet.Unprotect
Sheets("Raise a CI").Select
Range("B6").Select
Selection.Copy
Sheets("Matrix").Range("A" & Rows.Count).End(xlUp).Offset(1).
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Raise a CI").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Matrix").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[SanjayGMusafir]

Try this -

Sheets("Matrix").Select
ActiveSheet.Unprotect
Sheets("Raise a CI").Select
Range("B6").Select
Selection.Copy
Sheets("Matrix").Range("A" & Rows.Count).End(xlUp).Offset(1).
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Raise a CI").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Matrix").Select
Range("B2").Select

'To go to last row of the same Active column
Dim LRow As Long, LCol As Long
LCol = ActiveCell.Column
LRow = Cells(Rows.Count, LCol).End(xlUp).Row
Cells(LRow, LCol).Select

'To paste at selected cell
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[mumps]

How about:

Sub CopyData()
    With Sheets("Raise a CI")
        .Range("B6").Copy
        With Sheets("Matrix")
            .Unprotect
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End With
        .Range("B3").Copy
        With Sheets("Matrix")
            .Cells(.Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End With
    End With
    Application.CutCopyMode = False
End Sub

 

[Athopp]

Posting here hoping that someone can point me in the right direction.... I have VBA Code that Copy and Pastes but it seems that my final results in column D has all of column B and tones of blank rows and then entries from column C. I know there is something in my code that capturing all these blank row, I just don't know what/how to fix it. Any suggestions?

Columns("B:C").EntireColumn.Hidden = False
ActiveSheet.Range("$A$1:$E$50").AutoFilter Field:=2, Criteria1:="<>"
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=1
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
ActiveWindow.SmallScroll ToRight:=-2
Call Application.Calculate
ActiveSheet.Range("$A$1:$E$50").AutoFilter Field:=2
LastRow = ActiveWorkbook.Worksheets("Rename_Folders").Cells(Rows.Count, 4).End(xlUp).Row
NextRowExport = LastRow + 1

ActiveWorkbook.Worksheets("Rename_Folders").Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

ActiveWorkbook.Worksheets("Rename_Folders").Range("D" & NextRowExport).PasteSpecial xlPasteValues
Call Application.Calculate
Columns("B:C").EntireColumn.Hidden = True
Range("D2").Activate

 

[mumps]

Please start your own new thread. You will increase your chances of getting a solution.