Auto copy and paste column data using command button

[TurfBurger]

Attached is a copy of a work schedule that my boss produces each week (sheet 1). He then painstakingly copies each worker their own schedule to a new worksheet. The problem is, many times he misses copying a schedule or gets another workers schedule in with the wrong workers schedule.

I would like to build a VBA macro that would automatically copy individual schedules to each workers (using a command button?) sheet from sheet 1 based on the WHO name column. When the macro is run, the main schedule (sheet 1) would copy each person’s schedule to their own sheet. Keeping the same format as sheet 1. The copied row of data would automatically be placed in the sheet tab with their name, thus avoiding the wrong scheduling of workers.


Find worker name (column) on sheet 1

Clear worker sheet before copying (to avoid duplications)

Then copy and paste worker schedule (rows) to their individual named sheet keeping the formatting of sheet 1
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Nebu]

Hi:
Use the following code:

Sub test()

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

r& = Sheet7.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.AutoFilterMode = False

For i& = 2 To r
    With Sheet1.Range("A1:K" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row)
        .AutoFilter
        .AutoFilter field:=4, Criteria1:=Sheet7.Range("A" & i)
    End With
   
    Sheet1.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(Sheet7.Range("A" & i).Text).Range("A3")
Next

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Thanks

 

[vletm]

.. or something like this...
Shows 'not valid' "who" and makes sheet for 'who' if missing.