Search code examples
excelvbadashboardworksheet-function

Copy cell between worksheet using VBA

I have a dashboard copy from my company website and I'm trying to copy the email raw data into my editted worksheet, but it's still like a mess: It's kinda like this:

[enter image description here

This sheet name "Sheet3" and the other one is "Sheet1". They all have the same Tracking# number:

enter image description here

Sub CopyRow()

Dim I As Variant
I = Sheet1.Cells(Sheet1.Rows.Count, 6).End(xlUp).Row`
If Not Intersect(Target, Worksheets("Sheet3").Columns("E:E")) Is Nothing Then
     If InStr(UCase(Target.Value), "@") > 0 Then
         If InStr(UCase(Worksheets("Sheet3").Columns("B:B")), "L") = Worksheets("Sheet1").Columns("B:B") Then
         Target = I

   End If
End If

End Sub
Solution

  • I usually do something like this, assuming sheet1 is the sheet to be updated by data from sheet3;

    dim destLength as Long 'rows used in sheet 1
dim sourceLength as Long 'rows used in sheet 3

    Open directory with source files and loop through each file and do the following

    destLength = Sheet1.Range("A"&Rows.Count).End(xlUp).Row
sourceLength = Sheet3.Range("A"&Rows.Count).End(xlUp).Row
Sheet3.Range("B1" & ":I" & sourceLen).copy
Sheet1.Range("A" & destLen + 1).pasteSpecial xlValues
Application.CutCopyMode = False