Copy Data from Sheet to Sheet

[NeoSez]

My Aswer Is This, the problem has been copied to this thread now.

The names of the sheets are SHEET2 & SHEET1.
SHEET1 is the main sheet that is updated daily. All data begins on row 5.
SHEET2 is the sheet that changes daily and gets deleted after all updates have been copied to SHEET1.

I would like to copy the full contents from SHEET2 column T to SHEET1 column I. The contents do not have to be deleted. Sheet 2 will be deleted after copy is completed, as there will be a new Sheet 2 every day.

The value of SHEET1 Column A, has a unique identifier number for every row, such as 000001, 000002, etc... which is also on SHEET2 Column F. So the contents will be copied based on this unique identifier number, replacing the current cell contents in SHEET1 column I.

I hope I provided enough information for you now. Thank you so much for your help.

 

[My Aswer Is This]

Well I was hoping someone else here on the forum would answer this question but:
So how many rows of data are we talking about?
Is it like 100 rows or 10,000 rows.
The reason I ask is the script will have to search every row on sheet2 column F for the unique identifier found on sheet1 column A

Which if we are dealing with 10,000 rows this may take a while.
But then who knows maybe someone else will jump in here and have a answer
But I will keep monitoring this thread till we get a answer. Or I provide a answer.
Could we not just replace all the data on sheet1 with all the data on sheet2?

 

[NeoSez]

Well I was hoping someone else here on the forum would answer this question but:
So how many rows of data are we talking about?
Is it like 100 rows or 10,000 rows.
The reason I ask is the script will have to search every row on sheet2 column F for the unique identifier found on sheet1 column A

Which if we are dealing with 10,000 rows this may take a while.
But then who knows maybe someone else will jump in here and have a answer
But I will keep monitoring this thread till we get a answer. Or I provide a answer.
Could we not just replace all the data on sheet1 with all the data on sheet2?

The number of rows varies. It will change daily as new rows of data are added all the time. Can we search it to last found row? No, we can not replace for all the rows sadly, as it contains comments on some of rows.
You can provide what you have and see if anyone has any suggestions for change or addition. I am too new to provide any sound advice.
I really appreciate your "helpMy Aswer Is This" .

 

[My Aswer Is This]

The number of rows varies. It will change daily as new rows of data are added all the time. Can we search it to last found row? No, we can not replace for all the rows sadly, as it contains comments on some of rows.
You can provide what you have and see if anyone has any suggestions for change or addition. I am too new to provide any sound advice.
I really appreciate your "helpMy Aswer Is This" .

I know the number of rows may very. But do they very from 100 to 300 or from 30,000 to 100,000
See if we are dealing with extremely large numbers of rows the script may need to be different then if we are only dealing with a 100 rows

 

[TotallyConfused]

Hello NeoSez
I hope this is what you are looking for.

Sub LoadColumnsIntoArrayThenCompare()

   Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
   Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")

   Dim Ary1 As Variant        ' Array containing Sheet #1 columns 'A' and 'F'
   Dim Ary2 As Variant        ' Array containing Sheet #2 columns 'F' and 'T'

   Dim Ary1rows As Long       ' Loop counter for number of rows in Ary1
   Dim Ary2rows As Long       ' Loop counter for number of rows in Ary2
   Dim Cols As String         ' Specifies which columns are to be loaded into each array
   Dim LastRow As Long        ' Specifies last row of data from each sheet to be loaded into each array
   Dim NumRows As Long        ' Number of rows of data in Sheet #1 column 'A'

Application.ScreenUpdating = False   ' Turn off screen update

With ws1
     Cols = "1,6"                 ' Specify columns 'A' and 'F' to be loaded into Ary1
     LastRow = .Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row    ' Row number of last data in Sheet #1
     Ary1 = Application.Index(.Cells, Evaluate("ROW(5:" & LastRow & ")"), Split(Cols, ","))       ' Data starts in row #5
     NumRows = Range("A" & Rows.Count).End(xlUp).Row           ' Calculate number of data rows in Sheet #1 column 'A'
End With

With ws2
      Cols = "6,20"               ' Specify columns 'F' and 'T' to be loaded into Ary2
      LastRow = .Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row     ' Row number of last data in Sheet #2
      Ary2 = Application.Index(.Cells, Evaluate("ROW(5:" & LastRow & ")"), Split(Cols, ","))        ' Data starts in row #5
End With

Ary1rows = 1      ' Loop counter for number of rows in Ary1
Ary2rows = 1      ' Loop counter for number of rows in Ary2

For Ary1rows = 1 To UBound(Ary1, 1)      ' Loop through each data row in Ary1 col. #1 while being compared to Ary2 col. #1
    For Ary2rows = 1 To UBound(Ary2, 1)  ' Loop through each data row in Ary2 col. #1 while being compared to Ary1 col. #1
        If (Ary2(Ary2rows, 1) = Ary1(Ary1rows, 1)) Then      ' Compare Ary1 column #1 to Ary2 column #1
           Ary1(Ary1rows, 2) = Ary2(Ary2rows, 2)             ' Copy Ary2 column #2 into Ary1 column #2
        End If
    Next
Next

With ws1
    .Range("F5:F" & NumRows) = Application.Index(Ary1, 0, 2)     ' Load Ary1 column #2 back into Sheet #1 column 'F'
End With
   Application.ScreenUpdating = True    ' Turn on screen update
End Sub

TotallyConfused

 

[NeoSez]

TotallyConfused​

Thank you

My Aswer Is This​

I really appreciate all your time as well.

 

[TotallyConfused]

You are very welcome, NeoSez. I'm glad I was able to be of assistance to you. That was an interesting question you asked.

THANK YOU for the 'thumbs up' vote.

TotallyConfused