title: how to select a specific cells range dynamically in excel vba?
body: <p>I've a scenario in which i'm copying data from multiple files to master files, I want when the program run at first time it should start pasting the data at my specified range in the master file which is working fine. But when the program is run again it's not going to start from the previous range rather it's start pasting data below at the previous record which is duplication of the same data, I want that when user first time or as many time run the program program range start at the same location where it was at first time running.
  Following is my code.</p>
  
  <pre><code>Sub Append()
   'Append data from other files
    Path = "E:\NPM PahseIII\"
   Dim c As Range
  
     'find the second empty cell in ColA
   Set c = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
     'target range for pasting data it first run this is actually pointing to 
     'my desire range but at second or multiple running the range is starting 
      'below at the previous record 
   Set targetcellL = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(2, 1)
   Set targetcellR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(5, 4)
   Filename = Dir(Path &amp; "*.xlsx")
  Do While Filename &lt;&gt; ""
  If InStr(Filename, ".") &gt; 0 Then
   Filenamenoext = Left(Filename, InStr(Filename, ".") - 1)
  End If
     c.Value = Filenamenoext
     Set c = c.Offset(4, 0)
  
    Set wb = Workbooks.Open(Filename:=Path &amp; Filename, ReadOnly:=True)
  Data = wb.Worksheets(1).Range("B3:E6").Value
  wb.Worksheets(1).Range("B3:E6").Copy
  ThisWorkbook.Activate
  ActiveSheet.Range(targetcellL, targetcellR).Select
    ActiveSheet.Paste
    Set targetcellL = targetcellL.Offset(4, 0)
    Set targetcellR = targetcellR.Offset(5, 0)
  Workbooks(Filename).Close
  Filename = Dir()
  Loop
  
  End Sub
  </code></pre>
  
  <p>Problem: I want that when the program as run as many time it should start pasting Data it the Range where it is pasting data for the first time.
  Following images will clear my problem more precisely.
  When the program run for the first time i get pasted data it below range which is what i want.</p>
  
  <p><a href="https://i.stack.imgur.com/lMn18.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lMn18.png" alt="First"></a> </p>
  
  <p>When Run for the 2nd time i get data it below range
  <a href="https://i.stack.imgur.com/8He8N.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8He8N.png" alt="second"></a>
  what should i do to make the behavior as such that when the program run for as many time the data should paste at the range where it's at first run see pic.</p>