Copy data from one sheet to another, knowing last line

Gwhaou

Board Regular
Joined
May 10, 2022
Messages
78
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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)

VBA Code:
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 🙏
 

Attachments

  • actual code.PNG
    actual code.PNG
    39.4 KB · Views: 10
  • data_sheet.PNG
    data_sheet.PNG
    43.6 KB · Views: 9
  • way to extract.PNG
    way to extract.PNG
    40.4 KB · Views: 10

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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 :

VBA Code:
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)
 

Attachments

  • Problem.PNG
    Problem.PNG
    5.7 KB · Views: 8
  • pr.PNG
    pr.PNG
    6 KB · Views: 8
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top