VBA code that copies each option in a drop-down list and pastes it to a new workbook.

R

Rephot

New Member
Joined
Apr 14, 2024
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good day everyone,

I have a sheet in a workbook that displays results based on a drop-down list in that sheet.
For example,
if you choose "For approval" then all the results with for approval will paste into the "FOR APPROVAL" sheet.
if you choose "Rejected" then all the results with for approval will paste into the "REJECTED PTO" sheet.
if choose "Cancel" then all the results with for approval will paste into the "CANCELLED PTO" sheet.

All I have now is the first dropdown to copy and paste to the FOR APPROVAL sheet and the rest has no changes. Also, It didn't show in FOR APPROVAL sheet if I choose same row. Please see the images.

Please see the code I used,

Private Sub Worksheet_Change(ByVal Target As Range)


Dim Z As Long

Dim xVal As String

On Error Resume Next

If Intersect(Target, Range("n:n")) Is Nothing Then Exit Sub

Application.EnableEvents = False

For Z = 1 To Target.Count

If Target(Z).Value > 0 Then

Call CopyRowBasedOnCellValue

End If

Next

Application.EnableEvents = True

End Sub

Sub CopyRowBasedOnCellValue()


Dim xRg As Range

Dim xCell As Range

Dim A As Long

Dim B As Long

Dim C As Long

A = Worksheets("TEAM LINO").UsedRange.Rows.Count

B = Worksheets("FOR APPROVAL").UsedRange.Rows.Count

If B = 1 Then

If Application.WorksheetFunction.CountA(Worksheets("FOR APPROVAL").UsedRange) = 1 Then B = 0

End If

Set xRg = Worksheets("TEAM LINO").Range("N1:N" & A)

On Error Resume Next

Application.ScreenUpdating = False

For C = 1 To xRg.Count

If CStr(xRg(C).Value) = "For approval" Then

xRg(C).EntireRow.Copy Destination:=Worksheets("FOR APPROVAL").Range("A" & B + 1)

B = B + 1

End If

Worksheets("FOR APPROVAL").UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes

Worksheets("FOR APPROVAL").UsedRange.SpecialCells(xlCellTypeBlanks).Delete xlShiftUp

Next

Application.ScreenUpdating = True

End Sub



I have total of 11 sheets,
7 sheets are for different team names and 4 sheets for what you choose in the dropdown list result.

i.e
Approved PTO- Sheet 1
Cancelled PTO - Sheet 2
Rejected PTO - Sheet 3
For approval - Sheet 4

Team 1 - Sheet 5
Team 2 - Sheet 6
Team 3 - Sheet 7
Team 4 - Sheet 8
Team 5 - Sheet 9
Team 6 - Sheet 10
Team 7 - Sheet 11


