Nested For Next Loop

S

srdavisgb

Board Regular
Joined
Nov 5, 2011
Messages
51
I'm trying to use a "nested For Next loop" for the first time. I would appreciate your help for the following problem. Below is my code. I apologize for its length but, I thought it might be needed in its entirety to understand the issue. I'm getting a Compile Error - Next without For.

Thanks in advance for your assistance.
Code: 
Sub Create_JD_Workbooks()

    'Get directory information and ECS requirement data.
    Grp_ECS_Path = Sheets("JD Workbook Requirements").Cells(12, 6)
    ECS_Template_Path = Sheets("JD Workbook Requirements").Cells(14, 6)
    ECS_Save_Path = Sheets("JD Workbook Requirements").Cells(16, 6)
    LstRowNo = Sheets("JD Workbook Requirements").Cells(20, 6)
    Grp_ECS_Save_Dir = Sheets("JD Workbook Requirements").Cells(18, 6)


'CREATE ECS Files

Dim TotRows As Integer
Dim StartLine As Integer
Dim EndLine As Integer
Dim I As Integer
Dim CurrRow As Integer
Dim CurrRow2 As Integer

Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
TotRows = Sheets("All Job Properties").Cells(1, 1)
CurrRow = 2

Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
TotRows2 = Sheets("Pivot Table").Cells(1, 1)
CurrRow2 = 2

Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
TotRows3 = Sheets("Pivot Table Job Elements").Cells(1, 1)
CurrRow3 = 2

For I = 1 To TotRows
EECount = Sheets("Pivot Table").Cells(CurrRow2, 3)
StartLine = Sheets("Pivot Table").Cells(CurrRow2, 4)
EndLine = Sheets("Pivot Table").Cells(CurrRow2, 5)

Filename = Sheets("All Job Properties").Cells(CurrRow, 45)

Job_Title = Sheets("All Job Properties").Cells(CurrRow, 2)
Job_Code = Sheets("All Job Properties").Cells(CurrRow, 4)
Grade = Sheets("All Job Properties").Cells(CurrRow, 7)
Grade2 = Sheets("All Job Properties").Cells(CurrRow, 8)
Job_Family = Sheets("All Job Properties").Cells(CurrRow, 12)
Summary = Sheets("All Job Properties").Cells(CurrRow, 19)
FLSA = Sheets("All Job Properties").Cells(CurrRow, 23)


RowNo = Sheets("All Job Properties").Cells(CurrRow, 43)
GrpCtr = Sheets("All Job Properties").Cells(CurrRow, 44)
grpcounternext = Sheets("All Job Properties").Cells(CurrRow + 1, 44)
CS_Type = Sheets("All Job Properties").Cells(CurrRow, 46)
Education = Sheets("All Job Properties").Cells(CurrRow, 47)
Wksht_Job_Title = Sheets("All Job Properties").Cells(CurrRow, 49)

'******************************************************************************************

If GrpCtr = 1 Then
Workbooks.Open Filename:=Grp_ECS_Path & "" & "XYZ Group Job Desc Workbook.xlsx"

