VBA help with Data transfer

[Aberdham]

Hey guys,

I am hoping to get some help with the VBA coding to transfer certain columns in 2 output sheets into one master file.

so basically in

· in 2018_sales file; exclude all rows that indicates “other” in column C
· If column V is CF in 2018_sales file , then refer to column W, transfer the data based on the starting date to the respective month. (i.e.: start: 1-5-2018 allocates to May-18 section; 1-6-2018 to Jun-18; retrospectively)
· If column V is AF, then refer to column W -1, transfer them to the current month.



2018_sales to Monthly-tracking file
A A
D B
E C
F D
J E
M F
--------------------------------------------------------------------------------------------------------------------------------

· in 2018_cost file, exclude all the rows in column A indicates “other & overhead”
· Transfer all the rows to the respective months based on the month indicated in column B (cost file)


2018_Cost to Monthly-tracking file
A A
D B
E C
F D
J E
M F
--------------------------------------------------------------------------------------------------------------------------------

I have uploaded the file in onedrive to be edited, and note that the expected results is indicated in the monthly-tracking file.

https://1drv.ms/w/s!Aqt4VfikFsyKgSccePa2WnBYMwii (input)
https://1drv.ms/x/s!Aqt4VfikFsyKgSUTPSv_ya__1xMn. (monthly-tracking)
https://1drv.ms/x/s!Aqt4VfikFsyKgSN_5CAtiL89Rimb. (2018_sales)
https://1drv.ms/x/s!Aqt4VfikFsyKgSGsxstu9sFVET4- (2018_costs)

Any help will be greatly appreciated.
Happy coding

 

[mumps]

Could you clarify in detail what you mean by:

If column V is AF, then refer to column W -1, transfer them to the current month.

Use a few examples from your data and refer to specific cell, rows, columns and sheets to explain what you mean by "column W -1". Where is "the current month" found?

 

[Aberdham]

Could you clarify in detail what you mean by:
Use a few examples from your data and refer to specific cell, rows, columns and sheets to explain what you mean by "column W -1". Where is "the current month" found?

hey mumps,
thank you for your quick response.

To clarify the problem.
Please first refer to 2018 sales sheet. Row 9 column V indicates AF, then refer to column W, if the date in column W falls into the April period. Transfer the respective column in tutorial sales May in the monthly tracking file. The same approach applies for all the AF type. If the date falls into the interval of May then it should transfer the data to tutorial sales June tab.

I hope that clarifies my request

 

[mumps]

In the 2018_Cost file, in column B you have dates for may, June, July and August. Where do you want to transfer the rows for July and August? Also the dates in column B include the time. Do you need the time or would the date only be OK?

 

[Aberdham]

In the 2018_Cost file, please only take column B as a condition. if it is in May, then it should be transferred to the tutorial-cost May.
the only exception is the AF condition in the 2018_Sales file. (CF follows the same rule as the cost file)
Thank you.

 

[mumps]

You said:

· in 2018_cost file, exclude all the rows in column A indicates “other & overhead”
· Transfer all the rows to the respective months based on the month indicated in column B (cost file)

What I'm saying is that column B contains dates for May, June, July and August. Do you want only the May dates transferred?

 

[Aberdham]

Hi Mumps,

Let's have a walkthrough about this:

the file I provided are only an extract, the original file contains records dates back to 2017.
in the 2018_cost file, exclude all the rows that indicates "other" and "overhead" in column A.
Then transfer all the rows to the respective months based on the period indicated in column B to the monthly-tracking file.
There are supposed to be more tabs such as: tutorial-sales April, tutorial-fees April, tutorial-sales august, tutorial-fees august etc which I have not added there.

in the 2018_sales file, (referring columns V,W)
If column V is "AF", refer to column W, and transfer to the respective month -1 in the monthly-tracking file.
... is "CF", transfer to the respective month based on period in column W.

I hope it helps
Thank you

 

[mumps]

What may work on an extract will most likely not work on the actual file. Please upload versions of the 3 files that include that include dates back to 2017 and tabs such as tutorial-sales April, tutorial-fees April, tutorial-sales august and tutorial-fees august.

 

[Aberdham]

Hey Mumps, I tried to auto-populate with the records of 2017. Please see below file:

In the monthly-tracking file, you can see the expected results.

https://1drv.ms/x/s!Aqt4VfikFsyKgSlN3Gh1zjxyKSHy (cost)
https://1drv.ms/x/s!Aqt4VfikFsyKgSsruSJ0MRpqWAE1 (sales)
https://1drv.ms/x/s!Aqt4VfikFsyKgS0mNrAfbuj1KPBp (tracking)

Hopefully this will suffice.

Thank you very much !

 

[mumps]

I seem to have accidentally deleted the monthly tracking file that contained the original macro. Can you please post the last macro I sent you?