code really slow when copy range frome file to another

Maklil

Board Regular
Joined
Jun 23, 2022
Messages
140
Office Version
  1. 2019
Platform
  1. Windows
Hi
I got this code from another web , but it seems slow after I modified based on my needs .
so my goal copy data from file report for sheets(TABLE1) for range B4:D to file BS for sheet MATCH for range F2:I
and I want changing this line
VBA Code:
            .Range("B4:D54").Copy
to
VBA Code:
            .Range("B4:D" & lr).Copy


to make dynamically without specifying end of range
and when copy data to file BS for sheet MATCH for range F2:I I want skiping column G and move next .I mean column B=F and columns C:D = columns H2:I just keep the column G is empty because I need it to auto fill ID .


VBA Code:
Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, file2WS As Worksheet, file3WS As Worksheet
    Set file2WS = Workbooks("report.xlsx").Sheets("Table1")
    Set file3WS = Workbooks("BS.xlsm").Sheets("MATCH")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("B4:D" & LastRow)
        With file2WS
            .Range("B4") = rng
            .Range("B4:D54").Copy
            file3WS.Cells(file3WS.Rows.Count, "F").End(xlUp).Offset(1, 1).PasteSpecial xlPasteValue
        End With
    Next rng
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,214,649
Messages
6,120,732
Members
448,987
Latest member
marion_davis

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
Back
Top