selection.copy leads to selection.pastespecial not working. excel VBA

First, a relative welcome to SO!

Second, some tips for you that will make life easier in VBA programming:

1. Use Option Explicit and always Dimension and Declare your variable types.
2. When naming variables, make them easy to understand and follow. So, if you are going to create a worksheet variable, call it something like wksCopy. Or, if you are going to name a workbook, call it wkbCopyTo
3. You don't need to use .Select and .Activate, but rather you can work directly with your objects. Also, by declaring the appropriate variables types, this make it much easier to work with these objects in your code each time you need them.
4. I don't know if you are running this code inside Excel, or another application (like Access), but if you are in Excel, there is no need to create an Excel object, as you can work with the Excel App directly. Ignore this if you are using Access / Word / PPT etc to fire the code.

All these tips make your code much easier to read and understand and follow when trying to debug, and write.

All that said, I have refactored your code above to incorporate most of these principles (I kept all your variable names intact so you wouldn't get lost in any re-namings.) If this re-write doesn't directly solve your problem = which it may not, because the code is kind of confusing to me as written, I think it will be much easier for you to follow and understand and find out where it's not doing what you expect when you debug. Also, I think it will help us help you if you can't figure it out.

Sub parse() Dim strPath As String, strPathused As String Dim objexcel As Excel.Application Set objexcel = CreateObject("Excel.Application") With objexcel .Visible = True .DisplayAlerts = False End With strPath = "C:\prodplan" Dim objfso As FileSystemObject, objFolder As Folder 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 Dim objWorkbook As Excel.Workbook Set objWorkbook = objexcel.Workbooks.Open(objfile.Path) ' Set path for move to at end of script strPathused = "C:\prodplan\used\" & objWorkbook.Name 'open WB to consolidate too objexcel.Workbooks.Open "C:\prodplan\compiled\plancon.xlsx" 'Range management sourcebook Dim SRCwb As Excel.Worksheet, SRCrange1 As Excel.Range, SRCrange2 As Excel.Range Set SRCwb = objWorkbook.Worksheets("plan") 'sjh -> to me wb implies wb, but you set it to a worksheet (could be a style thing, but worth pointing out Set SRCrange1 = objWorkbook.Worksheets("plan").Range("b6:i7") Set SRCrange2 = objWorkbook.Worksheets("plan").Range("k6:p7") 'Range management sourcebook Set DSTwb = Excel.Worksheet Set DSTwb = Workbooks("plancon.xlsx").Worksheets("data") 'start header dates and shifts copy from objworkbook to consolidated WB Dim MyColumn As String Dim Here As String Dim AC As Variant Here = DSTwb.Range("B2").Address MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2) 'sjh comment -> best to change "ActiveWorkbook" to the workbook referece you mean, like objWorkbook Dim lastrow As Range Set lastrow = ActiveWorkbook.ActiveSheet.Range(MyColumn & "65536").End(xlUp).Offset(1, 0) SRCrange1.Copy lastrow.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True Here = Workbooks("plancon.xlsx").Worksheets("sheet1").Range("B2").Address MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2) 'sjh comment -> best to change "ActiveWorkbook" to the workbook referece you mean, like objWorkbook Set lastrow = ActiveWorkbook.ActiveSheet.Range(MyColumn & "65536").End(xlUp).Offset(1, 0) SRCrange2.Copy lastrow.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True 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 

UPDATE If you are running this all in Excel. Just use this code below. I left both codes in my answer, in case you are not running this from Excel.

Option Explicit Sub parse() Application.DisplayAlerts = False Dim strPath As String, strPathused As String strPath = "C:\prodplan" Dim objfso As FileSystemObject, objFolder As Folder 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 Dim objWorkbook As Workbook Set objWorkbook = Workbooks.Open(objfile.Path) ' Set path for move to at end of script strPathused = "C:\prodplan\used\" & objWorkbook.Name 'open WB to consolidate too Workbooks.Open "C:\prodplan\compiled\plancon.xlsx" 'Range management sourcebook Dim SRCwb As Worksheet, SRCrange1 As Range, SRCrange2 As Range Set SRCwb = objWorkbook.Worksheets("plan") Set SRCrange1 = SRCwb.Range("b6:i7") Set SRCrange2 = SRCwb.Range("k6:p7") 'Range management sourcebook Dim DSTwb As Worksheet Set DSTwb = Workbooks("plancon.xlsx").Worksheets("data") 'start header dates and shifts copy from objworkbook to consolidated WB Dim MyColumn As String Dim Here As String Dim AC As Variant Here = DSTwb.Range("B2").Address MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2) 'sjh comment -> best to change "ActiveWorkbook" to the workbook referece you mean, like objWorkbook or the other workbook you have open Dim lastrow As Range Set lastrow = ActiveWorkbook.ActiveSheet.Range(MyColumn & "65536").End(xlUp).Offset(1, 0) SRCrange1.Copy lastrow.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True Here = Workbooks("plancon.xlsx").Worksheets("sheet1").Range("B2").Address MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2) 'sjh comment -> best to change "ActiveWorkbook" to the workbook referece you mean, like objWorkbook or the other workbook you have open Set lastrow = ActiveWorkbook.ActiveSheet.Range(MyColumn & "65536").End(xlUp).Offset(1, 0) SRCrange2.Copy lastrow.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True 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 End Sub