Extracting data from sheets added everyhour

Closed
haroltan Posts 4 Registration date Monday May 2, 2016 Status Member Last seen May 5, 2016 - May 2, 2016 at 09:34 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 10, 2016 at 11:45 AM
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 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 2, 2016 at 11:46 AM
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
0
haroltan Posts 4 Registration date Monday May 2, 2016 Status Member Last seen May 5, 2016
May 2, 2016 at 04:24 PM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 3, 2016 at 11:08 AM
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
0
Haroltan > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
May 3, 2016 at 03:02 PM
Yes so the columns are C,D,AW they never change its be great if it breaks it down by the different dates
0
haroltan Posts 4 Registration date Monday May 2, 2016 Status Member Last seen May 5, 2016
May 3, 2016 at 04:20 PM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 10, 2016 at 11:45 AM
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
0