Extracting data from sheets added everyhour [Closed]

Report
Posts
4
Registration date
Monday May 2, 2016
Status
Member
Last seen
May 5, 2016
-
Posts
2761
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2021
-
is there a way to extract data from sheets i add? like if i have data from multiple hours but i put all the data in one book but in different hours is there a way to extract data?

2 replies

Posts
2761
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2021
462
Hi Haroltan,

Could you give on example of how your data looks now and which data you wish to extract? Hard to understand from multiple hours to different hours.

Best regards,
Trowa
Posts
4
Registration date
Monday May 2, 2016
Status
Member
Last seen
May 5, 2016

so i grab data from our website theres alot of useless data but i only need tracking id location and estimated arrival date i need to take those data from those columnsvand put it in another sheet so its easier see all the data and filter it does that makes sense?
the problem is i use a different sheet everytime i grab that once a day so i want a master file where i can delete the sheet i use yesterday and put a new sheet the next day and it will just auto populate everything on a second sheet
Posts
2761
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2021
462
Hi Haroltan,

Where are the 'tracking id location' and 'estimated arrival date' located?

Is it always the same columns?

It would probably help if you upload your file (careful with sensitive data) or just a dummy file to see how your sheets are made up. You can use free file sharing sites like www.speedyshare.com or ge.tt for that and then post back the download link.

For what I understand now is that you have 1 master sheet and 1 data sheet. Then you want to extract specific data from the data sheet to the master sheet so you can delete the data sheet to make room for the next data sheet and repaet the proces. Then all is left is how you setup your sheet, i.e. which data needs to go where.

Best regards,
Trowa
>
Posts
2761
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2021

Yes so the columns are C,D,AW they never change its be great if it breaks it down by the different dates
Posts
4
Registration date
Monday May 2, 2016
Status
Member
Last seen
May 5, 2016

http://speedy.sh/BFwX5/TEST.xlsx

here is the spread sheet so DATA file is how i want it to look pretty much i want the date to tracking id and shipment id under the date for all the dates the dates will change as i put in new data of course
Posts
2761
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2021
462
Hi Haroltan,

Not sure what you did with the dates in Sheet "Sheet". The date cannot be found on the "Data" sheet. But when I enter the cell (with date on "Sheet") and confirm it will be found.

The second thing that could mess up the code is that column A and B of "Data" sheet contains header "Tracking Id" while the other columns don't. Make sure the headers are consistent.

Once you tackle the above issues then try this code:
Sub RunMe()
Dim fValue As Range
Sheets("Sheet").Select

For Each cell In Range _
("AW2:AW" & Range("AW" & Rows.Count).End(xlUp).Row)
    Set fValue = Sheets("Data").Rows("1:1").Find(cell)
    If fValue Is Nothing Then
        MsgBox ("Date " & cell & " not found on Data sheet." & _
        vbNewLine & vbNewLine & "Code will now abort.")
        Exit Sub
    Else:
        Range("C" & cell.Row).Copy _
        Sheets("Data").Cells(Rows.Count, fValue.Column). _
        End(xlUp).Offset(1, 0)
        Range("D" & cell.Row).Copy _
        Sheets("Data").Cells(Rows.Count, fValue.Column + 1). _
        End(xlUp).Offset(1, 0)
    End If
Next cell
End Sub


Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!