title: VBA Copy data from one sheet to another
body: <p>I'm pretty new to VBA and need some help with a project. I need to write a macro that reads the Sheet Name in Column C, and pastes the values from a source workbook to a range in a target workbook, which is specified in Column D. </p>
  
  <p>So for example, it needs to copy the data in Sheet2 of Myworkbook book, and paste it into range of Theirworkbook Sheet2. The place where the range and sheet number information is stored in a separate workbook. </p>
  
  <p>Edit: I've added a picture of what wbOpen looks like. <a href="http://i.stack.imgur.com/psfeD.png" rel="nofollow">This is it here.</a>
  <div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>Option Explicit
  
  Sub PasteToTargetRange()
  
      Dim arrVar As Variant 'stores all the sheets to get the copied
      Dim arrVarTarget As Variant 'stores names of sheets in target workbook
      Dim rngRange As Range 'each sheet name in the given range
      Dim rngLoop As Range    'Range that rngRange is based in
      Dim wsSource As Worksheet 'source worksheet where ranges are found
      Dim wbSource As Workbook    'workbook with the information to paste
      Dim wbTarget As Workbook    'workbook that will receive information
      Dim strSourceFile As String 'location of source workbook
      Dim strTargetFile As String 'location of source workbook
      Dim wbOpen As Workbook  'Current open workbook(one with inputs)
      Dim wsRange As Range 'get information from source workbook
      Dim varRange As Range   'Range where values should be pasted
      Dim i As Integer 'counter for For Loop
      Dim wbkNewSheet As Worksheet 'create new worksheet if target workbook doesn't have
      Dim wsTarget As Worksheet 'target workbook worksheet
      Dim varNumber As String 'range to post
      
      
      
      Set wbOpen = Workbooks.Open("WorkbookWithRanges.xlsx")
      
      'Open source file
      MsgBox ("Open the source file")
      strSourceFile = Application.GetOpenFilename
         If strSourceFile = "" Then Exit Sub
         Set wbSource = Workbooks.Open(strSourceFile)
         
      'Open target file
      MsgBox ("Open the target file")
      strTargetFile = Application.GetOpenFilename
         If strTargetFile = "" Then Exit Sub
          Set wbTarget = Workbooks.Open(strTargetFile)
      
      'Activate transfer Workbook
      wbOpen.Activate
      
  
      Set wsRange = ActiveSheet.Range("C9:C20")
      
      Set arrVarTarget = wbTarget.Worksheets
      
          
      For Each varRange In wsRange
          If varRange.Value = 'Target workbook worksheets
              varNumber = varRange.Offset(0, -1).Value
              Set wsTarget = X.Offset(0, 1)
              
              wsSouce.Range(wsTarget).Value = varNumber
          Else
              wbkNewSheet = Worksheets.Add
              wbkNewSheet.Name = varRange.Value
        End If
      Next
          
      
  End Sub</code></pre>
  </div>
  </div>
  </p>