VBA, Transferring of Data from one File to another
Seems like instead of transferring, Copying the Data and using ThisWorkbook was able to get the job done easily.
Sub Mainsubtwo()

Call Reset
Worksheets(&quot;Main Data&quot;).Rows(&quot;12:&quot; &amp; Rows.Count).ClearContents


Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Open CM file
Set MainDataCM = Workbooks.Open(Sheets(&quot;Input&quot;).Range(&quot;B16&quot;) &amp; Sheets(&quot;Input&quot;).Range(&quot;B19&quot;))

With Sheets(&quot;Main Data&quot;)
lastrow = Sheets(&quot;Main Data&quot;).Range(&quot;A&quot; &amp; .Rows.Count).End(xlUp).Row
Sheets(&quot;Main Data&quot;).Range(&quot;A2:P&quot; &amp; lastrow).Copy
End With
'Paste into this workbook
ThisWorkbook.Sheets(&quot;Main Data&quot;).Range(&quot;A12&quot;).PasteSpecial xlPasteValues


'Close CM file
MainDataCM.Close SaveChanges:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True

Worksheets(&quot;Input&quot;).Activate
Worksheets(&quot;Input&quot;).Select

MsgBox &quot;Step 1 Completed&quot;


End Sub