title: selection.copy leads to selection.pastespecial not working. excel VBA
body: <p>I will keep this quick. The attached code for the most part works i have used slight variations of it on other projects. the commented out range3.copy is from my last project.</p>
  
  <p>I am currently having issues getting selection.copy to copy the selected range in the correct workbook. I have tried many things some are noted in the script. but I can not get the selection.copy to work 
  .range.copy will work and populate the clipboard. But I have not figured out how to pastespecial using .copy.</p>
  
  <p>I tried outputting to variable .. didn't work as i thought it might. I feel I have to be missing something on the workbook selection/activation but I don't know what. Thanks in advance for any advice or assistance .. I will continue plugging away and see if I can figure it out.</p>
  
  <p><strong>Here is the first segment with the issue.  SRCrange1.select then selection.copy does not actually copy the designated selection. The full code is below</strong>.</p>
  
  <pre><code>      Dim MyColumn As String
      Dim Here As String
      Dim AC As Variant
  
       'SRCrange1.copy  ': This will copy to clipboard
  
         'objworkbook.Worksheets("plan").Range("b6:h7").Select  no change from SRCrange1.select
         'SRCrange1.Select 'the range does select
          'Selection.copy  '  this will cause a activecell in DSTwb _
          to be copied neither direct reference to SRCrange1.select or .avtivate will change that.
  
  
  DSTwb.Select
               DSTwb.Range("b2").Select
               Here = ActiveCell.Address
               MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2)
               Set lastrow = ActiveWorkbook.ActiveSheet.Range(MyColumn &amp; "65536").End(xlUp).Offset(1, 0)
               lastrow.Select
               Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True
  </code></pre>
  
  <p><strong>FULL CODE</strong> </p>
  
  <pre><code>Sub parse()
  Dim strPath As String
  Dim strPathused As String
  
  
  'On Error Resume Next
  
  
  Set objexcel = CreateObject("Excel.Application")
  objexcel.Visible = True
  objexcel.DisplayAlerts = False
  strPath = "C:\prodplan"
  Set objfso = CreateObject("Scripting.FileSystemObject")
  Set objFolder = objfso.GetFolder(strPath)
  
  
  'Loop through objWorkBooks
  For Each objfile In objFolder.Files
  
      If objfso.GetExtensionName(objfile.Path) = "xlsx" Then
          Set objworkbook = objexcel.Workbooks.Open(objfile.Path)
                                  ' Set path for move to at end of script
                                  strPathused = "C:\prodplan\used\" &amp; objworkbook.Name
  
  'open WB to consolidate too
                          Workbooks.Open "C:\prodplan\compiled\plancon.xlsx"
  
  'Range management sourcebook
          Set SRCwb = objworkbook.Worksheets("plan")
          Set SRCrange1 = objworkbook.Worksheets("plan").Range("b6:i7")
          Set SRCrange2 = objworkbook.Worksheets("plan").Range("k6:p7")
          'Set SRCrange3 = objworkbook.Worksheets("").Range("")
  
  'Range management sourcebook
          Set DSTwb = Workbooks("plancon.xlsx").Worksheets("data")
          'Set DSTrange1 = Workbooks("plancon.xlsx").Worksheets("data").Range("")
          'Set DSTrange2 = Workbooks("plancon.xlsx").Worksheets("data").Range("")
          'Set DSTrange3 = Workbooks("plancon.xlsx").Worksheets("data").Range("")
  
  'start header dates and shifts copy from objworkbook to consolidated WB
                  SRCwb.Select
                  'On Error Resume Next
                  'SRCwb.Cells.UnMerge
  
  Dim MyColumn As String
  Dim Here As String
  Dim AC As Variant
  
   'SRCrange1.copy  ': This will copy to clipboard
  
     'objworkbook.Worksheets("plan").Range("b6:h7").Select  no change from SRCrange1.select
     'SRCrange1.Select 'the range does select
      'Selection.copy  '  this will cause a activecell in DSTwb _
      to be copied neither direct reference to SRCrange1.select or .avtivate will change that.
           DSTwb.Select
           DSTwb.Range("b2").Select
           Here = ActiveCell.Address
           MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2)
           Set lastrow = ActiveWorkbook.ActiveSheet.Range(MyColumn &amp; "65536").End(xlUp).Offset(1, 0)
           lastrow.Select
           Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True
  
  
     SRCrange2.Select
      Selection.copy
           Workbooks("plancon.xlsx").Worksheets("sheet1").Select
           ActiveSheet.Range("b2").Select
           ActiveSheet.Range("b2").Activate
           Here = ActiveCell.Address
           MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2)
           Set lastrow = ActiveWorkbook.ActiveSheet.Range(MyColumn &amp; "65536").End(xlUp).Offset(1, 0)
           lastrow.Select
           Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True
  
  '    range3.copy
  '         Workbooks("data.xlsx").Worksheets("sheet1").Activate
  '         ActiveSheet.Range("c2").Select
  '         ActiveSheet.Range("c2").Activate
  '         Here = ActiveCell.Address
  '         MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2)
  '         Set lastrow = ActiveWorkbook.ActiveSheet.Range(MyColumn &amp; "65536").End(xlUp).Offset(1, 0)
  '         ActiveSheet.Paste Destination:=lastrow
  
  
                      'start loop for objworkbook name copy to field in plancon corisponding with date/shift and copy/paste select row data.
  
      objworkbook.Close False
                          'Move proccesed file to new Dir
  
      OldFilePath = objfile 'original file location
          NewFilePath = strPathused ' new file location
              Name OldFilePath As NewFilePath ' move the file
  
  End If
  
  Next
  
  objexcel.Quit
  
  
  
  
  End Sub
  </code></pre>