'Copy Job Title into Group Workbook
Windows("Create XYZ Job Description Workbooks_2.xlsm").Activate
Sheets("All Job Properties").Select
RangeArea = "B" & StartLine & ":B" & EndLine
Range(RangeArea).Select
Selection.Copy
Windows("XYZ Group Job Desc Workbook.xlsx").Activate
Sheets("Job Family").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Copy Job Code into Group Workbook
Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
Sheets("All Job Properties").Select
RangeArea = "D" & StartLine & ":D" & EndLine
Range(RangeArea).Select
Selection.Copy
Windows("XYZ Group Job Desc Workbook.xlsx").Activate
Sheets("Job Family").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Copy Grade into Group Workbook
Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
Sheets("All Job Properties").Select
RangeArea = "G" & StartLine & ":G" & EndLine
Range(RangeArea).Select
Selection.Copy
Windows("XYZ Group Job Desc Workbook.xlsx").Activate
Sheets("Job Family").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Copy Management Level into Group Workbook
Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
Sheets("All Job Properties").Select
RangeArea = "H" & StartLine & ":H" & EndLine
Range(RangeArea).Select
Selection.Copy
Windows("XYZ Group Job Desc Workbook.xlsx").Activate
Sheets("Job Family").Select
Range("D2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Copy FLSA into Group Workbook
Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
Sheets("All Job Properties").Select
RangeArea = "W" & StartLine & ":W" & EndLine
Range(RangeArea).Select
Selection.Copy
Windows("XYZ Group Job Desc Workbook.xlsx").Activate
Sheets("Job Family").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Copy Job Summary into Group Workbook
Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
Sheets("All Job Properties").Select
RangeArea = "S" & StartLine & ":S" & EndLine
Range(RangeArea).Select
Selection.Copy
Windows("XYZ Group Job Desc Workbook.xlsx").Activate
Sheets("Job Family").Select
Range("F2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


' Delete Unneeded Rows in Job Family Worksheet
Windows("XYZ Group Job Desc Workbook.xlsx").Activate
Sheets("Job Family").Select
RangeArea = "A" & (EECount + 2) & ":w10000"
Range(RangeArea).Select
Selection.Delete Shift:=xlUp
Range("A1").Select

'Sort by LNAME
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("A1").Select

CurrRow2 = CurrRow2 + 1


End If

'Individual Job Description

If CS_Type = 1 Then

Workbooks.Open Filename:=ECS_Template_Path & "\XYZ Job Description.xlsx"

'Enter Demographic information
Range("D4").Select
ActiveCell.FormulaR1C1 = Job_Title
Range("D6").Select
ActiveCell.FormulaR1C1 = Job_Code
Range("D8").Select
ActiveCell.FormulaR1C1 = Grade
Range("D10").Select
ActiveCell.FormulaR1C1 = Grade2
Range("D12").Select
ActiveCell.FormulaR1C1 = Job_Family
Range("D14").Select
ActiveCell.FormulaR1C1 = Summary
Range("D16").Select
ActiveCell.FormulaR1C1 = FLSA
Range("D18").Select
ActiveCell.FormulaR1C1 = Education


'******************************************************************************************

For I_2 = 1 To TotRows3
EECount3 = Sheets("Pivot Table Job Elements").Cells(CurrRow3, 4)
StartLine3 = Sheets("Pivot Table Job Elements").Cells(CurrRow3, 5)
EndLine3 = Sheets("Pivot Table Job Elements").Cells(CurrRow3, 6)

Job_Title_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 2)
Job_Code_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 3)
Job_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 11)


RowNo_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 43)
GrpCtr_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 44)
grpcounternext_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3 + 1, 44)
CS_Type_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 46)
Education = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 47)

'******************************************************************************************

