Copy and paste is very slow VBA

haroon2015 · Dec 8th 2016

hi,

i am using this code to copy data and paste at other workbook,
but it is very slow.
need help

Code

Sub ExcelToTally_V2()Dim r As Range
Set r = Sheet15.Range("D3")
If r.Value > 0 Then
Dim sh1 As Worksheet
Set sh1 = ActiveWorkbook.Worksheets("V2")
Windows("Vouchers - Purchase Transactions With StockItems.xlsm").Activate
On Error Resume Next
With sh1
   .Range("C5:C54").SpecialCells(xlCellTypeVisible).Copy
   Range("D" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("E5:E54").SpecialCells(xlCellTypeVisible).Copy
   Range("F" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("F5:F54").SpecialCells(xlCellTypeVisible).Copy
   Range("H" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("G5:G54").SpecialCells(xlCellTypeVisible).Copy
   Range("J" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("H5:H54").SpecialCells(xlCellTypeVisible).Copy
   Range("E" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("I5:I54").SpecialCells(xlCellTypeVisible).Copy
   Range("C" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End With
Else
Exit Sub
End If

Display More

shknbk2 · Dec 9th 2016

Re: Copy and paste is very slow VBA

Try this and see if it is any faster. It may not be, but give it a shot. I haven't tested it because I don't have a workbook set up the same way, but something similar worked on one of my workbooks.

Code

Sub ExcelToTally_V2()
    Dim r As Range
    Dim rTemp(6) As Range
    
    Set r = Sheet15.Range("D3")
    If r.Value > 0 Then
        Dim sh1 As Worksheet
        Set sh1 = ActiveWorkbook.Worksheets("V2")
        Windows("Vouchers - Purchase Transactions With StockItems.xlsm").Activate
        On Error Resume Next
        With sh1
            Set rTemp(0) = .Range("C5:C54").SpecialCells(xlCellTypeVisible)
            Set rTemp(1) = .Range("E5:E54").SpecialCells(xlCellTypeVisible)
            Set rTemp(2) = .Range("F5:F54").SpecialCells(xlCellTypeVisible)
            Set rTemp(3) = .Range("G5:G54").SpecialCells(xlCellTypeVisible)
            Set rTemp(4) = .Range("H5:H54").SpecialCells(xlCellTypeVisible)
            Set rTemp(5) = .Range("I5:I54").SpecialCells(xlCellTypeVisible)
            pasteValues rTemp(0), "D"
            pasteValues rTemp(1), "F"
            pasteValues rTemp(2), "H"
            pasteValues rTemp(3), "J"
            pasteValues rTemp(4), "E"
            pasteValues rTemp(5), "C"
        End With
    Else
        Exit Sub
    End If
End Sub




Sub pasteValues(r As Range, s As String)
    Dim i As Integer, c As Range, destination As Range
    i = 0
    Set destination = Range(r & Rows.Count).End(xlUp).Offset(1, 0)
    
    For Each c In r
        destination.Offset(i, 0).Value = c.Value
        i = i + 1
    Next
End Sub

Display More

haroon2015 · Dec 10th 2016

Re: Copy and paste is very slow VBA

not working

bryce · Dec 10th 2016

Re: Copy and paste is very slow VBA

Not working? Well good luck to you friend. How about something like: Thanks for trying, when I ran it this is what I saw and compared to my original here is the performance.

Try to remember you came here for help.

**KjBox** · Dec 10th 2016

Re: Copy and paste is very slow VBA

Try this.

Your code is slow because it switches between the 2 workbooks 6 times as well as copy/pasting 6 times. This code will place the values to be transferred into 6 arrays, then put those arrays onto the other workbook, no copy/pasting and only 1 workbook switch.

Code

Sub ExcelToTally_V2()
    Dim x1, x2, x3, x4, x5, x6
    
    If sheet15.[d3] > 0 Then
        With Sheets("V2").[c5:i54]
            x1 = .Columns(1).SpecialCells(12)
            x2 = .Columns(3).SpecialCells(12)
            x3 = .Columns(4).SpecialCells(12)
            x4 = .Columns(5).SpecialCells(12)
            x5 = .Columns(6).SpecialCells(12)
            x6 = .Columns(7).SpecialCells(12)
        End With
        With Windows("Vouchers - Purchase Transactions With StockItems.xlsm")
            .Activate
            .Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize(UBound(x1, 1)) = x1
            .Cells(.Rows.Count, 6).End(xlUp).Offset(1).Resize(UBound(x2, 1)) = x2
            .Cells(.Rows.Count, 8).End(xlUp).Offset(1).Resize(UBound(x3, 1)) = x3
            .Cells(.Rows.Count, 10).End(xlUp).Offset(1).Resize(UBound(x4, 1)) = x4
            .Cells(.Rows.Count, 5).End(xlUp).Offset(1).Resize(UBound(x5, 1)) = x5
            .Cells(.Rows.Count, 3).End(xlUp).Offset(1).Resize(UBound(x6, 1)) = x6
        End With
    Else
        Exit Sub
    End If
    
End Sub

Display More

haroon2015 · Dec 10th 2016

Re: Copy and paste is very slow VBA

giving an error 438
object doesn't supoort property
at below line

Code

[COLOR=#333333].Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize([/COLOR][COLOR=blue]UBound[/COLOR][COLOR=#333333](x1, 1)) = x1[/COLOR]

**KjBox** · Dec 10th 2016

Re: Copy and paste is very slow VBA

Sorry wrote that code on the fly and forgot to specify the sheet in the 2nd workbook, try this version.

You will need to change the sheet name in the code if it is not named "Sheet1"

Code

Sub ExcelToTally_V2()
    Dim x1, x2, x3, x4, x5, x6
     
    If sheet15.[d3] > 0 Then
        With Sheets("V2").[c5:i54].SpecialCells(12)
            x1 = .Columns(1): x2 = .Columns(3)
            x3 = .Columns(4): x4 = .Columns(5)
            x5 = .Columns(6): x6 = .Columns(7)
        End With
        Windows("Vouchers - Purchase Transactions With StockItems.xlsm").Activate
        With ActiveWorkbook.Sheets("Sheet1")    '//Change sheet name to suit
            .Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize(UBound(x1, 1)) = x1
            .Cells(.Rows.Count, 6).End(xlUp).Offset(1).Resize(UBound(x2, 1)) = x2
            .Cells(.Rows.Count, 8).End(xlUp).Offset(1).Resize(UBound(x3, 1)) = x3
            .Cells(.Rows.Count, 10).End(xlUp).Offset(1).Resize(UBound(x4, 1)) = x4
            .Cells(.Rows.Count, 5).End(xlUp).Offset(1).Resize(UBound(x5, 1)) = x5
            .Cells(.Rows.Count, 3).End(xlUp).Offset(1).Resize(UBound(x6, 1)) = x6
        End With
        Windows(ThisWorkbook.Name).Activate '// This will make the 1st. workbook active again
    Else
        Exit Sub
    End If
     
End Sub

Display More

haroon2015 · Dec 10th 2016

Re: Copy and paste is very slow VBA

@KjBox
THANX A LOT

I CHANGED THESE ONES

Code

[/I][COLOR=#417394]        End With[/COLOR][COLOR=#417394]        Exit Sub[/COLOR][COLOR=#417394]End With[/COLOR][COLOR=#417394]End If[/COLOR][COLOR=#417394]End Sub[/COLOR][I]

NOW ITS WORKING PERFECTLY AS I WANTED[/I]

haroon2015 · Dec 10th 2016

Re: Copy and paste is very slow VBA

why i am not able to add reputation you u
@KjBox

haroon2015 · Dec 10th 2016

Re: Copy and paste is very slow VBA

KjBox
in below code i am having same problem, will u look plz this one here or i start a new thread?

Code

Sub OLV_FORMULA_COPY()Sheets("OL V").Activate
Application.ScreenUpdating = False
Dim i, j, count
count = WorksheetFunction.CountIf(Sheets("OL V").Range("W4:W53"), ">0") - 1




i = 47
For j = 1 To count
    Range("A4:R46").Copy
    Range("A" & i).PasteSpecial Paste:=xlPasteAll
    i = i + 43
Next j
Application.ScreenUpdating = True
End Sub

Display More

**KjBox** · Dec 10th 2016

Re: Copy and paste is very slow VBA

Since this is a different question referring to a different, even though similar, issue it should be asked in a new thread.

Copy and paste is very slow VBA

Participate now!

Share

Users Viewing This Thread