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
to
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 .
thanks
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
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