Simple copy, paste macro HELP!!

[CHWK26]

I would like to use the Master worksheet as the intake for new files to the department.
Each technician has their own worksheet within the same workbook.
I would like to run a macro which takes the data from cells A to G and use the number input from the cell in J and copy over into the technician in cell M

For example in the attachments. Two exhibits were submitted and i assigned this file to Technician GOODYEAR. I would like to quickly run a macro which would
then look at GOODYEAR (column M), see the total number of exhibits (column J) and then copy and paste cells A-G in the same row over onto the GOODYEAR sheet.

If you see on the MASTER sheet Technician ANDERSON has 4 exhibits assigned to him on the file 2021-56789 and so he would need to have cells A-G pasted on his worksheet
on 4 rows.

All sheets are working sheets and will be updated often. New files would come in, the macro would be ran every week or so and the content copied over. It is ok that the macro
starts over from scratch each time as long as it overrides and the columns A-G in the Technician sheets, but doesn't touch any other cells.

Can anyone help me with writing this for me?

Thanks for your help.

 

[Zot]

Not sure if this what you wanted. It is not the most efficient but it would do the job. Previous data will be cleared each time you run

Sub Copy()

Dim n As Long, offn As Long
Dim cell As Range, rngData As Range, rngDest As Range
Dim ws As Worksheet, wsSource As Worksheet

Set wsSource = ActiveWorkbook.Sheets("MASTER")
Set rngData = wsSource.Range("M2", wsSource.Cells(Rows.Count, "M").End(xlUp))

Application.ScreenUpdating = False