'Copy Essential Job Elements into Job Description Worksheet23
Windows("Create XYZ Job Description Workbooks_2.xlsm").Activate
Sheets("Job Specific Essential Fun,4274").Select
RangeArea = "L" & StartLine3 & ":L" & EndLine3
Range(RangeArea).Select
Selection.Copy
Windows("XYZ Job Description.xlsx").Activate
Sheets("Name").Select
Range("D25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Next I_2

'CurrRow3 = CurrRow + 1

'******************************************************************************************

'Copy Leadership Elements into Job Description Worksheet

If Grade2 = "Supervisor" Then
Sheets("Leadership Essential Elements").Select
RangeArea = "B2:B3"
Range(RangeArea).Select
Selection.Copy
Workbooks("XYZ Job Description.xlsx").Activate
Sheets("Name").Select
Range("D20").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Else

If Grade2 = "Coordinator" Then
Sheets("Leadership Essential Elements").Select
RangeArea = "B4:B6"
Range(RangeArea).Select
Selection.Copy
Workbooks("XYZ Job Description.xlsx").Activate
Sheets("Name").Select
Range("D20").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Else

If Grade2 = "Manager" Then
Sheets("Leadership Essential Elements").Select
RangeArea = "B7:B9"
Range(RangeArea).Select
Selection.Copy
Workbooks("XYZ Job Description.xlsx").Activate
Sheets("Name").Select
Range("D20").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Else

If Grade2 = "Senior Manager" Then
Sheets("Leadership Essential Elements").Select
RangeArea = "B10:B12"
Range(RangeArea).Select
Selection.Copy
Workbooks("XYZ Job Description.xlsx").Activate
Sheets("Name").Select
Range("D20").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Else

If Grade2 = "Director" Then
Sheets("Leadership Essential Elements").Select
RangeArea = "B13:B16"
Range(RangeArea).Select
Selection.Copy
Workbooks("XYZ Job Description.xlsx").Activate
Sheets("Name").Select
Range("D20").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Else

If Grade2 = "Executive Director" Then
Sheets("Leadership Essential Elements").Select
RangeArea = "B17:B20"
Range(RangeArea).Select
Selection.Copy
Workbooks("XYZ Job Description.xlsx").Activate
Sheets("Name").Select
Range("D20").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Else

If Grade2 = "Vice President" Then
Sheets("Leadership Essential Elements").Select
RangeArea = "B21:B24"
Range(RangeArea).Select
Selection.Copy
Workbooks("XYZ Job Description.xlsx").Activate
Sheets("Name").Select
Range("D20").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Else

Workbooks("XYZ Job Description.xlsx").Activate
Sheets("Name").Select
Rows("20:24").Select
Selection.Delete Shift:=xlUp

End If


'*****************************************************************************************

CurrRow3 = CurrRow + 1


'*****************************************************************************************

Workbooks("XYZ Job Description.xlsx").Activate
Sheets("Name").Select
Sheets("Name").Name = Wksht_Job_Title
ActiveWindow.DisplayVerticalScrollBar = True

Range("A1").Select

ActiveSheet.Protect Password:="XYZ2018", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=False, AllowSorting:=False, AllowFiltering:=False
'ActiveWorkbook.SaveAs Filename:=ECS_File_Name_Path _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Sheets(Wksht_Job_Title).Copy After:=Workbooks("XYZ Group Job Desc Workbook.xlsx").Sheets(1)

Windows("XYZ Job Description.xlsx").Activate
ActiveWorkbook.Close SaveChanges:=False

End If

If grpcounternext = 1 Or grpcounternext = "" Then


Windows("XYZ Group Job Desc Workbook.xlsx").Activate

ActiveWorkbook.SaveAs Filename:=Grp_ECS_Save_Dir & "" & Filename _
, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWorkbook.Close

End If

CurrRow = CurrRow + 1

Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
Range("A1").Select

Next I


Sheets("JD Workbook Requirements").Activate
Range("A1").Select

End Sub
 
Last edited by a moderator:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Please surround you code with CODE tags (see my signature block below).

You have several IF statements at the bottom without End Ifs. I indented your original code to illustrate nested hierarchy, but didn't correct your code. Each IF should have a corresponding End If at the same indent.

Code: 
[COLOR=darkblue]Sub[/COLOR] Create_JD_Workbooks()


[COLOR=green]'Get directory information and ECS requirement data.[/COLOR]
    Grp_ECS_Path = Sheets("JD Workbook Requirements").Cells(12, 6)
    ECS_Template_Path = Sheets("JD Workbook Requirements").Cells(14, 6)
    ECS_Save_Path = Sheets("JD Workbook Requirements").Cells(16, 6)
    LstRowNo = Sheets("JD Workbook Requirements").Cells(20, 6)
    Grp_ECS_Save_Dir = Sheets("JD Workbook Requirements").Cells(18, 6)




    [COLOR=green]'CREATE ECS Files[/COLOR]


    [COLOR=darkblue]Dim[/COLOR] TotRows   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] StartLine [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] EndLine   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] I         [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] CurrRow   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] CurrRow2  [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]


    Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
    TotRows = Sheets("All Job Properties").Cells(1, 1)
    CurrRow = 2


    Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
    TotRows2 = Sheets("Pivot Table").Cells(1, 1)
    CurrRow2 = 2


    Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
    TotRows3 = Sheets("Pivot Table Job Elements").Cells(1, 1)
    CurrRow3 = 2


    [COLOR=darkblue]For[/COLOR] I = 1 [COLOR=darkblue]To[/COLOR] TotRows
        EECount = Sheets("Pivot Table").Cells(CurrRow2, 3)
        StartLine = Sheets("Pivot Table").Cells(CurrRow2, 4)
        EndLine = Sheets("Pivot Table").Cells(CurrRow2, 5)


        Filename = Sheets("All Job Properties").Cells(CurrRow, 45)


        Job_Title = Sheets("All Job Properties").Cells(CurrRow, 2)
        Job_Code = Sheets("All Job Properties").Cells(CurrRow, 4)
        Grade = Sheets("All Job Properties").Cells(CurrRow, 7)
        Grade2 = Sheets("All Job Properties").Cells(CurrRow, 8)
        Job_Family = Sheets("All Job Properties").Cells(CurrRow, 12)
        Summary = Sheets("All Job Properties").Cells(CurrRow, 19)
        FLSA = Sheets("All Job Properties").Cells(CurrRow, 23)




        RowNo = Sheets("All Job Properties").Cells(CurrRow, 43)
        GrpCtr = Sheets("All Job Properties").Cells(CurrRow, 44)
        grpcounternext = Sheets("All Job Properties").Cells(CurrRow + 1, 44)
        CS_Type = Sheets("All Job Properties").Cells(CurrRow, 46)
        Education = Sheets("All Job Properties").Cells(CurrRow, 47)
        Wksht_Job_Title = Sheets("All Job Properties").Cells(CurrRow, 49)


        [COLOR=green]'******************************************************************************************[/COLOR]


        [COLOR=darkblue]If[/COLOR] GrpCtr = 1 [COLOR=darkblue]Then[/COLOR]
            Workbooks.Open Filename:=Grp_ECS_Path & "" & "XYZ Group Job Desc Workbook.xlsx"


            [COLOR=green]'Copy Job Title into Group Workbook[/COLOR]
            Windows("Create XYZ Job Description Workbooks_2.xlsm").Activate
            Sheets("All Job Properties").Select
            RangeArea = "B" & StartLine & ":B" & EndLine
            Range(RangeArea).Select
            Selection.Copy
            Windows("XYZ Group Job Desc Workbook.xlsx").Activate
            Sheets("Job Family").Select
            Range("A2").Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                   [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


            [COLOR=green]'Copy Job Code into Group Workbook[/COLOR]
            Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
            Sheets("All Job Properties").Select
            RangeArea = "D" & StartLine & ":D" & EndLine
            Range(RangeArea).Select
            Selection.Copy
            Windows("XYZ Group Job Desc Workbook.xlsx").Activate
            Sheets("Job Family").Select
            Range("B2").Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                   [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


            [COLOR=green]'Copy Grade into Group Workbook[/COLOR]
            Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
            Sheets("All Job Properties").Select
            RangeArea = "G" & StartLine & ":G" & EndLine
            Range(RangeArea).Select
            Selection.Copy
            Windows("XYZ Group Job Desc Workbook.xlsx").Activate
            Sheets("Job Family").Select
            Range("C2").Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                   [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


            [COLOR=green]'Copy Management Level into Group Workbook[/COLOR]
            Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
            Sheets("All Job Properties").Select
            RangeArea = "H" & StartLine & ":H" & EndLine
            Range(RangeArea).Select
            Selection.Copy
            Windows("XYZ Group Job Desc Workbook.xlsx").Activate
            Sheets("Job Family").Select
            Range("D2").Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                   [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


            [COLOR=green]'Copy FLSA into Group Workbook[/COLOR]
            Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
            Sheets("All Job Properties").Select
            RangeArea = "W" & StartLine & ":W" & EndLine
            Range(RangeArea).Select
            Selection.Copy
            Windows("XYZ Group Job Desc Workbook.xlsx").Activate
            Sheets("Job Family").Select
            Range("E2").Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                   [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


            [COLOR=green]'Copy Job Summary into Group Workbook[/COLOR]
            Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
            Sheets("All Job Properties").Select
            RangeArea = "S" & StartLine & ":S" & EndLine
            Range(RangeArea).Select
            Selection.Copy
            Windows("XYZ Group Job Desc Workbook.xlsx").Activate
            Sheets("Job Family").Select
            Range("F2").Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                   [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]




            [COLOR=green]' Delete Unneeded Rows in Job Family Worksheet[/COLOR]
            Windows("XYZ Group Job Desc Workbook.xlsx").Activate
            Sheets("Job Family").Select
            RangeArea = "A" & (EECount + 2) & ":w10000"
            Range(RangeArea).Select
            Selection.Delete Shift:=xlUp
            Range("A1").Select


            [COLOR=green]'Sort by LNAME[/COLOR]
            Cells.Select
            Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                           OrderCustom:=1, MatchCase:=False, [COLOR=darkblue]Or[/COLOR]ientation:=xl[COLOR=darkblue]To[/COLOR]pToBottom, _
                           DataOption1:=xlSortNormal


            Range("A1").Select


            CurrRow2 = CurrRow2 + 1




        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]


        [COLOR=green]'Individual Job Description[/COLOR]


        [COLOR=darkblue]If[/COLOR] CS_Type = 1 [COLOR=darkblue]Then[/COLOR]


            Workbooks.Open Filename:=ECS_Template_Path & "\XYZ Job Description.xlsx"


            [COLOR=green]'Enter Demographic information[/COLOR]
            Range("D4").Select
            ActiveCell.FormulaR1C1 = Job_Title
            Range("D6").Select
            ActiveCell.FormulaR1C1 = Job_Code
            Range("D8").Select
            ActiveCell.FormulaR1C1 = Grade
            Range("D10").Select
            ActiveCell.FormulaR1C1 = Grade2
            Range("D12").Select
            ActiveCell.FormulaR1C1 = Job_Family
            Range("D14").Select
            ActiveCell.FormulaR1C1 = Summary
            Range("D16").Select
            ActiveCell.FormulaR1C1 = FLSA
            Range("D18").Select
            ActiveCell.FormulaR1C1 = Education




            [COLOR=green]'******************************************************************************************[/COLOR]


            [COLOR=darkblue]For[/COLOR] I_2 = 1 To TotRows3
                EECount3 = Sheets("Pivot Table Job Elements").Cells(CurrRow3, 4)
                StartLine3 = Sheets("Pivot Table Job Elements").Cells(CurrRow3, 5)
                EndLine3 = Sheets("Pivot Table Job Elements").Cells(CurrRow3, 6)


                Job_Title_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 2)
                Job_Code_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 3)
                Job_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 11)




                RowNo_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 43)
                GrpCtr_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 44)
                grpcounternext_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3 + 1, 44)
                CS_Type_Elements = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 46)
                Education = Sheets("Job Specific Essential Fun,4274").Cells(CurrRow3, 47)


                [COLOR=green]'******************************************************************************************[/COLOR]


                [COLOR=green]'Copy Essential Job Elements into Job Description Worksheet23[/COLOR]
                Windows("Create XYZ Job Description Workbooks_2.xlsm").Activate
                Sheets("Job Specific Essential Fun,4274").Select
                RangeArea = "L" & StartLine3 & ":L" & EndLine3
                Range(RangeArea).Select
                Selection.Copy
                Windows("XYZ Job Description.xlsx").Activate
                Sheets("Name").Select
                Range("D25").Select
                Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                       [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


            [COLOR=darkblue]Next[/COLOR] I_2


            [COLOR=green]'CurrRow3 = CurrRow + 1[/COLOR]


            [COLOR=green]'******************************************************************************************[/COLOR]


            [COLOR=green]'Copy Leadership Elements into Job Description Worksheet[/COLOR]


            [COLOR=darkblue]If[/COLOR] Grade2 = "Supervisor" [COLOR=darkblue]Then[/COLOR]
                Sheets("Leadership Essential Elements").Select
                RangeArea = "B2:B3"
                Range(RangeArea).Select
                Selection.Copy
                Workbooks("XYZ Job Description.xlsx").Activate
                Sheets("Name").Select
                Range("D20").Select
                Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                       [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


            [COLOR=darkblue]Else[/COLOR]


                [COLOR=darkblue]If[/COLOR] Grade2 = "Coordinator" [COLOR=darkblue]Then[/COLOR]
                    Sheets("Leadership Essential Elements").Select
                    RangeArea = "B4:B6"
                    Range(RangeArea).Select
                    Selection.Copy
                    Workbooks("XYZ Job Description.xlsx").Activate
                    Sheets("Name").Select
                    Range("D20").Select
                    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                           [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


                [COLOR=darkblue]Else[/COLOR]


                    [COLOR=darkblue]If[/COLOR] Grade2 = "Manager" [COLOR=darkblue]Then[/COLOR]
                        Sheets("Leadership Essential Elements").Select
                        RangeArea = "B7:B9"
                        Range(RangeArea).Select
                        Selection.Copy
                        Workbooks("XYZ Job Description.xlsx").Activate
                        Sheets("Name").Select
                        Range("D20").Select
                        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                               [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


                    [COLOR=darkblue]Else[/COLOR]


                        [COLOR=darkblue]If[/COLOR] Grade2 = "Senior Manager" [COLOR=darkblue]Then[/COLOR]
                            Sheets("Leadership Essential Elements").Select
                            RangeArea = "B10:B12"
                            Range(RangeArea).Select
                            Selection.Copy
                            Workbooks("XYZ Job Description.xlsx").Activate
                            Sheets("Name").Select
                            Range("D20").Select
                            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                                   [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


                        [COLOR=darkblue]Else[/COLOR]


                            [COLOR=darkblue]If[/COLOR] Grade2 = "Director" [COLOR=darkblue]Then[/COLOR]
                                Sheets("Leadership Essential Elements").Select
                                RangeArea = "B13:B16"
                                Range(RangeArea).Select
                                Selection.Copy
                                Workbooks("XYZ Job Description.xlsx").Activate
                                Sheets("Name").Select
                                Range("D20").Select
                                Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                                       [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


                            [COLOR=darkblue]Else[/COLOR]


                                [COLOR=darkblue]If[/COLOR] Grade2 = "Executive Director" [COLOR=darkblue]Then[/COLOR]
                                    Sheets("Leadership Essential Elements").Select
                                    RangeArea = "B17:B20"
                                    Range(RangeArea).Select
                                    Selection.Copy
                                    Workbooks("XYZ Job Description.xlsx").Activate
                                    Sheets("Name").Select
                                    Range("D20").Select
                                    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                                           [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


                                [COLOR=darkblue]Else[/COLOR]


                                    [COLOR=darkblue]If[/COLOR] Grade2 = "Vice President" [COLOR=darkblue]Then[/COLOR]
                                        Sheets("Leadership Essential Elements").Select
                                        RangeArea = "B21:B24"
                                        Range(RangeArea).Select
                                        Selection.Copy
                                        Workbooks("XYZ Job Description.xlsx").Activate
                                        Sheets("Name").Select
                                        Range("D20").Select
                                        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                                               [COLOR=darkblue]False[/COLOR], Transpose:=[COLOR=darkblue]False[/COLOR]


                                    [COLOR=darkblue]Else[/COLOR]


                                        Workbooks("XYZ Job Description.xlsx").Activate
                                        Sheets("Name").Select
                                        Rows("20:24").Select
                                        Selection.Delete Shift:=xlUp


                                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]




                                    [COLOR=green]'*****************************************************************************************[/COLOR]


                                    CurrRow3 = CurrRow + 1




                                    [COLOR=green]'*****************************************************************************************[/COLOR]


                                    Workbooks("XYZ Job Description.xlsx").Activate
                                    Sheets("Name").Select
                                    Sheets("Name").Name = Wksht_Job_Title
                                    ActiveWindow.DisplayVerticalScrollBar = [COLOR=darkblue]True[/COLOR]


                                    Range("A1").Select


                                    ActiveSheet.Protect Password:="XYZ2018", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=False, AllowSorting:=[COLOR=darkblue]False[/COLOR], AllowFiltering:=False
                                    [COLOR=green]'ActiveWorkbook.SaveAs Filename:=ECS_File_Name_Path _
                                     , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
                                     ReadOnlyRecommended:=[COLOR=darkblue]False[/COLOR], CreateBackup:=False[/COLOR]
                                    Sheets(Wksht_Job_Title).Copy After:=Workbooks("XYZ Group Job Desc Workbook.xlsx").Sheets(1)


                                    Windows("XYZ Job Description.xlsx").Activate
                                    ActiveWorkbook.Close SaveChanges:=False


                                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]


                                [COLOR=darkblue]If[/COLOR] grpcounternext = 1 Or grpcounternext = "" [COLOR=darkblue]Then[/COLOR]




                                    Windows("XYZ Group Job Desc Workbook.xlsx").Activate


                                    ActiveWorkbook.SaveAs Filename:=Grp_ECS_Save_Dir & "" & Filename _
                                                        , FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
                                                          ReadOnlyRecommended:=False, CreateBackup:=False


                                    ActiveWorkbook.Close


                                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]


                                CurrRow = CurrRow + 1


                                Workbooks("Create XYZ Job Description Workbooks_2.xlsm").Activate
                                Range("A1").Select


                            [COLOR=darkblue]Next[/COLOR] I




                            Sheets("JD Workbook Requirements").Activate
                            Range("A1").Select


[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Last edited:
Upvote 0
Pro TIP:
Code: 
[color=green]'Instead of this...[/color]
 [color=darkblue]If[/color] something1 [color=darkblue]Then[/color]
 
 [color=darkblue]Else[/color]
    [color=darkblue]If[/color] something2 [color=darkblue]Then[/color]
    
    [color=darkblue]Else[/color]
         [color=darkblue]If[/color] something3 [color=darkblue]Then[/color]
         
         [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
 [color=darkblue]End[/color] [color=darkblue]If[/color]
 
[color=green]'Use this...[/color]
 [color=darkblue]If[/color] something1 [color=darkblue]Then[/color]
 
 [color=darkblue]ElseIf[/color] something2 [color=darkblue]Then[/color]
    
 [color=darkblue]ElseIf[/color] something3 [color=darkblue]Then[/color]
         
 End [color=darkblue]If[/color]
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
244
willow1985
willow1985
F
  • Solved
How to make loop stop when the cell is empty? (VBA)
Replies
3
Views
281
kvsrinivasamurthy
K
S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
286
MARK858
MARK858
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
181
jolivanes
jolivanes
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
594
mumps
mumps

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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