TEAM JEN
COPY-CLUSTER-ARCIE-PTO-1.xlsx
ABCDEFGHIJKLMN
1AGENTS14#REF!#REF!#REF!#REF!#REF!#REF!#REF!
2WD IDNameSupervisorManagerSunMonTueWedThuFriSatReasonPriority List Number:Action
3102149489Aldrich DiazJenissa MicabaloArcie Jane Pann7:00AM-4:00PM2:00PM-11:00PM2:00PM-11:00PM2:00PM-11:00PM2:00PM-11:00PM2:00PM-11:00PM2:00PM-11:00PMASDSADSADSAD11. Travel without ticketFor approval
4101862957Elle MadidisJenissa MicabaloArcie Jane Pann--------
5102082853Archelle Pie QuemaJenissa MicabaloArcie Jane Pann--------
6101839878Ivan Paul DavalanJenissa MicabaloArcie Jane Pann--------
7102143489John Renzo AclanJenissa MicabaloArcie Jane Pann--------
8101840879Krizia Mae Jean FlorendoJenissa MicabaloArcie Jane Pann--------
9101039111Lovela AbingJenissa MicabaloArcie Jane Pann--------
10102082838Maryette Rea BanagloriosoJenissa MicabaloArcie Jane Pann--------
11101862047Nedzfhar AbadiaJenissa MicabaloArcie Jane Pann--------
12102175481Patrick John FangoJenissa MicabaloArcie Jane Pann--------
13102080670Renz Brylle MatugasJenissa MicabaloArcie Jane Pann--------
14102080307Russel HilauJenissa MicabaloArcie Jane Pann--------
15101856531Shiela Mae LabtonJenissa MicabaloArcie Jane Pann--------
16101856165Zyra InitanJenissa MicabaloArcie Jane Pann--------
17--Jenissa MicabaloArcie Jane Pann--------
18--Jenissa MicabaloArcie Jane Pann--------
19--Jenissa MicabaloArcie Jane Pann--------
20--Jenissa MicabaloArcie Jane Pann--------
21--Jenissa MicabaloArcie Jane Pann--------
22--Jenissa MicabaloArcie Jane Pann--------
23
24SME1#REF!#REF!#REF!#REF!#REF!#REF!#REF!
25WD IDNameSupervisorManagerSunMonTueWedThuFriSatReasonPriority List Number:Action
26101799120Olga MarieJenissa MicabaloArcie Jane Pann---------For approval
TEAM JEN
Cell Formulas
RangeFormula
B1B1=COUNTA(B3:B16)
E1:K1E1='FOR APPROVAL'!#REF!
B24B24=COUNTA(B26)
E24:K24E24=E1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E3:K22Cell Valuecontains "6:00AM-3:00PM"textNO
E3:K22Cell Valuecontains "7:00;M-4:00PM"textNO
E3:K22Cell Valuecontains "2:00;M-11:00PM"textNO
E3:K22Cell Valuecontains "PTO"textNO
E3:K22Cell Valuecontains "OFF"textNO
E26:K26Cell Valuecontains "6:00AM-3:00PM"textNO
E26:K26Cell Valuecontains "7:00;M-4:00PM"textNO
E26:K26Cell Valuecontains "2:00;M-11:00PM"textNO
E26:K26Cell Valuecontains "PTO"textNO
E26:K26Cell Valuecontains "OFF"textNO
Cells with Data Validation
CellAllowCriteria
E26:K26ListOFF, PTO, 6:00AM-2:00PM, 2:00PM-11:00PM, -
E3:K22ListOFF, PTO, 6:00AM-3:00PM, 7:00AM-4:00PM, 2:00PM-11:00PM, -
M3:M22List1. Medical surgery, 2. 40th day, 3. Travel with ticket, 4. Rater's Bday, 5. Wedding, 6. Medical checkup, 7. Exams, 8. Graduation, 9. Death anniv, 10. Other's birthday, 11. Travel without ticket, 12. Personal Matter, -
N3:N22ListFor approval, Rejected, Cancel
M26List1. Medical surgery, 2. 40th day, 3. Travel with ticket, 4. Rater's Bday, 5. Wedding, 6. Medical checkup, 7. Exams, 8. Graduation, 9. Death anniv, 10. Other's birthday, 11. Travel without ticket, 12. Personal Matter, -
N26ListFor approval, Rejected, Cancel



