VBA Help - Copy the daily data into one sheet

[Tej]

i have one monthly report which pulls data from individual excel file for each day.

the data for each month is lying horizontally in 7 cells and i need to pull it vertically.
from -

this is how monthly looks

i am using - hlookup to pull the data for each cells..

and code is going too lengthy

 

[Luke M]

It looks like your source data is actually in a "preferred" layout, as you could create a PivotTable from this setup and easily create the desired output. The PT then has the benefit of easier analysis/change of layout, if desired.

 

[Tej]

@Luke M i was trying to put all daywise data in a monthly consolidated file.
i understand with PT can be used for better analysis but I just need to put all #'s in one file.
I am using HLooup and then replacing the date as below,
its going too long.

Range("D16").Select
  Application.CutCopyMode = False
  ActiveCell.FormulaR1C1 = _
  "=HLOOKUP(R16C3,'[EOD Report 10-01-2014.xlsb]CLIENT 10'!R6C2:R65C12,2,0)"
  Range("D17").Select
  ActiveCell.FormulaR1C1 = _
  "=HLOOKUP(R17C3,'[EOD Report 10-01-2014.xlsb]CLIENT 10'!R6C2:R65C12,2,0)"
  Range("D18").Select
  ActiveCell.FormulaR1C1 = _
  "=HLOOKUP(R18C3,'[EOD Report 10-01-2014.xlsb]CLIENT 10'!R6C2:R65C12,2,0)"
  Range("D21").Select
  ActiveCell.FormulaR1C1 = _
  "=HLOOKUP(R21C3,'[EOD Report 10-01-2014.xlsb]CLIENT 10'!R6C2:R65C12,2,0)"
  Range("D22").Select
  ActiveCell.FormulaR1C1 = _
  "=HLOOKUP(R22C3,'[EOD Report 10-01-2014.xlsb]CLIENT 10'!R6C2:R65C12,2,0)"
 
  Range("d16:d22").Select
 Selection.Copy
  Range("E16").Select
  Selection.PasteSpecial Paste:=xlPasteFormulas
  Range("E16:E22").Select
  Selection.Replace What:="10-01", Replacement:="10-02"

 

[Luke M]

Would it be possible for you to post an actual workbook with data (or dummy data) for us to work with? It's difficult to understand the layout fully with just pictures, and writing macros tends to need to be very specific in nature.