Copy Data from Sheet to Sheet

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,265
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,265
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hello NeoSez
I hope this is what you are looking for.
VBA Code:
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
 
Last edited:
Solution

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,391
Messages
5,601,396
Members
414,448
Latest member
Jessica 22664

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
Top