VBA, Transferring of Data from one File to another I have this code that:

Clears my worksheet from row 12 and onward in Workbook A
Opens a new file Workbook B
Copys the sheet to my Workbook A. and Close Workbook B
Renames sheet in Workbook A
Transfers data from New Sheet to another Tab.

This seems to take me 10 minutes+, it is roughly 35k Rows.
Is there a faster way of doing this?
Thanks.
    Sub Mainsub()
    
    Worksheets(&quot;Main Data&quot;).Rows(&quot;12:&quot; &amp; Rows.Count).ClearContents
    
    'Copy data from the CM File to Template
    Application.ScreenUpdating = 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;))
    'Copy main data tab to EPM file workbook
    MainDataCM.Sheets(&quot;Main Data&quot;).Copy After:=ThisWorkbook.Sheets(1)
    'Close CM Comm file
    MainDataCM.Close SaveChanges:=False
    Application.ScreenUpdating = True
          
     
    
    Sheets(&quot;Main Data (2)&quot;).Name = &quot;CM_MainData&quot;
    
    Worksheets(&quot;CM_MainData&quot;).Visible = False
    
    'Read the CM_MainData tab and copy the required columns in the MainData tab
    Dim k As Long
    k = Sheets(&quot;CM_MainData&quot;).Range(&quot;A1&quot;, Sheets(&quot;CM_MainData&quot;).Range(&quot;A1&quot;).End(xlDown)).Rows.Count
    Debug.Print (k)
    
    i = 12
    j = 2
    
    While j &lt;= k
        Sheets(&quot;Main Data&quot;).Range(&quot;A&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;A&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;B&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;B&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;C&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;C&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;D&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;D&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;E&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;E&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;F&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;F&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;G&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;G&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;H&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;H&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;I&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;I&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;J&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;J&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;K&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;K&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;L&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;L&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;M&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;M&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;N&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;N&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;O&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;O&quot; &amp; j)
        Sheets(&quot;Main Data&quot;).Range(&quot;P&quot; &amp; i) = Sheets(&quot;CM_MainData&quot;).Range(&quot;P&quot; &amp; j)
    
        i = i + 1
        j = j + 1
    Wend
    
    Worksheets(&quot;Input&quot;).Activate
    Worksheets(&quot;Input&quot;).Select
    
    MsgBox &quot;Step 1 Completed&quot;

End Sub
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