Solved

Speed up copying of rows from one workbook to another

Posted on 2015-02-20
13
74 Views
Last Modified: 2016-02-11
Hi,

An expert recently provided the solution below which works perfectly except with the size of my dataset, its taking 17 minutes to execute. Is there anyway to speed it up considerably?

The code executes in a workbook (the Destination), opens another workbook (the Source) sheet1, copies all rows from the source where the date in column A of the source is greater than the latest (max) date in Column A sheet "Raw Data" of the destination (starting with the next blank row of any existing data)

Any help would be greatly appreciated!

Sub LoadData()
    Dim srcWb As Workbook
    Dim srcWs As Worksheet, destWs As Worksheet
    Dim maxDate As Date
    Dim i As Long, destCurrRow As Long
   
    Application.ScreenUpdating = False
    Set destWs = ActiveWorkbook.Sheets("Raw Data")
    Set srcWb = Workbooks.Open("C:\!Data\Book1.xlsx")
    Set srcWs = srcWb.Sheets("Sheet1")
   
    'Get Max Date
    maxDate = Application.WorksheetFunction.Max(destWs.Range("A:A"))
   
    destCurrRow = destWs.Range("A" & Rows.Count).End(xlUp).Row + 1

    For i = 2 To srcWs.Range("A" & Rows.Count).End(xlUp).Row
        If srcWs.Range("A" & i).Value > maxDate Then
            srcWs.Range("A" & i).EntireRow.Copy destWs.Range("A" & destCurrRow)
            destCurrRow = destCurrRow + 1
        End If
    Next i
   
    Application.DisplayAlerts = False  'avoids clipboard message
    srcWb.Close SaveChanges:=False
    Application.DisplayAlerts = True

    Worksheets("Table").Select
     
    Application.ScreenUpdating = True
    MsgBox "Data was successfully imported!", vbInformation
End Sub

Thanks in advance,

swjtx99
0
Comment
Question by:swjtx99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40622237
How about using a Filter to just show the rows with the desired date and then copy all the rows that are still visible (at once) to the new workbook?
0
 

Author Comment

by:swjtx99
ID: 40622253
Hi Martin,

Thanks for the reply. That is how I'm doing it now (manually) and wanted to automate it. I suppose if in VBA a filter could be applied to hide/delete all rows with a date in Column A of the Source workbook older than the latest date in the Destination and then copy everything over at once, that would also work.

Thanks,

swjtx99
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40622267
You could record a macro while you do it manually. You could also provide me cut down versions of the two workbooks and I can do it for you.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:Martin Liss
ID: 40622351
The following could be used as the starting point for the new macro. Right now it filters, copies and pastes from one sheet to another in the same workbook and that and the filter date would need to be changed.

Sub OneFellSwoop()

Dim rng As Range
Dim lngVisibleCount As Long

Set rng = Range("A1:A" & ActiveSheet.UsedRange.Rows.Count)
' Clear any existing filter
rng.AutoFilter

With rng
    ' Filter out everything that doesn't have 2/20/2015 as the date in coulmn 1
    .AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(1, "2/20/2015")
    ' Copy the visible rows
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy
End With

' Get a count of the visible rows
lngVisibleCount = Cells(1).CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count

With Sheets("sheet2")
    .Select
    ' select the number of rows as the destination for the paste
    .Range("A1:A" & lngVisibleCount).Select
    .Paste
End With

Application.CutCopyMode = False
Selection.AutoFilter
End Sub

Open in new window

0
 

Author Comment

by:swjtx99
ID: 40622359
Hi Martin,

Thank you. I'll see if I can work it out based on your starting point. If not, I'll post cut down versions of the files.

Sincerely,

swjtx99
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40622888
Set calculation setting to manual in the beginning, if not needed.
Application.Calculation = xlCalculationManual

Open in new window

And set back again to automatic, if needed.
Application.Calculation = xlCalculationAutomatic

Open in new window

Excel tries to calculate all workbooks opened in the same application instance even when a single cell value changes.
0
 

Author Comment

by:swjtx99
ID: 40623134
Hi Hakan,

Thanks. I did have that in the working version but it was of little help.

I've attached a cut version of the two workbooks. The "Destination Workbook.xls" contains the macro I'm using now. The "testdata.xls" is the workbook that contains the rows I want to copy over to the destination (only those that have a date in column A after the max date found in column A of the Destination workbook).

If you put both of these into a folder named c:\!data and run it you can observe how slow it is and again, these are smaller versions of the actual files.

Thanks,

swjtx99
Destination-Workbook.xls
testdata.xls
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40623480
Use this instead of copying.
destWs.Range("A" & destCurrRow).EntireRow.Value = srcWs.Range("A" & i).EntireRow.Value

Open in new window

Or this, simpler.
destWs.Rows(destCurrRow).Value = srcWs.Rows(i).Value

Open in new window

Or this may even be faster
destWs.Rows(destCurrRow).Value2 = srcWs.Rows(i).Value2

Open in new window

And then format new rows if needed.
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 40623511
Or sort the source data in ascending date order, find the date > maxDate, and copy the entire range from that point down at once.
See attached.
Destination-Workbook.xls
0
 

Author Closing Comment

by:swjtx99
ID: 40624687
Thanks to all who responded for your time.

Eigel, this solution worked very well and very fast. Even on the production workbook with over 50K rows, it only took a few seconds. Exactly what I needed.

Very much appreciate your help.

Regards,

swjtx99
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40625109
Again in Ejgil Hedegaard's code, use this instead of copying cells.
    If i <= sourceMaxRow Then
        destWs.Range(destWs.Cells(destCurrRow, 1), destWs.Cells(destCurrRow + sourceMaxRow - i, sourceMaxColumn)).Value2 = _
        srcWs.Range(Cells(i, 1), Cells(sourceMaxRow, sourceMaxColumn)).Value2
    End If

Open in new window

0
 

Author Comment

by:swjtx99
ID: 40627015
Hi Hakan,

Thanks for the additional info. What does this do? Does this speed up the execution?

Regards,

swjtx99
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40627453
Of course. This only copies values without formats. You may also modify Ejgil's code to paste values only.
But i prefer direct setting of cell values; you don't have to switch sheet or select a cell before.
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question