VBA Copy, Paste, Match query

[ckdragon]

HELLO – I feel like I am asking for the moon with this and I don’t know if this going to be possible, but worth the shot to ask the brain trust….


Some background info…

• I am currently building a billing spreadsheet for our business.
• Essentially, we hire out trucks and bill on a time and km basis but only on certain days in the week, we are a remote business, have trucks in different bases and do not have staff on sight to process payments etc.
• Currently we do everything manually thus why I am building this spreadsheet as things have gotten a bit out of hand.

I currently have sheets that clean the booking data and the GPS tracking data from the raw report outputs from our systems.

I now need to consolidate the information into a single sheet that will apply the calculations and spit out the final number to charge.


I have managed to get the first part of the data (the booking info) copied across to the calculations spreadsheet (thanks mumps!) but now I need to get the GPS data copied across, but also matched to the right booking.

What I am looking for is something that will copy certain cells from the GPS data, based on location criteria and paste it into the calculations spreadsheet, but only if it is a match to the booking data that is already there.

If there is no match, for example there is a booking but no GPS data that matches the parameters (the customer booked, but never came and got the truck) then I need it to skip pasting anything in that row.



Spreadsheet layouts.

Calculations Spreadsheet Layout

​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

7 (Headings)​

Truck​

Type​

Client Name​

Booking Duration​

Booking Start Date​

Booking Start Time​

Booking End Time​

Booking End Date​

Booking End Date Display​

Comments​

Ins​

Ins 2​

Blank​

GPS Start Date​

GPS Start Time​

GPS End Date​

GPS End Time​

8 (First row of data)

Truck 1

Small

John Smith

0.18:00:00​

28/03/2022​

10:30:00 AM​

3:30:00 PM​

28/03/2022​

28/03/2022​

Blank

GPS Data Sheet Layout (Cleaned via Power Query - Table Format)

A

B

C

D

E

F

G

H

I

J

K

L

1

Truck

Type

Start Date

Start Time

Stop Date

Stop Time

From Address

To Address

Duration

Distance

Odometer

Description

2

Truck 1

Small

28/03/2022​

10:36:58 AM​

28/03/2022​

10:53:45 AM​

Base 1

Stop 1

0:16:47

12.058​

140483​

3

Truck 1

Small

28/03/2022​

11:01:03 AM​

28/03/2022​

11:50:35 AM​

Stop 1

Stop 2

0:49:32

46.73​

140529​

Toll 1

4

Truck 1

Small

28/03/2022​

11:59:26 AM​

28/03/2022​

12:09:47 PM​

Stop 2

Stop 3

0:10:21

6.738​

140536​

5

Truck 1

Small

28/03/2022​

12:48:17 PM​

28/03/2022​

12:59:53 PM​

Stop 3

Stop 4

0:11:36

6.716​

140543​

6

Truck 1

Small

28/03/2022​

1:07:56 PM​

28/03/2022​

1:26:54 PM​

Stop 4

Stop 5

0:18:58

18.372​

140561​

Toll 1

7

Truck 1

Small

28/03/2022​

1:40:03 PM​

28/03/2022​

2:01:16 PM​

Stop 5

Stop 6

0:21:13

4.635​

140566​

8

Truck 1

Small

28/03/2022​

2:09:17 PM​

28/03/2022​

2:32:37 PM​

Stop 6

Base 1

0:23:20

18.153​

140584​

9

Blank

Blank

Blank

Blank

Blank

Blank

Blank

Blank

Blank

(Total Distance Calculated Here)

Blank

Blank

Expected Output in Calculation Sheet

​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

7 (headings)​

Truck​

Type​

Client Name​

Booking Duration​

Booking Start Date​

Booking Start Time​

Booking End Time​

Booking End Date​

Booking End Date Display​

Comments​

Ins​

Ins 2​

Blank​

GPS Start Date​

GPS Start Time​

GPS End Date​

GPS End Time​

8 (first data row)

Truck 1

Small

John Smith

0.5:00:00​

28/03/2022​

10:30:00 AM​

3:30:00 PM​

28/03/2022​

28/03/2022​

Blank

28/03/2022​

10:36:58 AM​

28/03/2022​

2:32:37 PM​

​

​

​

​

​

​

Copied from GPS DATA: C2

Copied from GPS DATA: D2

Copied from GPS DATA: E8

Copied from GPS DATA: F8

The criteria I need to match is

• when the FROM address = Base 1
  THEN COPY Start Date (GPS Data Sheet: C2) and Start Time (GPS Data Sheet: D2) to GPS Start Date (Calculations Sheet: O8) and GPS Start Time (Calculations Sheet: P8) >> So only copy the Date and Time that is on that same row as the Base name
  BUT ONLY if Start Date (GPS Data Sheet: C2) = Booking Start Date (Calculations Sheet: F8)
  AND if Start Time (GPS Data Sheet: D2) is close to the Booking Start Time (Calculations Sheet: F8)
  • Buy close to, I mean that the GPS Start time can be after the Boking start time (as this is the time that the GPS started recording) but it sometimes can be a bit before (up to 30mins) the booking start time (as Customer’s might come and pick it up before the *official* booking time starts)

