Simple copy, paste macro HELP!!

CHWK26

New Member
Joined
Aug 17, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Master.jpg
    Master.jpg
    171 KB · Views: 16
  • Goodyear.jpg
    Goodyear.jpg
    131.7 KB · Views: 16
  • Complete.jpg
    Complete.jpg
    161.8 KB · Views: 17

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,006
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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

New Member
Joined
Aug 17, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
2,006
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 17, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Attachments

  • More.jpg
    More.jpg
    220.2 KB · Views: 4

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,006
Office Version
  1. 2016
Platform
  1. Windows
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)

VBA Code:
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
 
Solution

CHWK26

New Member
Joined
Aug 17, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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)

VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
2,006
Office Version
  1. 2016
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,570
Messages
5,770,915
Members
425,652
Latest member
Pemby

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