Seeking help here as I'm not good at VBA. This situation is about tranforming
data from user into a single output file with structured column format.
There are 3 files (C, Q and W) from user. They look similar but columns and
rows setup are slightly different. Each of these files represent data from a
supplier. I would like to tranform them into a single output file and adding
a column to identify the Supplier.

Below are details of the files and some sample data. Let me know if further
info is required. I'm looking for a fully automated process/VBA where the
output file is generated as soon as the C,Q and W files are available.

Thanks.


C.xls format:
Col A is Platform Name
Col B is Region
Col C is Unused
Col D onwards are data with Row 1 as Month and Row 2 as Week.

Col A Col B Col C Col D Col E Col F Col G Col H
Row1 DEC DEC DEC DEC JAN
Row2 Week44 Week45 Week46 Week47 Week48
Row3 Platform01 US F1 0 0 0 0 0
Row4 Platform02 US F4 3,844 5,064 5,613 3,822 2,283
Row5 Platform03 US F4 11,339 14,939 16,559 11,275 8,302
Row6 Platform04 US F4 0 0 0 0 0

Q.xls format:
Col A is Region
Col B is Platform Name
Col C is Unused
Col D is Type Name
Col E onwards are data with Row 4 as Month and Row 5 as Week.


Row1 Col A Col B Col C Col D Col E Col F Col G Col H
Row2
Row3
Row4 DEC DEC DEC DEC
Row5 Week44 Week45 Week46 Week47
Row6 US Platform01 OM - - - -
Row7 US Platform02 OM - - - -
Row8 US Platform04 OM 5,392 7,103 7,874 5,361
Row9 US Platform05 OM 5,228 6,887 7,634 5,198
Row10 US Platform06 OM 6,052 7,973 8,837 6,018


W.xls format:
Col A is Region
Col B is Platform Name
Col C is Config
Col D is Type Name
Col E onwards are data with Row 1 as Month and Row 2 as Week.

Col A Col B Col C Col D Col E Col F Col G Col H
Row1 DEC Dec Dec Dec
Row2 Week44 Week45 Week46 Week47
Row3 US Platform03 CTO OM 0 0 0 0
Row4 US Platform04 CTO OM 1,922 2,532 2,807 1,911
Row5 US Platform04 BTS OM 1,153 1,519 1,684 1,147
Row6 US Platform01 CTO OM 0 0 0 0
Row7 US Platform05 BTS OM 15,375 20,257 22,453 15,289
Row8 EMEA Platform03 CTO OM 0 0 0 0


Desired Output.xls format:
The Supplier column is taking on the file name (C, W or Q) provided by user.

Region Platform Config Supplier MONTH WEEK QTY
US Platform01 CTO W SEP Week31 5982.608544
US Platform01 CTO W SEP Week32 5801.804102
US Platform01 CTO W SEP Week33 6955.543465
US Platform01 CTO W SEP Week34 7260.043889
US Platform01 CTO W OCT Week35 4949.034663
US Platform01 CTO W OCT Week36 4743.036811
US Platform01 CTO W OCT Week37 5790.307877