Copying data from multiple excel files to one excel file through Macro / VBA

This is my first VBA program:) I wanted to copy a specific data form 14 files into one file. The macro I developed to do this is below. Its exactly developed in the way I wanted it but u can change it according to ur requirements. Now I am working on to use for loop to make the code efficient.

   1: Sub Macro5()

   2: '

   3: Dim FileName1 As String

   4: Dim Day1 As String

   5: Dim FileName2 As String

   6: Dim FileName3 As String

   7: Dim FileName4 As String

   8: Dim FileName5 As String

   9: Dim OutputFileName1 As String

  10:  

  11: FileName1 = "\\folder\"

  12: FileName5 = "1- January 2010\"

  13: Day1 = "Jan 11"

  14: Day2 = "Jan 12"

  15: Day3 = "Jan 13"

  16: Day4 = "Jan 14"

  17: Day5 = "Jan 15"

  18: Day6 = "Jan 16"

  19: Day7 = "Jan 17"

  20: FileName2 = "\file_"

  21: FileName3 = " 2010.xls"

  22:  

  23: FileName4 = "\file2_"

  24:  

  25: OutputFileName1 = "Week 02 - KPIs.xlsm"

  26:     

  27: ' A -----------------------------------------------------------------------

  28:     

  29:     Workbooks.Open Filename:=FileName1 + FileName5 + Day1 + FileName2 + Day1 + FileName3

  30:     Rows("3:8").Select

  31:     Selection.Copy

  32:     Windows(OutputFileName1).Activate

  33:     Sheets("A").Select

  34:     Range("A02").Select

  35:     ActiveSheet.Paste

  36:     Application.CutCopyMode = Flase

  37:     Workbooks(Right(FileName2, 22) + Day1 + FileName3).Close

  38:  

  39:     

  40:     Workbooks.Open Filename:=FileName1 + FileName5 + Day2 + FileName2 + Day2 + FileName3

  41:     Rows("3:8").Select

  42:     Selection.Copy

  43:     Windows(OutputFileName1).Activate

  44:     Range("A08").Select

  45:     Sheets("A").Select

  46:     ActiveSheet.Paste

  47:     Application.CutCopyMode = Flase

  48:     Workbooks(Right(FileName2, 22) + Day2 + FileName3).Close

  49:     

  50:     Workbooks.Open Filename:=FileName1 + FileName5 + Day3 + FileName2 + Day3 + FileName3

  51:     Rows("3:8").Select

  52:     Selection.Copy

  53:     Windows(OutputFileName1).Activate

  54:     Range("A14").Select

  55:     Sheets("A").Select

  56:     ActiveSheet.Paste

  57:     Application.CutCopyMode = Flase

  58:     Workbooks(Right(FileName2, 22) + Day3 + FileName3).Close

  59:     

  60:     Workbooks.Open Filename:=FileName1 + FileName5 + Day4 + FileName2 + Day4 + FileName3

  61:     Rows("3:8").Select

  62:     Selection.Copy

  63:     Windows(OutputFileName1).Activate

  64:     Range("A20").Select

  65:     Sheets("A").Select

  66:     ActiveSheet.Paste

  67:     Application.CutCopyMode = Flase

  68:     Workbooks(Right(FileName2, 22) + Day4 + FileName3).Close

  69:     

  70:     Workbooks.Open Filename:=FileName1 + FileName5 + Day5 + FileName2 + Day5 + FileName3

  71:     Rows("3:8").Select

  72:     Selection.Copy

  73:     Windows(OutputFileName1).Activate

  74:     Range("A26").Select

  75:     Sheets("A").Select

  76:     ActiveSheet.Paste

  77:     Application.CutCopyMode = Flase

  78:     Workbooks(Right(FileName2, 22) + Day5 + FileName3).Close

  79:     

  80:     Workbooks.Open Filename:=FileName1 + FileName5 + Day6 + FileName2 + Day6 + FileName3

  81:     Rows("3:8").Select

  82:     Selection.Copy

  83:     Windows(OutputFileName1).Activate

  84:     Range("A32").Select

  85:     Sheets("A").Select

  86:     ActiveSheet.Paste

  87:     Application.CutCopyMode = Flase

  88:     Workbooks(Right(FileName2, 22) + Day6 + FileName3).Close

  89:     

  90:     Workbooks.Open Filename:=FileName1 + FileName5 + Day7 + FileName2 + Day7 + FileName3

  91:     Rows("3:8").Select

  92:     Selection.Copy

  93:     Windows(OutputFileName1).Activate

  94:     Range("A38").Select

  95:     Sheets("A").Select

  96:     ActiveSheet.Paste

  97:     Application.CutCopyMode = Flase

  98:     Workbooks(Right(FileName2, 22) + Day7 + FileName3).Close

  99:     

 100:     Range("A02").Select

 101:     

 102: 'M -------------------------------------------------------------------------

 103:     

 104:     Workbooks.Open Filename:=FileName1 + FileName5 + Day1 + FileName4 + Day1 + FileName3

 105:     Rows("3:8").Select

 106:     Selection.Copy

 107:     Windows(OutputFileName1).Activate

 108:     Sheets("M").Select

 109:     Range("A02").Select

 110:     ActiveSheet.Paste

 111:     Application.CutCopyMode = Flase

 112:     Workbooks(Right(FileName4, 19) + Day1 + FileName3).Close

 113:  

 114:     

 115:     Workbooks.Open Filename:=FileName1 + FileName5 + Day2 + FileName4 + Day2 + FileName3

 116:     Rows("3:8").Select

 117:     Selection.Copy

 118:     Windows(OutputFileName1).Activate

 119:     Range("A08").Select

 120:     Sheets("M").Select

 121:     ActiveSheet.Paste

 122:     Application.CutCopyMode = Flase

 123:     Workbooks(Right(FileName4, 19) + Day2 + FileName3).Close

 124:     

 125:     Workbooks.Open Filename:=FileName1 + FileName5 + Day3 + FileName4 + Day3 + FileName3

 126:     Rows("3:8").Select

 127:     Selection.Copy

 128:     Windows(OutputFileName1).Activate

 129:     Range("A14").Select

 130:     Sheets("M").Select

 131:     ActiveSheet.Paste

 132:     Application.CutCopyMode = Flase

 133:     Workbooks(Right(FileName4, 19) + Day3 + FileName3).Close

 134:     

 135:     Workbooks.Open Filename:=FileName1 + FileName5 + Day4 + FileName4 + Day4 + FileName3

 136:     Rows("3:8").Select

 137:     Selection.Copy

 138:     Windows(OutputFileName1).Activate

 139:     Range("A20").Select

 140:     Sheets("M").Select

 141:     ActiveSheet.Paste

 142:     Application.CutCopyMode = Flase

 143:     Workbooks(Right(FileName4, 19) + Day4 + FileName3).Close

 144:     

 145:     Workbooks.Open Filename:=FileName1 + FileName5 + Day5 + FileName4 + Day5 + FileName3

 146:     Rows("3:8").Select

 147:     Selection.Copy

 148:     Windows(OutputFileName1).Activate

 149:     Range("A26").Select

 150:     Sheets("M").Select

 151:     ActiveSheet.Paste

 152:     Application.CutCopyMode = Flase

 153:     Workbooks(Right(FileName4, 19) + Day5 + FileName3).Close

 154:     

 155:     Workbooks.Open Filename:=FileName1 + FileName5 + Day6 + FileName4 + Day6 + FileName3

 156:     Rows("3:8").Select

 157:     Selection.Copy

 158:     Windows(OutputFileName1).Activate

 159:     Range("A32").Select

 160:     Sheets("M").Select

 161:     ActiveSheet.Paste

 162:     Application.CutCopyMode = Flase

 163:     Workbooks(Right(FileName4, 19) + Day6 + FileName3).Close

 164:     

 165:     Workbooks.Open Filename:=FileName1 + FileName5 + Day7 + FileName4 + Day7 + FileName3

 166:     Rows("3:8").Select

 167:     Selection.Copy

 168:     Windows(OutputFileName1).Activate

 169:     Range("A38").Select

 170:     Sheets("M").Select

 171:     ActiveSheet.Paste

 172:     Application.CutCopyMode = Flase

 173:     Workbooks(Right(FileName4, 19) + Day7 + FileName3).Close

 174:   

 175:     Range("A02").Select

 176:     Sheets("A").Select

 177:     

 178:     

 179: End Sub

 180:  

References:

For taking substring

http://www.programmersheaven.com/mb/vba/97607/97607/substring-function-in-vba/

http://www.xtremevbtalk.com/showthread.php?t=277326

Function Right()

http://www.techonthenet.com/access/functions/string/right.php

Closing a file

http://www.contextures.com/xlfaqMac.html#Close

http://www.vbforums.com/showthread.php?t=534068

http://www.vbforums.com/showthread.php?t=511723

Closing a file with copypaste mode off

http://www.exceltip.com/st/Close_a_workbook_using_VBA_in_Microsoft_Excel/469.html

String concatenation

http://www.vbtutor.net/lesson6.html

Opening a file

http://www.ozgrid.com/VBA/loop-through.htm

Variables

http://www.ozgrid.com/VBA/variables.htm

Duplicating Conditional Formatting Rules

http://excelusergroup.org/forums/p/497/1480.aspx

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.programming&tid=80c0a31b-6383-461a-bc36-b8b88aef0ed7&cat=en_US_3a793e1f-4961-419d-9ec7-899d6e6086cd&lang=en&cr=US&sloc=&p=1

http://blogs.msdn.com/excel/archive/2006/05/09/594200.aspx

Function IF()

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=242

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=225

- urShadow