Copy data from one sheet to another, knowing last line

[Gwhaou]

Hello,

I'm trying to extract data from one "base" sheet called (Data_sheet) to another sheet called (transfert_sheet), to take only needed information.
Also that tranfert_sheet will be saved in csv to be imported on a website (which need fixed format data)

I include 3 photos to explain the actual problem.

I coded this code to extract those data from "Data_sheet" to the sheet "Transfert_sheet" but it's not working as i want because you can see that on the photo (actual code)how it's copying data :
Car data en Bike data are on the same line (which not the format accepted by the website)

Public Sub transfert_data()

Dim lg As Long

'Filter Car
Sheets("Data").Range("A1").AutoFilter Field:=3, Criteria1:="=*Car*", _
             Operator:=xlAnd
             
'Fixed the total number of lines filtered
lg = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
             
'Select the columns filtered
'Took it from line 2 (we don't want the headers from the FRIST LINE !
'copy that on the tranfert sheet
    Sheets("Data").Range("A2:A" & lg).Copy Destination:=Sheets("transfert_sheet").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Data").Range("B2:B" & lg).Copy Destination:=Sheets("transfert_sheet").Range("B" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Data").Range("D2:D" & lg).Copy Destination:=Sheets("transfert_sheet").Range("C" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Data").Range("F2:F" & lg).Copy Destination:=Sheets("transfert_sheet").Range("D" & Rows.Count).End(xlUp).Offset(1)
'Initialize the autofilter
Sheets("Data").AutoFilterMode = False

'Launch another filter to took only Bikes
Sheets("Data").Range("A1").AutoFilter Field:=3, Criteria1:="=*Bike*", _
             Operator:=xlAnd

'Fixed another time the total number of row to export
lg = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
             
'Select the columns filtered
'Took it from line 2 (we don't want the headers from the FRIST LINE !
'copy that on the tranfert sheet
    Sheets("Data").Range("A2:A" & lg).Copy Destination:=Sheets("transfert_sheet").Range("E" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Data").Range("B2:B" & lg).Copy Destination:=Sheets("transfert_sheet").Range("F" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Data").Range("D2:D" & lg).Copy Destination:=Sheets("transfert_sheet").Range("G" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Data").Range("F2:F" & lg).Copy Destination:=Sheets("transfert_sheet").Range("H" & Rows.Count).End(xlUp).Offset(1)
'Initialize the autofilter
Sheets("Data").AutoFilterMode = False


End Sub

I want some help to transfert data like on the image (way to extract). On the actual code we don't diffenciate which row contains data, it actually took the last row from the columns.
If some one helps me on that, i would appreciate

 

[Gwhaou]

Hello,

I'm back again ,yesterday night I tried to figure out a solution;

I tried to find the last row empty row from the tranfert_sheet before car data transfer and after that I refind the last row empty for the bike data tranfer
So I create a variable called "lst_row" which is gonna work on the sheet called (transfert_sheet) and I placed that after the cell in which is supposed to be copied : Range("A"& lst_row)

But it's not working :

Dim lg As Long
Dim lst_row As Long

Sheets("Data").Range("A1").AutoFilter Field:=3, Criteria1:="=*Car*", _
             Operator:=xlAnd

lg = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
'Find out the last row from the transfert_sheet
lst_row = Sheets("transfert_sheet").Range("D1").End(xlUp).Offset(1)

    'copy Car data from data sheet to transfert_sheet 

    Sheets("Data").Range("A2:A" & lg).Copy Destination:=Sheets("transfert_sheet").Range("A" & lst_row)
    Sheets("Data").Range("B2:B" & lg).Copy Destination:=Sheets("transfert_sheet").Range("B" & lst_row)
    Sheets("Data").Range("D2:D" & lg).Copy Destination:=Sheets("transfert_sheet").Range("C" & lst_row)
    Sheets("Data").Range("F2:F" & lg).Copy Destination:=Sheets("transfert_sheet").Range("D" & lst_row)


Sheets("Data").Range("A1").AutoFilter Field:=3, Criteria1:="=*Bike*", _
             Operator:=xlAnd

'REfind out the last row from the transfert_sheet
lst_row = Sheets("transfert_sheet").Range("D1").End(xlUp).Offset(1)

    'Copy Bike data but row after car data 
    Sheets("Data").Range("A2:A" & lg).Copy Destination:=Sheets("transfert_sheet").Range("E" & lst_row).End(xlUp).Offset(1)
    Sheets("Data").Range("B2:B" & lg).Copy Destination:=Sheets("transfert_sheet").Range("F" & lst_row).End(xlUp).Offset(1)
    Sheets("Data").Range("D2:D" & lg).Copy Destination:=Sheets("transfert_sheet").Range("G" & lst_row).End(xlUp).Offset(1)
    Sheets("Data").Range("F2:F" & lg).Copy Destination:=Sheets("transfert_sheet").Range("H" & lst_row).End(xlUp).Offset(1)