Pull Data from another Excel File

oHoi99

New Member
Joined
Dec 20, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
Hi,
I need to pull some data from different columns to another excel file. The following is my code:

VBA 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):

VBA Code:
    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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,550
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Dec 20, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,550
Office Version
  1. 2016
Platform
  1. Windows
Thanks!
But what if I want to use PasteSpecial? Only the values and number formats
Maybe something like this?

VBA Code:
    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

New Member
Joined
Dec 20, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
Maybe something like this?

VBA Code:
    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 :)
 

Forum statistics

Threads
1,141,203
Messages
5,704,941
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top