VBA help with Data transfer

Aberdham

Board Regular
Joined
Mar 8, 2018
Messages
162
Office Version
  1. 365
Platform
  1. MacOS
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 :)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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

Board Regular
Joined
Mar 8, 2018
Messages
162
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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

Board Regular
Joined
Mar 8, 2018
Messages
162
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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

Board Regular
Joined
Mar 8, 2018
Messages
162
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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?
 

Forum statistics

Threads
1,148,294
Messages
5,745,942
Members
423,985
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top