0

I’m new in VBA and I’m trying to copy and paste filtering data into another sheet. When I launch my code, nothing happens, no error messages either.

Input Sheet "DONNEE DRAFT" Columns K and L :

BU (K) AL (L)
1 FRQ40 ALPYIP
2 FRXK0 ALPAJW
3 FRRZ0 ALPK29
4 FR500 ALPAGD
5 FR500 ALPH51
6 FR040 ALPH51
7 etc... etc...

Note: data in Column K -> VlookUp formula

I'm adding a filter in this table > filtering in BU (Columns K) : "FR500" I copy the column K and L then past into another sheet:

In Columns A :BU "FR500" and in C :AL linked to FR500). In the example bellow it's what we have in line 6 and 7 (It won't start at this specified row always).

Outpout Expect in Sheet "IML" :

BU (A) BU AFF (B) AL (C)
1 data other data data
2 data other data data
3 data other data data
4 data other data data
5 data other data data
6 FR500 other data ALPH51
7 FR500 other data ALPAGD

Here the code (I tried only to copy/paste "BU"):

    Dim FR500 As Range
    Dim LastRow As Double
    Dim LastRow2 As Double
    
    LastRow = Sheets("DONNEE DRAFT").Range("K" & Rows.Count).End(xlUp).Row + 1 'last row
    LastRow2 = Sheets("IML").Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Set FR500 = Sheets("DONNEE DRAFT").Range("K1").CurrentRegion
    Sheets("DONNEE DRAFT").AutoFilterMode = False 'turning off all filter
    FR500.AutoFilter Field:=1, Criteria1:="FR500" 'filtering data

    Sheets("DONNEE DRAFT").Range("K" & LastRow).Copy
    Sheets("IML").Range("A" & LastRow2).PasteSpecial xlPasteValues
mfau
  • 45
  • 4
  • Excel does not know in which sheet `Range("K" & LastRow).Copy` is. Specify a sheet for that range. – Pᴇʜ Mar 29 '22 at 09:46
  • HI @Pᴇʜ, thanks for your help. I specified the "DONNE DRAFT" sheet. `Sheet("DONNEE DRAFT").Range("K" & LastRow).Copy Destination:=Sheets("IML").Range("A" & LastRow2)`. But in my input, in column K it's a formula (Vlookup), how do I copy/paste only the values (I edit my question to specify this point) – mfau Mar 29 '22 at 10:02
  • Solution is: Do some research, that was asked be fore: [Copy Paste Values only( xlPasteValues )](https://stackoverflow.com/questions/23937262/copy-paste-values-only-xlpastevalues) – Pᴇʜ Mar 29 '22 at 10:03
  • ok, I did some research before and try, it didn't work, that's why I post here. But thank you anyway – mfau Mar 29 '22 at 10:06
  • I don't see in your code that you have tried what I posted as a link. – Pᴇʜ Mar 29 '22 at 10:08
  • I apply this code: `Sheets("DONNEE DRAFT").Range("K" & LastRow).Copy Sheets("IML").Range("A" & LastRow2).PasteSpecial xlPasteValues`. But it's the same, nothing is pasted – mfau Mar 29 '22 at 10:43
  • 1
    `Lastrow` is `Sheets("DONNEE DRAFT").Range("K" & Rows.Count).End(xlUp).Row + 1` so you are choosing 1 row **below** last one with data. That means an **empty** cell. Then you copy/paste that empty cell with `Sheets("DONNEE DRAFT").Range("K" & LastRow).Copy` Also, Notice you copy a single one, not a range of several cells. – Foxfire And Burns And Burns Mar 29 '22 at 11:21
  • Also declare `LastRow` and `LastRow2` as `Long`. The type `Double` is a decimal value but row numbers are integer numbers, ther exists no row `2.3` for example. This might cause some issues (or not, but fix it). Note that if you want to copy the filtered data only you need to use something like `Sheets("DONNEE DRAFT").Range("K1:K" & LastRow).SpecialCells(xlCellTypeVisible).Copy` – Pᴇʜ Mar 29 '22 at 11:23

0 Answers0