Try this macro.

Sub CombineData()

CFname = Application _
.GetOpenFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
Title:="Open C File")
If CFname = False Then
MsgBox ("Cannot open file - exiting macro")
Exit Sub
End If

QFname = Application _
.GetOpenFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
Title:="Open Q File")
If QFname = False Then
MsgBox ("Cannot open file - exiting macro")
Exit Sub
End If

WFname = Application _
.GetOpenFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
Title:="Open W File")
If WFname = False Then
MsgBox ("Cannot open file - exiting macro")
Exit Sub
End If

filesavename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If filesavename = False Then
MsgBox ("Cannot Save file - Exiting macro")
Exit Sub
End If


Supplier = InputBox("Enter Supplier Name : ")

Set NewBk = Workbooks.Add
NewBk.SaveAs Filename:=filesavename
Set NewSht = NewBk.Sheets("Sheet1")
With NewSht
.Range("A1") = "Region"
.Range("B1") = "Platform"
.Range("C1") = "Config"
.Range("D1") = "Supplier"
.Range("E1") = "MONTH"
.Range("F1") = "WEEK"
.Range("G1") = "QT"
NewRowCount = 2
End With

Set OldBk = Workbooks.Open(Filename:=CFname)
Set OldSht = OldBk.Sheets("Sheet1")
StartRow = 1
StartDataCol = "D"
RegionCol = "B"
PlatformCol = "A"
ConfigCol = "C" 'Use unused column - no config
Call WriteData(NewSht, OldSht, _
NewRowCount, StartRow, StartDataCol, _
Supplier, RegionCol, PlatformCol, ConfigCol)
OldBk.Close savechanges:=False

Set OldBk = Workbooks.Open(Filename:=QFname)
Set OldSht = OldBk.Sheets("Sheet1")
StartRow = 4
StartDataCol = "E"
RegionCol = "A"
PlatformCol = "B"
ConfigCol = "C" 'Use unused column - no config
Call WriteData(NewSht, OldSht, _
NewRowCount, StartRow, StartDataCol, _
Supplier, RegionCol, PlatformCol, ConfigCol)
OldBk.Close savechanges:=False


Set OldBk = Workbooks.Open(Filename:=WFname)
Set OldSht = OldBk.Sheets("Sheet1")
StartRow = 1
StartDataCol = "E"
RegionCol = "A"
PlatformCol = "B"
ConfigCol = "C"
Call WriteData(NewSht, OldSht, _
NewRowCount, StartRow, StartDataCol, _
Supplier, RegionCol, PlatformCol, ConfigCol)
OldBk.Close savechanges:=False

End Sub

Sub WriteData(NewSht, OldSht, _
ByRef NewRowCount, StartRow, StartDataCol, _
Supplier, RegionCol, PlatformCol, ConfigCol)

With OldSht
OldRowCount = StartRow + 2 'skip month and week rows
DataCol = .Range(StartDataCol & 1).Column
Do While .Range("A" & OldRowCount) < ""
Region = .Range(RegionCol & OldRowCount)
Platform = .Range(PlatformCol & OldRowCount)
Config = .Range(ConfigCol & OldRowCount)
Do While .Cells(StartRow, DataCol) < ""
Mon = .Cells(StartRow, DataCol)
Wk = .Cells(StartRow + 1, DataCol)
QTY = .Cells(OldRowCount, DataCol)

With NewSht
.Range("A" & NewRowCount) = Region
.Range("B" & NewRowCount) = Platform
.Range("C" & NewRowCount) = Config
.Range("D" & NewRowCount) = Supplier
.Range("E" & NewRowCount) = Mon
.Range("F" & NewRowCount) = Wk
.Range("G" & NewRowCount) = QTY
End With
NewRowCount = NewRowCount + 1
DataCol = DataCol + 1
Loop

OldRowCount = OldRowCount + 1

Loop
End With
End Sub


"C02C04" wrote:

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