Find, copy and paste as link all filled rows but only a limited number of columns

Question

I am trying to search for rows in my 'All Data' sheet that meet a certain criterion in column one and then copy those rows into a sheet called 'TAP'. This all works fine but I want to retain the link between the sheets so that when the data in 'All Data' gets updated, the data in 'TAP' also change. To achieve this, I have chosen to paste it as a link.

Again, this works fine, but because of that (I think) all the other columns which are not populated in 'All Data' get filled in with zeros in the 'TAP' sheet when getting copied. I would therefore want to limit the copying to all the populated rows but only first 14 columns, in order to avoid the rest of them being zeros.

My code is as below:

Private Sub CommandButton1_Click()
a = Worksheets("All Data").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

    If Worksheets("All Data").Cells(i, 1).Value = "TAP" Then
        Worksheets("All Data").Rows(i).Copy
        Worksheets("TAP").Activate
        b = Worksheets("TAP").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("TAP").Cells(b + 1, 1).Select
        ActiveSheet.Paste Link:=True
        Worksheets("All Data").Activate
    End If

Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("All Data").Cells(1, 1).Select
End Sub

Apologies but it's my first attempt at VBA so any help would be much appreciated.

Answer 1

To copy the 1-14 columns instead of the full row, change:

Worksheets("All Data").Rows(i).Copy

to:

Worksheets("All Data").Range(Cells(i, 1), Cells(i, 14)).Copy