D
D
DS282018-05-29 11:23:51
Visual Basic
DS28, 2018-05-29 11:23:51

How to speed up copying (VLOOKUP) of pictures in Excel (VBA)?

There are 100-1500 lines on the first sheet; the 15th column (O) contains the names that are repeated.
On the second sheet, the names are matched with pictures of the same size and format, in total 30-40 pieces.
The goal is to pull pictures into the first table (it will be updated by inserting data from outside).
There is a code that works on small selections, but freezes on full ones.
Any ideas how to speed up?

The code
Sub push()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
    Dim Row1 As Integer, Row2 As Integer, i As Integer
    Dim sh1 As Worksheet, sh2 As Worksheet
    
    Set sh1 = Sheets("Лист1")
    Set sh2 = Sheets("Лист2")
    Row1 = sh1.Cells(Rows.Count, 15).End(xlUp).Row
    Row2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row

    For i = 8 To Row1
        If sh1.Cells(i, 15).Value > 0 Then
            sh2.Range("A2:A" & Row2).Find(what:=sh1.Cells(i, 15).Value, LookIn:=xlValues).Offset(0, 1).Copy
            sh1.Activate
            sh1.Cells(i, 16).Select
            ActiveSheet.Pictures.Paste(Link:=True).Select
        End If
        Application.CutCopyMode = False
    Next i
    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

Maybe there is a more efficient way to pull up images? From a folder or something else?
Purpose: to collect pdf with pictures based on data from the first table.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question