TEAM LINO
COPY-CLUSTER-ARCIE-PTO-1.xlsx
ABCDEFGHIJKLMN
1AGENTS15#REF!#REF!#REF!#REF!#REF!#REF!#REF!
2WD IDNameSupervisorManagerSunMonTueWedThuFriSatReasonPriority List Number:Action
3102149489Angelica DagandanLino TigaoArcie Jane PannPTO-OFF6:00AM-3:00PM----11. Travel without ticketFor approval
4101862957Janico RamadaLino TigaoArcie Jane Pann-PTO-OFF----10. Other's birthday
5102082853Jasper Kenneth ZapantaLino TigaoArcie Jane Pann----6:00AM-3:00PM-PTO-8. Graduation
6101839878Johna Ville LimLino TigaoArcie Jane Pann--PTO------
7102143489Joshua NavarroLino TigaoArcie Jane Pann---------
8101840879Joy BurgosLino TigaoArcie Jane Pann---------
9101039111Marcelo Jr AlfecheLino TigaoArcie Jane Pann---------
10102082838Mary Ann SamsonLino TigaoArcie Jane Pann---------
11101862047Michael OrtizoLino TigaoArcie Jane Pann---------
12102175481Raybener PedalinoLino TigaoArcie Jane Pann---------
13102080670Samuelle Marie RepolloLino TigaoArcie Jane Pann---------
14102080307Sev BanasiewiczLino TigaoArcie Jane Pann---------
15101856531Shella Marie CaralLino TigaoArcie Jane Pann---------
16101856165Syrell JabunganLino TigaoArcie Jane Pann---------
17101856662Zandy YtangLino TigaoArcie Jane Pann---------
18--Lino TigaoArcie Jane Pann---------
19--Lino TigaoArcie Jane Pann---------
20--Lino TigaoArcie Jane Pann---------
21--Lino TigaoArcie Jane Pann---------
22--Lino TigaoArcie Jane Pann---------
23
24SME1#REF!#REF!#REF!#REF!#REF!#REF!#REF!
25WD IDNameSupervisorManagerSunMonTueWedThuFriSatReasonPriority List Number:Action
26101835385Klyde RectoLino TigaoArcie Jane Pann---------For approval
TEAM LINO
Cell Formulas
RangeFormula
B1B1=COUNTA(B3:B17)
E1:K1E1='FOR APPROVAL'!#REF!
B24B24=COUNTA(B25)
E24:K24E24=E1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E3:K22,E26:K26Cell Valuecontains "6:00AM-3:00PM"textNO
E3:K22,E26:K26Cell Valuecontains "7:00;M-4:00PM"textNO
E3:K22,E26:K26Cell Valuecontains "2:00;M-11:00PM"textNO
E3:K22,E26:K26Cell Valuecontains "PTO"textNO
E3:K22,E26:K26Cell Valuecontains "OFF"textNO
Cells with Data Validation
CellAllowCriteria
E26:K26ListOFF, PTO, 6:00AM-2:00PM, 2:00PM-11:00PM, -
E3:K16ListOFF, PTO, 6:00AM-3:00PM, 7:00AM-4:00PM, 2:00PM-11:00PM, -
E17:K22ListOFF, PTO, 6:00AM-2:00PM, 2:00PM-11:00PM, -
M3:M22List1. Medical surgery, 2. 40th day, 3. Travel with ticket, 4. Rater's Bday, 5. Wedding, 6. Medical checkup, 7. Exams, 8. Graduation, 9. Death anniv, 10. Other's birthday, 11. Travel without ticket, 12. Personal Matter, -
N3:N22ListFor approval, Rejected, Cancel
M26List1. Medical surgery, 2. 40th day, 3. Travel with ticket, 4. Rater's Bday, 5. Wedding, 6. Medical checkup, 7. Exams, 8. Graduation, 9. Death anniv, 10. Other's birthday, 11. Travel without ticket, 12. Personal Matter, -
N26ListFor approval, Rejected, Cancel


FOR APPROVAL
COPY-CLUSTER-ARCIE-PTO-1.xlsx
ABCDEFGHIJKLMN
1WD IDNameSupervisorManagerSunMonTueWedThuFriSatReasonPriority List Number:Remarks
2102149489Aldrich DiazJenissa MicabaloArcie Jane Pann7:00AM-4:00PM2:00PM-11:00PM2:00PM-11:00PM2:00PM-11:00PM2:00PM-11:00PM2:00PM-11:00PM2:00PM-11:00PMASDSADSADSAD11. Travel without ticketFor approval
3101799120Olga MarieJenissa MicabaloArcie Jane Pann---------For approval
4101835385Klyde RectoLino TigaoArcie Jane Pann---------For approval
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
FOR APPROVAL


REJECTED PTO
COPY-CLUSTER-ARCIE-PTO-1.xlsx
ABCDEFGHIJKL
1CLUSTER ARCIE
2RATERS4/14/20244/15/20244/16/20244/17/20244/18/20244/19/20244/20/2024
3No.WD IDNameSupervisorManagerSunMonTueWedThuFriSat
41
52
63
74
85
96
107
118
129
1310
1411
1512
1613
1714
1815
1916
2017
2118
2219
2320
24
25
26SME4/14/20244/15/20244/16/20244/17/20244/18/20244/19/20244/20/2024
27No.WD IDNameSupervisorManagerSunMonTueWedThuFriSat
281
292
303
314
325
336
347
REJECTED PTO
Cell Formulas
RangeFormula
G26:L26,G2:L2G2=F2+1
F26F26=F2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F28:L34Cell Valuecontains "PTO"textNO
F28:L34Cell Valuecontains "OFF"textNO
F4:L12Cell Valuecontains "PTO"textNO
F4:L12Cell Valuecontains "OFF"textNO


