I want to copy the columns "Asset", "Quantity", "Market Value" and "Portfolio Weight %" and only up until the ClientStartRow & ClientEndRow

I am aware that the code I am using takes the entire row of the columns above, but when I change the entireColumn to Range(ClientStartRow & ":" & ClientEndRow) it has an error, so I am stumped on what to do next.

• The other step I would like to do is then filter the results of ClientStartRow & ClientEndRow to delete values with a string of 5 or greater. I have 0 clue how to work this out. So If you could assist with that that would be greatly appreciated.

Sub Client_CRM()

Dim ClientStartRow As Long, ClientEndRow As Long
Dim Listed As Long


Set PortfolioRange = Worksheets("Client Paste").Range("A:M")
Set PPSExport = Range("A2:M2")


With Sheets("Client Paste")

Sheets("Output Sheet").Cells.Clear
Worksheets("Client Paste").Activate

ClientStartRow = Worksheets("Client Paste").Range("A3").Row
ClientEndRow = Worksheets("Client Paste").Range("A:A").Find(What:="Totals", after:=.Range("A3"), LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False).Row

' Range(ClientStartRow & ":" & ClientEndRow).Select

    For Each cell In PPSExport
    
        If cell.Value = "Asset" Then
            cell.EntireColumn.Copy
            ActiveSheet.Paste Destination:=Worksheets("Output Sheet").Range("A:A")
        
        End If
        
        If cell.Value = "Quantity" Then
            cell.EntireColumn.Copy
            ActiveSheet.Paste Destination:=Worksheets("Output Sheet").Range("B:B")
        End If
        
        If cell.Value = "Market value" Then
            cell.EntireColumn.Copy
            ActiveSheet.Paste Destination:=Worksheets("Output Sheet").Range("C:C")
        End If
        
        If cell.Value = "Portfolio weight %" Then
            cell.EntireColumn.Copy
            ActiveSheet.Paste Destination:=Worksheets("Output Sheet").Range("D:D")
        End If


Next cell
 
Sheets("Output Sheet").Select

   End With
         
End Sub

The error at hand is that I only want the values from ClientStartRow and ClientEndRow to copy onto "Output Sheet" and not the entire column. I can't seem to work out a way to fix this issue.

CodePudding user response：

The reason

cell.Range(ClientStartRow & ":" & ClientEndRow).Copy

fails is because that line tries to specify the enitre row relative to cell. When cell is not in column A, that extends beyond the right hand edge of the sheet, hence the error

To fix, Change

cell.EntireColumn.Copy

to

cell.EntireColumn.Range("A" & ClientStartRow & ":A" & ClientEndRow).Copy

Note:

1. Because this is relative the cell, the A specifies the first column in range cell, so don't update this for other columns

2. Destination:=Worksheets("Output Sheet").Range("*:*") will always paste starting at row 1 of the destination range. Is this what you want?