Transferring data from one list to another

[Ivn68]

I have a dates on sheet2

Date	Hours Worked	Hours off
08/01/24	4	4
08/05/24	5	2
08/07/24	10	0

I want to be able to find the date on sheet1 and then enter the corresponding values underneath the date

date	8/1/2024	8/2/2024	8/3/2024	8/4/2024	8/5/2024	8/6/2024	8/7/2024
hours
off
date	8/8/2024	8/9/2024	8/10/2024	8/11/2024	8/12/2024	8/13/2024	8/14/2024
hours
off
date	8/15/2024	8/16/2024	8/17/2024	8/18/2024	8/19/2024	8/20/2024	8/21/2024
hours
hours
off

so ideally I would enter all the info onto the list as the year goes on and then I would like to hit a button and it will transfer all the info onto sheet1. So the list will grow

thanks for any help

 

[DataWannaBe]

Presuming top left 'Date' cell in each table is in cell A1
In Sheet1 in cell B2 enter

=IFERROR(INDEX(Sheet2!$B:$B,MATCH(B2,Sheet2!$A:$A,0)),"")

In cell B3 enter

=IFERROR(INDEX(Sheet2!$C:$C,MATCH(B2,Sheet2!$A:$A,0)),"")

Select B2&B3 and copy, select the range B2-H3 and paste. Now copy all of the B2-H3 range and copy, now paste in B5 and B8

	01/08/2024​	02/08/2024​	03/08/2024​	04/08/2024​	05/08/2024​	06/08/2024​	07/08/2024​
hours	4​				5​		10​
off	4​				2​		0​
date	08/08/2024​	09/08/2024​	10/08/2024​	11/08/2024​	12/08/2024​	13/08/2024​	14/08/2024​
hours
off
date	15/08/2024​	16/08/2024​	17/08/2024​	18/08/2024​	19/08/2024​	20/08/2024​	21/08/2024​
hours
off

 

[DataWannaBe]

Presuming top left 'Date' cell in each table is in cell A1
In Sheet1 in cell B2 enter

=IFERROR(INDEX(Sheet2!$B:$B,MATCH(B2,Sheet2!$A:$A,0)),"")

In cell B3 enter

=IFERROR(INDEX(Sheet2!$C:$C,MATCH(B2,Sheet2!$A:$A,0)),"")

Select B2&B3 and copy, select the range B2-H3 and paste. Now copy all of the B2-H3 range and copy, now paste in B5 and B8

	01/08/2024​	02/08/2024​	03/08/2024​	04/08/2024​	05/08/2024​	06/08/2024​	07/08/2024​
hours	4​				5​		10​
off	4​				2​		0​
date	08/08/2024​	09/08/2024​	10/08/2024​	11/08/2024​	12/08/2024​	13/08/2024​	14/08/2024​
hours
off
date	15/08/2024​	16/08/2024​	17/08/2024​	18/08/2024​	19/08/2024​	20/08/2024​	21/08/2024​
hours
off

Apologies,
In B2 enter

=IFERROR(INDEX(Sheet2!$B:$B,MATCH(B1,Sheet2!$A:$A,0)),"")

In B3 enter

=IFERROR(INDEX(Sheet2!$C:$C,MATCH(B1,Sheet2!$A:$A,0)),"")

 

[Ivn68]

that doesn't work for me because I may have info already in the cell
sheet 2 is like an adjustment
thanks

it needs to be something i can like activate after i enter the adjustments

 

[Ivn68]

something like this

Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer


b = Range("B4:B365").Rows.Count


For a = 4 To b
c = Sheets("PTO Adustments").Cells(2, a)
For d = 9 To 221
If c = ActiveSheet.Cells(3, d).Value Then
ActiveSheet.Cells(3, d + 1).Value = Sheets("PTO Adustments").Cells(3, a)
ActiveSheet.Cells(3, d + 2).Value = Sheets("PTO Adustments").Cells(4, a)
End If
Next d
next a

having problems with which sheet im dealing with in the vba




Next a

 

[Georgiboy]

Perhaps something like the below:

Sub Test()
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim rCell As Range, fRng As Range
  Dim fDate As Date
  
  Set ws1 = Sheets("Sheet1")
  Set ws2 = Sheets("Sheet2")
  
  For Each rCell In ws2.Range("A2:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row)
    fDate = rCell
    Set fRng = ws1.Cells.Find(fDate)
    If Not fRng Is Nothing Then
      fRng.Offset(1).Resize(2) = Application.Transpose(rCell.Offset(, 1).Resize(, 2))
    End If
  Next rCell
End Sub

 

[Georgiboy]

Should probably update the below:

Set fRng = ws1.Cells.Find(fDate)

With:

Set fRng = ws1.Cells.Find(fDate, , xlValues, xlWhole)

Making the code:

Sub Test()
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim rCell As Range, fRng As Range
  Dim fDate As Date
  
  Set ws1 = Sheets("Sheet1")
  Set ws2 = Sheets("Sheet2")
  
  For Each rCell In ws2.Range("A2:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row)
    fDate = rCell
    Set fRng = ws1.Cells.Find(fDate, , xlValues, xlWhole)
    If Not fRng Is Nothing Then
      fRng.Offset(1).Resize(2) = Application.Transpose(rCell.Offset(, 1).Resize(, 2))
    End If
  Next rCell
End Sub

 

[Ivn68]

I get a type miss match on
fDate = rCell

Line

 

[Ivn68]

I get a type miss match on
fDate = rCell

Line

I get that error at work but at home no error but it didn't do anything
Nothing changed on the first sheet

 

[Georgiboy]

I am wondering if your dates are actual dates, are you able to use the XL2BB add in to display the data you are working with?

Are your dates - actual dates in the cell/ the result of a formula or just text strings?