Match and copy data between sheets

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I have developed some VBA code that does what you ask, however, to make it work, you must unmerge all data cells in sheet1. VBA does not like to work with merged cells and if you do not unmerge your cells, this code will fail.

Option Explicit

Sub ams()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheet1
    Set sh2 = Sheet2
    Dim i As Long
    i = Day(sh1.Range("D3"))
    
    Application.ScreenUpdating = False
    sh1.Range("F7:F13").Copy
    sh2.Range("B" & i + 6).PasteSpecial xlPasteAll, , , True
    Application.Union(sh1.Range("F19"), sh1.Range("H19"), sh1.Range("J19")).Copy sh2.Range("I" & i + 6)
    Application.Union(sh1.Range("F20"), sh1.Range("H20"), sh1.Range("J20")).Copy sh2.Range("L" & i + 6)
    Application.Union(sh1.Range("F21"), sh1.Range("H21"), sh1.Range("J21")).Copy sh2.Range("O" & i + 6)
    sh1.Range("D24").Copy sh2.Range("R" & i + 6)
    sh1.Range("I24").Copy sh2.Range("T" & i + 6)
    Application.CutCopyMode = False
    sh1.Range("F7:F13").ClearContents
    sh1.Range("F19:J21").ClearContents
    sh1.Range("I24").ClearContents
    sh1.Range("D24").ClearContents
    Application.ScreenUpdating = True
    MsgBox "Action Completed"
End Sub

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

What does this mean?

do you have any idea how the code looks like?

With out seeing your actual file, I have no idea what anything on your spreadsheet looks like. If you don't wish to unmerge your cells and redesign your worksheet, then you will need to find another means to solve this issue. The solution I provided works for the example you provided. If you wish to determine a solution that works for a different spreadsheet, then we will need to see that spreadsheet.