Copy Data from one sheet to another from same number of cell which keep updating every day

[SKN2022]

Hello
I want to copy data from one sheet to another but the problem is that the source data is keep updating on same number of cells daily basis, so to extract data from those cells and freeze those data to another sheet, so that it will not update next day due to change in source data.
for example
in sheet 1 data from cell E3 to E7 is keep updating on daily basis (date wise like 24.08.22), now i want to copy this data automatically to sheet 2 for same date(24.08.22), now next day(25.08.22) the cell data of E3 to E7 in sheet 1 is changed so now i want these data to be copied to sheet 2 for date (25.08.22), but the data of 24.08.22 in sheet 2 not to be changed as its source data E3 to E7 is changed on 25.08.22, rather 24.08.22 data in sheet 2 should freeze.

hope u understood the problem.

thanks

 

[Joe4]

What range exactly is the data being copied 2?
Are you just copying each successive day under the previous one?

It might be helpful for us to see what these sheets look like. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[SKN2022]

What range exactly is the data being copied 2?
Are you just copying each successive day under the previous one?

It might be helpful for us to see what these sheets look like. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Loading Google Sheets

file is attached , sheet named "daily" is keep updating on daily basis with its link to another master sheet, wat i want is that when it is getting updated it should extract the data date wise to sheet named "compiled" currently for understanding i have linked the data between daily and compiled sheet to make u understand how i want the data to be copied......................................now the issue in this linking is that when the date and data changes in daily sheet next day it will not copy & pasting to compiled sheet in SUBSEQUENT ROW rather it will copy to same row,

 

[Joe4]

I see that you have merged cells in your worksheet. That is problematic. I know merged cells are often used to "make things look pretty", but they are quite literally one of the worst features in Excel, and make it very hard to work with data, and notoriously cause problems for VBA. Many programmers won't touch any problem that involves VBA and merged cells (they are that bad).

Do you have any ability to change the format of your worksheet to NOT use merged cells?

 

[SKN2022]

I see that you have merged cells in your worksheet. That is problematic. I know merged cells are often used to "make things look pretty", but they are quite literally one of the worst features in Excel, and make it very hard to work with data, and notoriously cause problems for VBA. Many programmers won't touch any problem that involves VBA and merged cells (they are that bad).

Do you have any ability to change the format of your worksheet to NOT use merged cells?

sorry for the late reply ,

as u said unmerged the cell, although it doesnt look the way i wanted but, that's not important so unmerged the cell to get the data as required..............other question is that can we do anything else to look it better also and no issue in running the vba.................
updated file is attached

Loading Google Sheets

 

[SKN2022]

WAITING....................................... FOR REPLY

 

[Joe4]

WAITING....................................... FOR REPLY

Please do not type in all Caps, that is considered shouting.

Sorry I missed your reply.
Your titles on your compiled sheet look a little funny (some seem to come from the "Contractor" column while others seem to come from the "Worker Type" column.
But as long as they are always going to be in the same order, I think this code should work:

Sub MyCopy()

    Dim wsD As Worksheet
    Dim wsC As Worksheet
    Dim nr As Long
    
'   Designate worksheet
    Set wsD = Sheets("daily")
    Set wsC = Sheets("compiled")
    
'   Find next row on "compiled" sheet
    nr = wsC.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy data over to new row
    wsC.Cells(nr, "A").Value = wsD.Range("A3").Value
    wsD.Range("E3:E7").Copy
    wsC.Cells(nr, "B").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
        
End Sub

 

[SKN2022]

Thank u its working, may i know can we create such code by using record macro option?

 

[Joe4]

Thank u its working, may i know can we create such code by using record macro option?

You can get some of the code using the Macro Recorder, but usually not all of it (or you may need to fix it up a little).
The Macro Recorder is often a great tool to get started, or to get snippets of code.
However, it is very literal, so all your ranges are "hard-coded".
So you usually need to adjust that part of the code, to make it more dynamic.

Note that the Macro Recorder will record some of the dynamic functionality. like CTRL+Down Arrow, CTRL+Up Arrow, CTRL+Right Arrow, CTRL + Left Arrow, and CTRL + End.

 

[SKN2022]

Thanks for the reply, can u tell how to learn VBA coding for a Non Tech guy in simple steps ................is there any tutorial available for the same