Pull Data from another Excel File

[oHoi99]

Hi,
I need to pull some data from different columns to another excel file. The following is my code:

    Sub TestPull()

    Workbooks("Database.xlsm").Worksheets("Overall").Activate
    Range("E5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy _
    Workbooks("Framework.xlsm").Worksheets("NEW").Range("A5")
    Application.CutCopyMode = False

    Workbooks("Database.xlsm").Worksheets("Overall").Activate
    Range("G5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy _
    Workbooks("Framework.xlsm").Worksheets("NEW").Range("B5")
    Application.CutCopyMode = False

    End Sub

The code works perfectly fine, just that I have a lot of data to pull (the above are just 2 of the data to be pulled, therefore when I run the code, it will keep activating Database file every time when it pulls from a new column, which makes my laptop to slow down, and even worse, Excel will crash. So I am wondering if there is any way to stop activating the Database file for each time?

I have tried doing the code shown below, but it gives me an error saying Select Method of range class failed (highlighting the first line):

    Workbooks("Database.xlsm").Worksheets("Overall").Range("E5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy _
    Workbooks("Framework.xlsm").Worksheets("NEW").Range("A5")
    Application.CutCopyMode = False

Hope to receive help as soon as possible

 

[Zot]

You do not need to activate or select but can just write like formula

This is not tested but just on top of my head. I defined workbook and worksheet to simplify writing.

Sub TestPull()
    
    Dim wsOverall As Worksheet, wsNew As Worksheet
    Dim wbDB As Workbook, wbFrameW As Workbook
    
    Set wbDB = Workbooks("Database.xlsm")
    Set wbFrameW = Workbooks("Framework.xlsm")
    Set wsOverall = wbDB.Sheets("Overall")
    Set wsNew = wbFrameW.Sheets("NEW")
    
    wsOverall.Range("E5", "E" & wsOverall.Range("E5").End(xlDown).Row).Copy wsNew.Range("A5")
    wsOverall.Range("G5", "G" & wsOverall.Range("G5").End(xlDown).Row).Copy wsNew.Range("B5")
    
    Application.CutCopyMode = False
    
End Sub

 

[oHoi99]

You do not need to activate or select but can just write like formula

This is not tested but just on top of my head. I defined workbook and worksheet to simplify writing.

Sub TestPull()
   
    Dim wsOverall As Worksheet, wsNew As Worksheet
    Dim wbDB As Workbook, wbFrameW As Workbook
   
    Set wbDB = Workbooks("Database.xlsm")
    Set wbFrameW = Workbooks("Framework.xlsm")
    Set wsOverall = wbDB.Sheets("Overall")
    Set wsNew = wbFrameW.Sheets("NEW")
   
    wsOverall.Range("E5", "E" & wsOverall.Range("E5").End(xlDown).Row).Copy wsNew.Range("A5")
    wsOverall.Range("G5", "G" & wsOverall.Range("G5").End(xlDown).Row).Copy wsNew.Range("B5")
   
    Application.CutCopyMode = False
   
End Sub

Thanks!
But what if I want to use PasteSpecial? Only the values and number formats

 

[Zot]

Thanks!
But what if I want to use PasteSpecial? Only the values and number formats

Maybe something like this?

    wsOverall.Range("E5", "E" & wsOverall.Range("E5").End(xlDown).Row).Copy
    wsNew.Range("A5").PasteSpecial (xlPasteValuesAndNumberFormats)
    wsOverall.Range("G5", "G" & wsOverall.Range("G5").End(xlDown).Row).Copy
    wsNew.Range("B5").PasteSpecial (xlPasteValuesAndNumberFormats)

 

[oHoi99]

Maybe something like this?

    wsOverall.Range("E5", "E" & wsOverall.Range("E5").End(xlDown).Row).Copy
    wsNew.Range("A5").PasteSpecial (xlPasteValuesAndNumberFormats)
    wsOverall.Range("G5", "G" & wsOverall.Range("G5").End(xlDown).Row).Copy
    wsNew.Range("B5").PasteSpecial (xlPasteValuesAndNumberFormats)

Thanks! Your help is very much appreciated

 

[Zot]

Thanks! Your help is very much appreciated

You're welcome