CANCELLED PTO
COPY-CLUSTER-ARCIE-PTO-1.xlsx
ABCDEFGHIJKL
1CLUSTER ARCIE
2RATERS4/14/20244/15/20244/16/20244/17/20244/18/20244/19/20244/20/2024
3No.WD IDNameSupervisorManagerSunMonTueWedThuFriSat
41
52
63
74
85
96
107
118
129
1310
1411
1512
1613
1714
1815
1916
2017
2118
2219
2320
24
25
26SME4/14/20244/15/20244/16/20244/17/20244/18/20244/19/20244/20/2024
27No.WD IDNameSupervisorManagerSunMonTueWedThuFriSat
281
292
303
314
325
336
347
CANCELLED PTO
Cell Formulas
RangeFormula
G26:L26,G2:L2G2=F2+1
F26F26=F2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F28:L34Cell Valuecontains "PTO"textNO
F28:L34Cell Valuecontains "OFF"textNO
F4:L12Cell Valuecontains "PTO"textNO
F4:L12Cell Valuecontains "OFF"textNO



APPROVED PTO
COPY-CLUSTER-ARCIE-PTO-1.xlsx
ABCDEFGHIJKL
1CLUSTER ARCIE
2RATERS4/14/20244/15/20244/16/20244/17/20244/18/20244/19/20244/20/2024
3No.WD IDNameSupervisorManagerSunMonTueWedThuFriSat
41
52
63
74
85
96
107
118
129
1310
1411
1512
1613
1714
1815
1916
2017
2118
2219
2320
24
25
26SME4/14/20244/15/20244/16/20244/17/20244/18/20244/19/20244/20/2024
27No.WD IDNameSupervisorManagerSunMonTueWedThuFriSat
281
292
303
314
325
336
347
APPROVED PTO
Cell Formulas
RangeFormula
G26:L26,G2:L2G2=F2+1
F26F26=F2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F28:L34Cell Valuecontains "PTO"textNO
F28:L34Cell Valuecontains "OFF"textNO
F4:L12Cell Valuecontains "PTO"textNO
F4:L12Cell Valuecontains "OFF"textNO





Also, is it possible to start pasting to row 4 and separate the agent to sme?
I am not familiar with VBA at all, I was hoping to get a code that would select each option from the list then copy it and paste it as values and formatting to a new workbook one by one to a new sheet within that workbook.
The data to be pasted to the new workbook should be on each individual sheet.

I hope someone will help me. Thank you very much.
 

Attachments

  • approval.png
    approval.png
    28 KB · Views: 4
  • approval1.png
    approval1.png
    20.9 KB · Views: 4
  • approve.png
    approve.png
    24 KB · Views: 3
  • jen.png
    jen.png
    29.8 KB · Views: 3
  • lino.png
    lino.png
    39.3 KB · Views: 3
  • reject.png
    reject.png
    37.5 KB · Views: 3

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
You must log in or register to reply here.

Similar threads

A
  • Question
VBA code that copies each option in a drop-down list and pastes it to a new workbook as values and formatting.
Replies
1
Views
534
ranman256
ranman256
D
  • Solved
Copy & Paste Specific cells from a Row based on Drop-Down List
Replies
3
Views
2K
mumps
mumps
B
  • Question
VBA Code to copy and paste data to a form via a loop
Replies
2
Views
2K
Nadamasaqui
N
S
  • Solved
vba for vlookup to paste info onto next line down in next open cell in new workbook
2 3
Replies
21
Views
1K
Saoirse
S
E
  • Question
Problem with macro to copy vba to new workbook
Replies
0
Views
778
excel2007uk
E

Forum statistics

Threads
1,215,265
Messages
6,123,961
Members
449,135
Latest member
jcschafer209

Share this page

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
Back
Top