Hi All,

I’m new to VBA. I’ve been looking for a while to see how to solve my problem. I’m looking for a code that will allow me to automatically copy specific data from 2 different workbooks into one existing master workbook according to specific formatting.
Please find below more information and examples of the workbooks used in attachment

I have:

* Wb01 = Sales Forecast Sales Person 01
* Wb02 = Sales Forecast Sales Person 02
* Wb03 = Sales Forecast Sales Person 03
* Wb04 = Recurring Business for all sales people
Sheet1 = Overview
Sheet2 = Recurring Business for Sales person 01
Sheet3 = Recurring Business for Sales person 02
Sheet4 = Recurring Business for Sales person 03

What I am trying to achieve:

1 master sales forecast report, called “Cash Flow Report”, with 1st sheet as an overview and Sheet 2= combination of data on wb01 sheet 1 and wb04 sheet 2.
I will need to repeat this code to other sheet of the report as we have 3 sales people. The data from wb01 and wb04 sheet2 should, ideally, be formatted on “cash Flow report” sheet 2 according to this:

* The data needed on Wb04 sheets 2 needs to be column B:C and E:BO – the number of line can change (some line will be added later during the year)

* The data needed from Wb01 is found on the 1st sheet of the each workbook “overview”. All columns are required (same column formatting as Cash flow Report) – number of line can differ.

* The table on sheet 2 of Cash flow Report has 4 categories (header and if possible a total row, can be the same):
1. Recurring : data from Wb04 sheet2
2. Order : data from Wb01 sheet 01
3. Prospect: data from Wb01 sheet 01
4. Pipeline: data from Wb01 sheet 01

For categories 2-4: category stated in the 1st column of wb01 sheet1

I would like to sort the table by category and add a Header/Total Row for each category. And if possible at the bottom after category 4, add a grand total.
Please see the “Desire layout Workbook” to have a better understanding.

Parameters:

The master sales forecast report will be located in a different drive.
The macro will need to be run from the master sales forecast report.

Many Thanks in advance,

Sophie

Master Cash Flow Report.xlsx
Master Desire layout.xlsx
WB01 Sales Forecast Sales Person 01.xlsm
Wb04 Recurring Business.xlsx