For Each cell In rngData
    For Each ws In Worksheets
        Select Case ws.Name
            Case cell.Value
                ws.Range("A2", "A500").EntireRow.Delete
                offn = wsSource.Range("J" & cell.Row)
                Set rngDest = ws.Range("A" & ws.Cells(Rows.Count, "A").End(xlUp).Row + 1, ws.Range("A" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Offset(offn, 0))
                wsSource.Range("A" & cell.Row, "G" & cell.Row).Copy rngDest
        End Select
    Next
Next

Application.ScreenUpdating = True

End Sub

 

[CHWK26]

Thanks Zot. Thats amazing. It works flawlessly; with one small issue. The EntireRow.delete function will erase what the technicians put in their rows for the files.
Is there a way to only repopulate rows A-G and leave the remaining cells alone?
PS: You are a rockstar!

 

[Zot]

Thanks Zot. Thats amazing. It works flawlessly; with one small issue. The EntireRow.delete function will erase what the technicians put in their rows for the files.
Is there a way to only repopulate rows A-G and leave the remaining cells alone?
PS: You are a rockstar!

That is not a problem but it is not clear to me when you said can start from scratch. Say you have 10 rows previously and then the new one only 5 rows. If I overwrite 5 rows, then you will have the data mixed with the previous one on the remaining rows.

Another problem would be if only column A to G are overwritten, then column H and beyond will have older data. I was assuming that the data in column A to G would be changed from time to time and this would not match with the rest of columns.

If you want to write new data below the previous data, then it would avoid data mixing. This way you will have history of all previous data.

Maybe I did not really understand the overall how your data looks like. I thought you are treating the workbook as working workbook and once compiling is done, you would copy the sheet to another workbook

 

[CHWK26]

Sorry about that.
The MASTER sheet will continue to grow and grow as there will be a new workbook generated each year. By the end of Dec the MASTER sheet will have over 300+ lines completed depending on the file load throughout the year. The Macro needs to continue to copy and paste the data over to the Technicians sheets.
The Master acts as my intake form. New requests for examinations on the exhibits come in, I add the requests to the Master sheet then run the macro to add the data to the Technicians work sheets.
They will view their worksheets, work on their exhibits and complete their part of the sheet for me.
On the MASTER i only want to have the one line per file. It might contain 1 or up to 12 exhibits. The Technicians need to have one line per exhibit per file, which you have mastered already. Thank you!

It would be great if the data did not override at all in cells A-G but it wouldn't matter as much as i wouldn't be changing anything already in the MASTER sheet, only adding on. The macro copy, paste to the technician sheets would look the same each time the macro started from scratch, it would just get bigger and bigger throughout the year. But yes, if there is a way the macro could, for example place a Y in column Q for when it completed a copy over to the technician worksheet, then another part of the code that checks column Q to see if a value exists and ignore it and move down, only copying the new data before submitting a Y in column Q so that it would ignore it the next time.......

Does that make sense? It seems like it is getting a little more complicated now though. Thanks for all your help.

 

[Zot]

Takes time ti reply since I'm probably 12 hours ahead ?

Since you are going to keep the list in that Master and also on each sheet, you need to keep track which list has been compiled before in the Master sheet. On Destination sheet, you just need to continue adding row from last occupied row.

One way is to just mark which line that has been compiled in Master. I am marking those live by coloring the cell to Yellow using
cell.Interior.ColorIndex = 6 (or vbYellow = 6. You can change to any color you like)

Sub Copy()

Dim n As Long, offn As Long
Dim cell As Range, rngData As Range, rngDest As Range
Dim ws As Worksheet, wsSource As Worksheet

Set wsSource = ActiveWorkbook.Sheets("MASTER")
Set rngData = wsSource.Range("M2", wsSource.Cells(Rows.Count, "M").End(xlUp))

Application.ScreenUpdating = False

For Each cell In rngData
    For Each ws In Worksheets
        Select Case ws.Name
            Case cell.Value
                If Not cell.Interior.ColorIndex = 6 Then
                    offn = wsSource.Range("J" & cell.Row)
                    Set rngDest = ws.Range("A" & ws.Cells(Rows.Count, "A").End(xlUp).Row + 1, ws.Range("A" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Offset(offn, 0))
                    wsSource.Range("A" & cell.Row, "G" & cell.Row).Copy rngDest
                    cell.Interior.ColorIndex = 6
                End If
        End Select
    Next
Next

Application.Goto wsSource.Range("A1")
Application.ScreenUpdating = True

End Sub

 

[CHWK26]

Takes time ti reply since I'm probably 12 hours ahead ?

Since you are going to keep the list in that Master and also on each sheet, you need to keep track which list has been compiled before in the Master sheet. On Destination sheet, you just need to continue adding row from last occupied row.

One way is to just mark which line that has been compiled in Master. I am marking those live by coloring the cell to Yellow using
cell.Interior.ColorIndex = 6 (or vbYellow = 6. You can change to any color you like)

Sub Copy()

Dim n As Long, offn As Long
Dim cell As Range, rngData As Range, rngDest As Range
Dim ws As Worksheet, wsSource As Worksheet

Set wsSource = ActiveWorkbook.Sheets("MASTER")
Set rngData = wsSource.Range("M2", wsSource.Cells(Rows.Count, "M").End(xlUp))

Application.ScreenUpdating = False

For Each cell In rngData
    For Each ws In Worksheets
        Select Case ws.Name
            Case cell.Value
                If Not cell.Interior.ColorIndex = 6 Then
                    offn = wsSource.Range("J" & cell.Row)
                    Set rngDest = ws.Range("A" & ws.Cells(Rows.Count, "A").End(xlUp).Row + 1, ws.Range("A" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Offset(offn, 0))
                    wsSource.Range("A" & cell.Row, "G" & cell.Row).Copy rngDest
                    cell.Interior.ColorIndex = 6
                End If
        End Select
    Next
Next

Application.Goto wsSource.Range("A1")
Application.ScreenUpdating = True

End Sub

This is the one! Cheers! Works flawlessly. I did change the colour from 6 to 35 as thats more my style! HAHA
Thanks for all your help. We really appreciate it!

~Matt

 

[Zot]

This is the one! Cheers! Works flawlessly. I did change the colour from 6 to 35 as thats more my style! HAHA
Thanks for all your help. We really appreciate it!

~Matt

Thanks for the update. Glad to help