title: Excel VBA to duplicate and fill the default template based on number of rows
body: <p>I have a default template and need to populate the value in A column (Material) of the output sheet from column I of the source template. I created a macro which duplicates the number of output row based on number of parts in source template. The issue here is the part number is populated only in the first column and its not looping to the other blank rows.</p>
  
  <p>Source Template
  <a href="https://i.stack.imgur.com/1Aaio.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1Aaio.png" alt="enter image description here"></a></p>
  
  <p>Sample Output sheet
  <a href="https://i.stack.imgur.com/8bECg.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8bECg.png" alt="enter image description here"></a></p>
  
  <p>Result:</p>
  
  <p><a href="https://i.stack.imgur.com/gOGH5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gOGH5.png" alt="enter image description here"></a></p>
  
  <p>VBA Code:</p>
  
  <pre><code>Sub Process_File()
  Dim Src_File As Workbook
  Dim Out_Template As Workbook
  
  Dim Src_Tot_Row, Out_Tot_Row As Integer
  Dim REG_CODE
  
  REG_CODE = "C299"
   Set Src_File = Workbooks.Open("C:\Users\raja\Desktop\NPI Automation\Sadhan\Source_Data.xlsx") 'Read source file name
      Set Out_Template = Workbooks.Open("C:\Users\raja\Desktop\NPI Automation\Sadhan\Plant\AMS.xlsx") 'Read output template file name
  
   '------------------------------------------------------------------- Portion-2
    ' Workbooks.Open (Sheet1.Range("G7").Value) ' Open source excel file
  
    Src_File.Sheets("Input_sheet").Activate
  
  If Range("I7").Value &lt;&gt; "Part numbers" Then ' Checking correct input file
      MsgBox "Select correct source file.!"
      End
  End If
  
  
  Range("I8").Select
  Selection.End(xlDown).Select
  
  Src_Tot_Row = ActiveCell.Row
  
      '------------------------------------------------------------------- Portion-3
  
       ' Workbooks.Open (Sheet1.Range("G9").Value) ' Open output template excel file
  
  Out_Template.Sheets("Plant").Activate 'Find Total Rows in Output Template
  Range("B1").Select
  Selection.End(xlDown).Select
  
  Out_Tot_Row = ActiveCell.Row
  
  Dim Temp_Row_Calc As Integer
  Temp_Row_Calc = Src_Tot_Row - 7
  Temp_Row_Calc = (Out_Tot_Row - 2) * Temp_Row_Calc ' Calculate total rows for data duplicate
  
  Range("A2:AJ" &amp; Out_Tot_Row).Copy
  Range("A" &amp; Out_Tot_Row + 1 &amp; ":AJ" &amp; Temp_Row_Calc + 2).PasteSpecial xlPasteValues
  
      '------------------------------------------------------------------- Portion-4
  Range("A1").EntireColumn.Insert ' Inserting temporary column for sorting back
  
  Range("A1").Value = "1"
  Range("A" &amp; Temp_Row_Calc - 1).Select
  
  Temp_Row_Calc = Temp_Row_Calc - 1
  
  Range(Selection, Selection.End(xlUp)).Select
  Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
      Step:=1, Stop:=Temp_Row_Calc, Trend:=False
  
  If ActiveSheet.AutoFilterMode = False Then ' Check Filter Mode and apply
    ActiveSheet.Range("A1").AutoFilter
  End If
  
  ActiveSheet.AutoFilter.Sort.SortFields.Clear
  ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range( _
      "C1:C" &amp; Temp_Row_Calc), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
      xlSortNormal
  With ActiveWorkbook.Worksheets("Plant").AutoFilter.Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With
  
  For I = 2 To Temp_Row_Calc
      If Range("C" &amp; I).Value = REG_CODE Then
          Src_File.Sheets("Input_Sheet").Activate 'Activate Source Excel
  
          ReDim ary(1 To Src_Tot_Row - 1) ' Copy material numbers
          For j = 1 To Src_Tot_Row - 1
              ary(j) = Src_File.Sheets("Input_Sheet").Cells(j + 1, 1)
          Next j
  
          Range("I8:I" &amp; Src_Tot_Row).Copy 'Copy source part numbers
  
          Out_Template.Sheets("Plant").Activate 'Activate Out Template Excel
          Range("B" &amp; I).SpecialCells(xlCellTypeVisible).PasteSpecial (xlPasteValues)
  
          ActiveSheet.AutoFilter.Sort.SortFields.Clear
          ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range( _
              "A1:A" &amp; Temp_Row_Calc), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
              xlSortNormal
          With ActiveWorkbook.Worksheets("Plant").AutoFilter.Sort
              .Header = xlYes
              .MatchCase = False
              .Orientation = xlTopToBottom
              .SortMethod = xlPinYin
              .Apply
          End With
  
          'filtervalues = """8121-0837"", ""B5L47-67901"", ""B5L47-67903"", "" ="""
         ary(Src_Tot_Row - 7) = ""
  
          ActiveSheet.Range("$A$1:$AJ$" &amp; Temp_Row_Calc).AutoFilter Field:=2, Criteria1:=ary, Operator:=xlFilterValues
  
          Dim cl As Range, rng As Range
  
          Set rng = Range("A2:A" &amp; Temp_Row_Calc)
  
          For Each cl In rng
              If cl.EntireRow.Hidden = False Then 'Use Hidden property to check if filtered or not
                  If cl &lt;&gt; "" Then
                      x = cl
                  Else
                      cl.Value = x
                  End If
              End If
          Next
  
          Exit For
      End If
  Next I
  
  If ActiveSheet.AutoFilterMode Then ' Check Filter Mode and apply
    ActiveSheet.Range("A1").AutoFilter
  End If
  
  Columns(1).EntireColumn.Delete
  
        MsgBox "Completed!"
  
     '-------------------------------------------------------------------
  End Sub
  
  Function GetFilenameFromPath(ByVal strPath As String) As String
  If Right$(strPath, 1) &lt;&gt; "\" And Len(strPath) &gt; 0 Then
      GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
  End If
  End Function
  
  
  Sub Test()
  Range("A1").Value = "1"
  Range("A" &amp; Out_Tot_Row).Select
  
  Range(Selection, Selection.End(xlUp)).Select
  Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
      Step:=1, Stop:=Out_Tot_Row, Trend:=False
  End Sub
  </code></pre>