Hello To All, whomever all is. I hope you enjoy challenges. I have the following data I am working on that I need help with. I have two VBA codes for inserting rows which exists in this file but am I struggling with the remaining code associated with copy/paste. For obvious reasons the employee data is fictitious.
The data reflects when an employee changed their time card to different codes (account, sub act, project, or task) data, or in other cases where you see from/to codes that are the same it indicates the only change is either decreasing/increasing hours worked, or where you see there is no CC From or CC To. These transactions is how which the data appears. Keep in mind I am working with 20,000+ rows of data all sorted by ID, Date, Project Code to get it into the From/To format and as you can tell there isn't a one to one ratio in distribution of hours which I am not concerned about. Column L, "False", indicates the next employee who changed their time card and where From/To format does not exist. The formula is listed above I. Line 14/15 is how the data should be per employee per date. I highlighted in yellow how codes seem to be the same but are slightly different.
END STATE: To get the data into a From/To Format consecutively by Employee Identification (E ID), on that EXP_ITEM_DATE (Date), not CC From CC From or CC To CC To consecutively.
The bottom line is that for every consecutive CC From or CC To I need to insert a row and copy the data from whichever row remaining that has the missing data, which would be a CC From or CC To, by Employee Identification (E ID), on that EXP_ITEM_DATE (Date), changing the copied rows to a different color of font so that they are easy to locate). If there is isn't a CC From or CC To offset then the existing data for those rows then the data that exists needs to be copied/inserted with the copied cells column "C" FR/TO, changed to whatever is missing and in column "K" HOURS, changed to zero (0).
Examples: As shown in Figure 2.
Look at employee Flinstone's transactions dated 4/10 and 4/11
4/10 – He is missing 2 CC From(s) in order to offset the remaining CC To(s). You would insert a row after 3 and 4 then you would copy the data from row 2 since it is the only CC From and paste the data in the newly inserted rows so that there is a From/To format.
4/11 – He is missing 1 CC To(s) in order to offset the remaining CC From(s). You would insert a row after 6 and then you would copy the data from row 8 since it is the only CC To and paste the data in the newly inserted row so that there is a From/To format.
Look at employee Dino's transactions dated 6/12 through 6/15
6/12 – 6/14 – There is no CC From(s) transactions in order to offset the CC To(s). You would copy the existing CC To(s), change column "C" CC To(s) to CC From(s), and then change column "K" HOURS to zero (0), then resort. [Doing the same for no CC To(s) – Bamm Bamm]
6/15 – He is missing 1 CC From(s) in order to offset the remaining CC To(s). You would insert a row after 26 and then you would copy the data from row 25 since it is the only CC From and paste the data in the newly inserted row so that there is a From/To format.
The attached file has two tabs. One instruction and the other Sample Data.
Figure 1 - The original time card.
The bold transactions that are in the correct format.
Step 1 (Figure 2) – Insert row
This is what it should look like when I insert rows. I have two macros listed in this file you can review and look at. The figure indicates the row that needs to be copied which is based on what is missing in order to get it into the CC From/CC To, format. In this case I need to insert a row after 3, 4, 6, 10, 11, 12, 16, 18, 19, etc……. so that I can copy/paste with either a CC From or a CC To, based on what transaction is missing for that Employee Identification (E ID) , on that EXP_ITEM_DATE (Date). See examples provided at the beginning of this email.
Look at employee Flinstone's transactions dated 4/10 and 4/11
4/10 – He is missing 2 CC From(s) in order to offset the remaining CC To(s). You would insert a row after 3 and 4 then you would copy the data from row 2 since it is the only CC From and paste the data in the newly inserted rows so that there is a From/To format.
4/11 – He is missing 1 CC To(s) in order to offset the remaining CC From(s). You would insert a row after 6 and then you would copy the data from row 8 since it is the only CC To and paste the data in the newly inserted row so that there is a From/To format.
Look at employee Dino's transactions dated 6/12 through 6/15
6/12 – 6/14 – There is no CC From(s) transactions in order to offset the CC To(s). You would copy the existing CC To(s), change column "C" CC To(s) to CC From(s), and then change column "K" HOURS to zero (0), then resort. [Doing the same for no CC To(s) – Bamm Bamm]
6/15 – He is missing 1 CC From(s) in order to offset the remaining CC To(s). You would insert a row after 26 and then you would copy the data from row 25 since it is the only CC From and paste the data in the newly inserted row so that there is a From/To format.
Step 2 (Figure 3) – Copy/Paste Data To The Inserted Rows
The rules for copying is based on what is missing in terms of CC From or CC To for that Employee Identification (E ID) , on that EXP_ITEM_DATE (Date), changing the copied rows to a different color of font so that they are easy to locate (blue bold). For the transactions not having a CC From or CC To then copy the existing data for those rows so that they can offset, changing copied/inserted cells column "C" CC FR/TO, to whatever is missing, and then in column "K" HOURS, changed to zero (0), then resort to get it into the FR/TO format for that date, then updating the True/False column which should then reflect only where a new employee begins on the spreadsheet.