• when the TO address = Base 1
  THEN COPY Stop Date (GPS Data Sheet: E8) and End Time (GPS Data Sheet: F8) to GPS End Date (Calculations Sheet: Q8) and GPS End Time (Calculations Sheet: R8) >> So only copy the Date and Time that is on that same row as the Base name
  BUT ONLY if End Date (GPS Data Sheet: E8) = Booking End Date (Calculations Sheet: I8)
  AND if End Time (GPS Data Sheet: F8) is close to the Booking End Time (Calculations Sheet: H8)
  • Buy close to, I mean that the GPS End time can be before the Booking End time (as the Customer may have returned it earlier) but it sometimes can be a bit after (up to 30mins) the booking end time (as the Customer might have returned it later)

As you can see that there is a lot of data to work through – I also can’t filter it and copy as there is another code I have written which calculates the total distance the truck has travelled between FROM Base 1 and TO Base 1 and then copies that into another part of the Calculations sheet.


I have tried a couple of things and then best I can come up with is a copy paste VBA (as below)

I originally had it going into a separate sheet, but that caused more problems when having to match that consolidated info as it sometimes copied the wrong info or there was a GPS error etc, but would love for the code to do the heavy lifting and thinking of matching the info to the booking info.



If even part of this is remotely possible, I would be so so so grateful.



Please and Thank you!

Public Sub Copy_Data_Clean_To_Update_and_Test()

Application.ScreenUpdating = False


For i = 2 To 500

Dim copySheet As Worksheet

Dim pasteSheet As Worksheet


Set copySheet = Worksheets("GPS Data")

Set pasteSheet = Worksheets("Consolidated")


If copySheet.Cells(i, 6).Value = "Base " Then

copySheet.Cells(i, 1).Copy


pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


End If


If copySheet.Cells(i, 6).Value = "Base " Then

copySheet.Cells(i, 2).Copy


pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


End If


If copySheet.Cells(i, 6).Value = "Base " Then

copySheet.Cells(i, 3).Copy


pasteSheet.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


End If


If copySheet.Cells(i, 7).Value = "Base " Then

copySheet.Cells(i, 4).Copy


pasteSheet.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


End If


If copySheet.Cells(i, 7).Value = "Base " Then

copySheet.Cells(i, 5).Copy


pasteSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


End If


If copySheet.Cells(i, 6).Value = "" Then

copySheet.Cells(i, 9).Copy


pasteSheet.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


End If

Next

Application.CutCopyMode = False

Application.ScreenUpdating = True

 

[bebo021999]

Because you have many trucks and many bases
I dont know GPS Data is mixed of trucks and bases or not, but if it is, it is complicate, need to one more another sheet, called "basic information" with: truck list (ID, size, register plate,...), Base list (ID, address,...)
As done, try to share the file via public sharing software, or google share...

 

[ckdragon]

Because you have many trucks and many bases
I dont know GPS Data is mixed of trucks and bases or not, but if it is, it is complicate, need to one more another sheet, called "basic information" with: truck list (ID, size, register plate,...), Base list (ID, address,...)
As done, try to share the file via public sharing software, or google share...

Hi there!

All the info is sorted the same across the sheets if that helps.

(The above tables are just pure copies from the data I'm currently working off to build this thing and show how the data displays that I am trying to search and copy from - I can't post the data set at this stage unfortunately)

Truck 1 always comes up first in both the booking and the GPS data, then it is sorted based on the booking date and then time.

The GPS Data is the same, Truck 1 then start date and then start time.

(The Raw data is all jumbled, but sorting it by Truck, then start date then start time means that everything is in line - I just now really need it to do the coping and pasting and matching)

When I manually do this I can literally see that the first set of GPS data is correlated to the first booking for that Truck... It's only if a truck is booked and then not taken and therefore the next set Start Time is REALLY off (like it could be hours or days different) that i know that the Truck didn't actually go out and then i just paste that Start time to the next booking which it correlates to

Bases are literally called "Base 1" or "Base 2"

But at this stage, I'm just trying to get some code that focuses on Base 1 information and then will look at duplicating (we don't do a lot from Base 2 and Base 2 Trucks are in a separate sheet) so I'm only looking for Base 1 tags and to see if this is even possible

I hope that helps!

But if there is something else, let me know. I will work on cleaning the data a bit more to upload a bit more of an example

Thank you!

 

[ckdragon]

I'm unable to upload our files for review but if anyone has any rough idea on how a lookup/match/copy/paste function might work on the data as above, i might be able to test it more once i can apply it to the whole sheet.

Thank you!