VBA Macro - Select range of SpecialCells

[JGR]

Hello,

I'm doing a VBA macro button for automatizing some filtering and copying of new data from an Excel book/query (wb1) to another book refering to a client (wb2) with the latter's format.

More specifically, I look for the last value in column 3 of wb2 and save it (I name it 'prt'), I filter the name of the client in column 5 of wb1.

Then, in the same wb1, I look for the last row containing the prt in column 12 and I name it 'k'.

Now, what I need to do is to copy the specialcells in the wb1 range "Cells('k+1', 10), Cells('last-filtered-row', 16)" and paste them below the last written row of wb2, with its upper rows cell format.

But 'k+1' is not the next row of the filtered table of wb1 (it's 11301), and I don't know how to call it, same for the last filtered row (which coincides with the next from k, in this case).

I managed, at least, to select the whole columns from 10 to 16 of the filtered table (last line of the code), but that's not what I need to do.

Also, is there a way I can only use numbers for refering to rows and columns? I can't manage to do it sistematically, and I end up using 'Range("L:L")', for example.

Here's the code I'm using:

Private Sub CommandButton1_Click()
'actualizar datos
    Const w1 As String = "Pack horas por fecha intervencion (pruebas).xlsx"
    Const w2 As String = "Abacus Pack horas 18112021 (pruebas).xlsm"
    Set wb1 = Workbooks(w1)
    Set ws1 = wb1.Sheets(1)
    Set wb2 = Workbooks(w2)
    Set ws2 = wb2.Sheets(2)
    
    Dim i, k As Long
    i = ws2.Cells(Rows.Count, 3).End(xlUp).Row 'the row where the prt will be
    
    ws2.Activate
    
    Dim prt As String
    ws2.Cells(i, 3).Select 'just to highlight it
    prt = ws2.Cells(i, 3).Value
    
    Dim C, where As Range, whatt As String
    ws1.Activate
    
    Dim cliente As Range
    Set cliente = ws1.Range("E1")
    cliente.AutoFilter Field:=5, Criteria1:="ABACUS*"
    
    whatt = prt
    Set C = ws1.Range("L:L")
    Set PVP = C.Find(what:=whatt, after:=C(1), searchdirection:=xlPrevious)
    k = PVP.Rows(PVP.Rows.Count).Row 'k = 10833
    
    ws1.Range("J:P").SpecialCells(xlCellTypeVisible).Select
    
End Sub

Here's some images of the Excels if it helps:

Thanks in advance!

 

[JGR]

I got it!

Private Sub CommandButton1_Click()
'actualizar datos
    Const w1 As String = "Pack horas por fecha intervencion (pruebas).xlsx"
    Const w2 As String = "Abacus Pack horas 18112021 (pruebas).xlsm"
    Set wb1 = Workbooks(w1)
    Set ws1 = wb1.Sheets(1)
    Set wb2 = Workbooks(w2)
    Set ws2 = wb2.Sheets(2)
    
    Dim i, j, k, LR, l As Long
    i = ws2.Cells(Rows.Count, 3).End(xlUp).Row 'the row where the prt will be
    
    ws2.Activate
    
    Dim prt As String
    ws2.Cells(i, 3).Select 'just to highlight it
    prt = ws2.Cells(i, 3).Value
    
    Dim C, where As Range
    ws1.Activate
    
    Dim cliente As Range
    Set cliente = ws1.Range("E1")
    cliente.AutoFilter Field:=5, Criteria1:="ABACUS*"
    
    Set C = ws1.Range("L:L")
    Set PVP = C.Find(what:=prt, after:=C(1), searchdirection:=xlPrevious)
    k = PVP.Rows(PVP.Rows.Count).Row 'k = 10833
    
    LR = ws1.Range("A" & Rows.Count).End(xlUp).Row
    
    Dim dataRange As Range
    Set dataRange = ws1.Range("A1", Cells(LR, 16).Address)

    With dataRange.SpecialCells(xlCellTypeVisible)
        j = .Areas(.Areas.Count).Row + .Areas(.Areas.Count).Rows.Count - 1
        ws1.Range(.Cells(k + 1, 10), .Cells(j, 16)).SpecialCells(xlCellTypeVisible).Select
        Selection.Copy
    End With
    
    ws2.Activate
    
    l = ws2.Cells(Rows.Count, 5).End(xlUp).Row
    ws2.Range(Cells(l + 1, 1), Cells(l + 1, 8)).PasteSpecial Paste:=xlPasteFormulas
    
End Sub