Need help duplicating a page

E

excelnoobhere

Board Regular
Joined
Mar 11, 2019
Messages
61
Hello all,

I have the following code that creates some type of tags, it duplicates the same tag that is on the left to the right side.
I would like this code to duplicate the first page and put in under the first page and then duplicate the second page and put it under the the third page

so it would be first page on pages (1 and 2)
second page on pages (3 and 4)
third page on pages (5 and 6) and so on till the last one.......

any help would be appreciated and thank you in advance.

VBA Code: 
Sub Create_tags()
'cdx: Captive Key Location
'     cdx = 1 --> Tags with Captive Key Location
'     cdx = 0 --> Tags without Captive Key Location
'idx: Vertical number in line of tag
'jdx: Color code
'kdx: Single/Double sided tags;
'     Double sided --> kdx=2
'     Single sided --> kdx=0 1st tag on left
'     Single sided --> kdx=1 2nd tag on right
'wdx: wdx=1 --> Tags with Action & Verify
'     wdx=2 --> Tags Only
'     wdx=3 --> Tags with Source Desc, Action & Verify
Dim i, idx, idx2, jdx, kdx, mdx, wdx, idum, cdx, y As Integer
Dim ch, tag_name, TransFold, g_path, g_file_s, PlacID, version, e_file_s As String
Dim ndx As Boolean
Dim x, lastRow As Long
Dim ws As Worksheet

Application.ScreenUpdating = False
'TagFont_Size.Show
Sheets("Lock Tags").Select
idx = 1r
idx2 = idx
kdx = 2
wdx = 1
cdx = 1

g_path = Sheets("file").Cells(2, 1).Value
g_file_s = Sheets("file").Cells(1, 1).Value
PlacID = Sheets("BG").Cells(11, 22).Value
version = Sheets("Main").Cells(37, 2).Value

'Starts Status Bar
x = 0.001
ufProgress.LabelProgress.Width = 0
ufProgress.Show
Call FractionComplete(x)
x = 0
Sheets("BG").Cells(10, 35).Value = x

If (Sheets("BG").Cells(9, "J").Value = True Or _
    Sheets("BG").Cells(11, "J").Value = True Or _
    Sheets("BG").Cells(13, "J").Value = True) Then
    kdx = 1
End If
If (Sheets("BG").Cells(10, "J").Value = True Or _
    Sheets("BG").Cells(11, "J").Value = True) Then
    wdx = 2
ElseIf (Sheets("BG").Cells(12, "J").Value = True Or _
    Sheets("BG").Cells(13, "J").Value = True) Then
    wdx = 3
    tag_name = Sheets("BG").Cells(13, "V").Value
End If
If Sheets("BG").Cells(5, "J").Value = False Then
    cdx = 0
End If

y = 2
Do Until (Sheets("D").Cells(y, "A").Value = "")
    y = y + 1
Loop
lastRow = (y - 2) * 13 + (Sheets("BG").Cells(20, 30).Value) * (y - 2)
Sheets("BG").Cells(11, 35).Value = lastRow

Do Until (Sheets("D").Cells(idx2 + 1, "A").Value = "")
    ch = Left(Sheets("D").Cells(idx2 + 1, "A").Value, 1)
    If (ch <> "D") Then
        If (kdx = 1) Then
            kdx = 0
        ElseIf (kdx = 0) Then
            kdx = 1
        End If
        If (kdx = 2) Then
            mdx = idx
        ElseIf (kdx = 0) Then
            mdx = (idx + 1) / 2
        ElseIf (kdx = 1) Then
            mdx = idx / 2
        End If
        If (wdx = 1) Then
            If ((mdx Mod 2) = 1) Then
                ndx = True
            Else
                ndx = False
            End If
        ElseIf (wdx = 2) Then
            If ((mdx Mod 3) = 1) Then
                ndx = True
            Else
                ndx = False
            End If
        ElseIf (wdx = 3) Then
            ndx = True
        End If
   
        If (ch = "E") Then
            jdx = 1
        ElseIf (ch = "A") Then
            jdx = 2
        ElseIf (ch = "G") Then
            jdx = 3
        ElseIf (ch = "W") Then
            jdx = 4
        ElseIf (ch = "C") Then
            jdx = 5
        ElseIf (ch = "H") Then
            jdx = 6
        ElseIf (ch = "N") Then
            jdx = 7
        ElseIf (ch = "R") Then
            jdx = 8
        ElseIf (ch = "M") Then
            jdx = 9
        ElseIf (ch = "S") Then
            jdx = 10
        ElseIf (ch = "T") Then
            jdx = 11
        End If
        Call lock_format(idx2, mdx, jdx, kdx, ndx, wdx, tag_name, idx, kdx, cdx)
        If (Sheets("D").Cells(idx2 + 1, "P").Value = True) Then
            If (kdx <> 2) Then
                Call lock_star(mdx, kdx + 1, wdx, idx2, cdx)
            Else
                Call lock_star(mdx, 1, wdx, idx2, cdx)
                Call lock_star(mdx, 2, wdx, idx2, cdx)
            End If
        End If
        If (kdx <> 2) Then
'            Call black_arc(mdx, kdx + 1, wdx)
        Else
'            Call black_arc(mdx, 1, wdx)
'            Call black_arc(mdx, 2, wdx)
        End If
        idx = idx + 1
    End If
    idx2 = idx2 + 1
    lastRow = Sheets("BG").Cells(11, 35).Value
    x = Sheets("BG").Cells(10, 35).Value
    x = x + (13 / lastRow)
    Sheets("BG").Cells(10, 35).Value = x
    Call FractionComplete(x)
Loop
Application.CutCopyMode = False
ActiveWindow.Zoom = 75
ActiveWindow.Zoom = 100
ActiveWindow.Zoom = 75
ActiveWindow.Zoom = 55
ActiveWindow.Zoom = 75
If (wdx = 1) And (kdx = 2) Then
    range("A1:J" & CStr((idx - 1) * 15)).Select
    ActiveSheet.PageSetup.PrintArea = "A1:J" & CStr((idx - 1) * 15)
ElseIf (wdx = 1) And (kdx <> 2) Then
    range("A1:J" & CStr((idx - kdx) / 2 * 15)).Select
    ActiveSheet.PageSetup.PrintArea = "A1:J" & CStr((idx - kdx) / 2 * 15)
ElseIf (wdx = 2) And (kdx = 2) Then
    range("A1:J" & CStr((idx - 1) * 9)).Select
    ActiveSheet.PageSetup.PrintArea = "A1:J" & CStr((idx - 1) * 9)
ElseIf (wdx = 2) And (kdx <> 2) Then
    range("A1:J" & CStr((idx - kdx) / 2 * 9)).Select
    ActiveSheet.PageSetup.PrintArea = "A1:J" & CStr((idx - kdx) / 2 * 9)
ElseIf (wdx = 3) And (kdx = 2) And (cdx = 1) Then
    range("A1:J" & CStr((idx - 1) * 16)).Select
    ActiveSheet.PageSetup.PrintArea = "A1:J" & CStr((idx - 1) * 16)
ElseIf (wdx = 3) And (kdx <> 2) And (cdx = 1) Then
    range("A1:J" & CStr((idx - kdx) / 2 * 16)).Select
    ActiveSheet.PageSetup.PrintArea = "A1:J" & CStr((idx - kdx) / 2 * 16)
ElseIf (wdx = 3) And (kdx = 2) And (cdx = 0) Then
    range("A1:J" & CStr((idx - 1) * 13)).Select
    ActiveSheet.PageSetup.PrintArea = "A1:J" & CStr((idx - 1) * 13)
ElseIf (wdx = 3) And (kdx <> 2) And (cdx = 0) Then
    range("A1:J" & CStr((idx - kdx) / 2 * 13)).Select
    ActiveSheet.PageSetup.PrintArea = "A1:J" & CStr((idx - kdx) / 2 * 13)
End If

i = idx - 1
Sheets("BG").Cells(10, 40).Value = i
   
'Re-inserted, will need to test
If Sheets("BG").Cells(3, 30).Value = True Or Sheets("BG").Cells(4, 30).Value = True _
    Or Sheets("BG").Cells(5, 30).Value = True Or Sheets("BG").Cells(6, 30).Value = True _
    Or Sheets("BG").Cells(7, 30).Value = True Or Sheets("BG").Cells(8, 30).Value = True _
    Or Sheets("BG").Cells(9, 30).Value = True Then
    Call TranslateLockTags(i)
End If
'Re-inserted ^^

On Error Resume Next
   
If Sheets("BG").Cells(2, 30).Value = True Then

    Application.DisplayAlerts = False
    Workbooks.Add
    'ActiveWorkbook.SaveAs Filename:=g_path & "\gPlac_" & PlacID & "_LockTags_ENGLISH_" & Format(Date, "YYMMDD")
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Worksheets("file").Cells(41, 1).Value
    e_file_s = ActiveWorkbook.Name
    Application.DisplayAlerts = True

    Workbooks(g_file_s).Activate
    For Each ws In ActiveWorkbook.Worksheets
        'If ws.Name = "Lock Tags" Or ws.Name = "Sign" Then
        If ws.Name = "Lock Tags" Then
            ws.Copy _
                Before:=Workbooks(e_file_s).Sheets("Sheet1")
        End If
        Workbooks(g_file_s).Activate
    Next ws
    Workbooks(e_file_s).Activate
   
    For Each ws In Workbooks(g_file_s).Worksheets
        If ws.Name = "Lock Tags" Then
            ws.Copy _
                Before:=Workbooks(e_file_s).Sheets("Sheet1")
        End If
    Next ws
   
   
   
    Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
    Sheets("Sheet2").Delete
    Sheets("Sheet3").Delete
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Workbooks(g_file_s).Activate
End If

Unload ufProgress

If (idx = 1) Then
    MsgBox ("There is no tag created since you did not select anything.")
Else
    MsgBox (CStr(idx - 1) & " tag(s) had been created based on what you selected.")
End If

Application.ScreenUpdating = True

'Sheets("Main").Activate
End Sub
Sub lock_star(i, j, wdx, idx2, cdx)
Dim x, y As Double
Dim no As Integer

Application.ScreenUpdating = False
x = 173 + (j - 1) * 250
If (wdx = 1) Then
    y = 20 + (i - 1) * 350.27
ElseIf (wdx = 2) Then
    y = 22 + (i - 1) * 188.3
ElseIf (wdx = 3 And cdx = 1) Then
    y = 12 + (i - 1) * 392.3
ElseIf (wdx = 3 And cdx = 0) Then
    y = 12 + (i - 1) * 351.75
End If
    ActiveSheet.Shapes.AddShape(msoShape5pointStar, x, y, 40, 38).Select
    Select Case Sheets("D").Cells(idx2 + 1, "s").Value
       
        'chooses yellow
       
            Case True
                Selection.ShapeRange.Fill.Visible = msoTrue
                Selection.ShapeRange.Fill.Solid
                Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
                Selection.ShapeRange.Fill.Transparency = 0#
                Selection.ShapeRange.Line.Weight = 0.75
                Selection.ShapeRange.Line.DashStyle = msoLineSolid
                Selection.ShapeRange.Line.Style = msoLineSingle
                Selection.ShapeRange.Line.Transparency = 0#
                Selection.ShapeRange.Line.Visible = msoTrue
                Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
                Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
               
        'chooses black
           
            Case False
                Selection.ShapeRange.Fill.Visible = msoTrue
                Selection.ShapeRange.Fill.Solid
                Selection.ShapeRange.Fill.ForeColor.SchemeColor = 0
                Selection.ShapeRange.Fill.Transparency = 0#
                Selection.ShapeRange.Line.Weight = 1
                Selection.ShapeRange.Line.DashStyle = msoLineSolid
                Selection.ShapeRange.Line.Style = msoLineSingle
                Selection.ShapeRange.Line.Transparency = 0#
                Selection.ShapeRange.Line.Visible = msoTrue
                Selection.ShapeRange.Line.ForeColor.ObjectThemeColor = msoThemeColorBackground1
                Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
               
            Case Else: MsgBox ("star color not found")
   
    End Select

    With Selection
        .Placement = xlMove
        .PrintObject = True
    End With
End Sub

Sub lock_format(i, m, j, k, new_page, wdx, tag_name, idx, kdx, cdx)
Dim iline, interior_color, font_color As Integer
Dim nb, nc, nd As Integer
Dim lng1, lng2 As Integer
Dim a, b, c As String
Dim x As String * 1

Application.ScreenUpdating = False
font_color = 1
If (j = 1) Then
    interior_color = 45
ElseIf (j = 2) Then
    interior_color = 41
    font_color = 2
ElseIf (j = 3) Then
    interior_color = 1
    font_color = 2
ElseIf (j = 4) Then
    interior_color = 10
    font_color = 2
ElseIf (j = 5) Then
    interior_color = 6
ElseIf (j = 6) Then
    interior_color = 9
    font_color = 2
ElseIf (j = 7) Then
    interior_color = 7
ElseIf (j = 8) Then
    interior_color = 13
    font_color = 2
ElseIf (j = 9) Then
    interior_color = 40
ElseIf (j = 10) Then
    interior_color = 15
ElseIf (j = 11) Then
    interior_color = 10
    font_color = 2
End If

If (k = 0 Or k = 2) Then
    nb = 2
    nc = 3
    nd = 4
ElseIf (k = 1) Then
    nb = 7
    nc = 8
    nd = 9
End If
If (wdx = 1) Then
    iline = (m - 1) * 15 + 1
ElseIf (wdx = 2) Then
    iline = (m - 1) * 8 + 1
ElseIf (wdx = 3 And cdx = 1) Then
    iline = (m - 1) * 16 + 1
ElseIf (wdx = 3 And cdx = 0) Then
    iline = (m - 1) * 13 + 1
End If
If (m = 1 And i = 1) Then
    Cells(1, 1).ColumnWidth = 0
    Cells(1, 2).ColumnWidth = 2.14
    Cells(1, 3).ColumnWidth = 34.86
    Cells(1, 4).ColumnWidth = 2.14
    Cells(1, 5).ColumnWidth = 2.57
    Cells(1, 6).ColumnWidth = 2.57
    Cells(1, 7).ColumnWidth = 2.14
    Cells(1, 8).ColumnWidth = 34.86
    Cells(1, 9).ColumnWidth = 2.14
    Cells(1, 10).ColumnWidth = 2.57
    Columns("E:E").Select
    Selection.Borders(xlEdgeRight).LineStyle = xlDash
    Columns("J:J").Select
    Selection.Borders(xlEdgeRight).LineStyle = xlDash
End If
If (k = 0 Or k = 2) Then
    If (new_page = True) Then
        If (wdx = 1) Then
            Cells(iline, 1).RowHeight = 10
            Cells(iline + 1, 1).RowHeight = 10
            Cells(iline + 2, 1).RowHeight = 59.25
            Cells(iline + 3, 1).RowHeight = 18
            Cells(iline + 4, 1).RowHeight = 18
            Cells(iline + 5, 1).RowHeight = 18
            Cells(iline + 6, 1).RowHeight = 5.5
            Cells(iline + 7, 1).RowHeight = 18
            Cells(iline + 8, 1).RowHeight = 72
            Cells(iline + 9, 1).RowHeight = 5.5
            Cells(iline + 10, 1).RowHeight = 18
            Cells(iline + 11, 1).RowHeight = 72
            Cells(iline + 12, 1).RowHeight = 7.5
            Cells(iline + 13, 1).RowHeight = 10
            Cells(iline + 14, 1).RowHeight = 10
           
            Cells(iline + 15, 1).RowHeight = 10
            Cells(iline + 16, 1).RowHeight = 10
            Cells(iline + 17, 1).RowHeight = 59.25
            Cells(iline + 18, 1).RowHeight = 18
            Cells(iline + 19, 1).RowHeight = 18
            Cells(iline + 20, 1).RowHeight = 18
            Cells(iline + 21, 1).RowHeight = 5.5
            Cells(iline + 22, 1).RowHeight = 18
            Cells(iline + 23, 1).RowHeight = 72
            Cells(iline + 24, 1).RowHeight = 5.5
            Cells(iline + 25, 1).RowHeight = 18
            Cells(iline + 26, 1).RowHeight = 72
            Cells(iline + 27, 1).RowHeight = 7.5
            Cells(iline + 28, 1).RowHeight = 10
            Cells(iline + 29, 1).RowHeight = 10
            Rows(iline + 14).Select
            Selection.Borders(xlEdgeBottom).LineStyle = xlDash
            Rows(iline + 29).Select
            Selection.Borders(xlEdgeBottom).LineStyle = xlDash
        ElseIf (wdx = 2) Then
            Cells(iline, 1).RowHeight = 10
            'Cells(iline + 1, 1).RowHeight = 72
            Cells(iline + 1, 1).RowHeight = 10
            Cells(iline + 2, 1).RowHeight = 59.25
            Cells(iline + 3, 1).RowHeight = 31
            Cells(iline + 4, 1).RowHeight = 31
            Cells(iline + 5, 1).RowHeight = 31
            Cells(iline + 6, 1).RowHeight = 7.5
            Cells(iline + 7, 1).RowHeight = 10
            Cells(iline + 8, 1).RowHeight = 10
            'Cells(iline + 9, 1).RowHeight = 72
            Cells(iline + 9, 1).RowHeight = 10
            Cells(iline + 10, 1).RowHeight = 59.25
            Cells(iline + 11, 1).RowHeight = 31
            Cells(iline + 12, 1).RowHeight = 31
            Cells(iline + 13, 1).RowHeight = 31
            Cells(iline + 14, 1).RowHeight = 7.5
            Cells(iline + 15, 1).RowHeight = 10
            Cells(iline + 16, 1).RowHeight = 10
            'Cells(iline + 17, 1).RowHeight = 72
            Cells(iline + 17, 1).RowHeight = 10
            Cells(iline + 18, 1).RowHeight = 59.25
            Cells(iline + 19, 1).RowHeight = 31
            Cells(iline + 20, 1).RowHeight = 31
            Cells(iline + 21, 1).RowHeight = 31
            Cells(iline + 22, 1).RowHeight = 7.5
            Cells(iline + 23, 1).RowHeight = 10
            Rows(iline + 7).Select
            Selection.Borders(xlEdgeBottom).LineStyle = xlDash
            Rows(iline + 15).Select
            Selection.Borders(xlEdgeBottom).LineStyle = xlDash
            Rows(iline + 23).Select
            Selection.Borders(xlEdgeBottom).LineStyle = xlDash
        ElseIf (wdx = 3 And cdx = 1) Then
            Cells(iline, 1).RowHeight = 9
            Cells(iline + 1, 1).RowHeight = 59.25
            Cells(iline + 2, 1).RowHeight = 38
            Cells(iline + 3, 1).RowHeight = 18
            Cells(iline + 4, 1).RowHeight = 18
            Cells(iline + 5, 1).RowHeight = 5
            Cells(iline + 6, 1).RowHeight = 18
            Cells(iline + 7, 1).RowHeight = 18
            Cells(iline + 8, 1).RowHeight = 5
            Cells(iline + 9, 1).RowHeight = 18
            Cells(iline + 10, 1).RowHeight = 69
            Cells(iline + 11, 1).RowHeight = 5
            Cells(iline + 12, 1).RowHeight = 18
            Cells(iline + 13, 1).RowHeight = 69
            Cells(iline + 14, 1).RowHeight = 18
            Cells(iline + 15, 1).RowHeight = 9
            Rows(iline + 15).Select
            Selection.Borders(xlEdgeBottom).LineStyle = xlDash
        ElseIf (wdx = 3 And cdx = 0) Then
            Cells(iline, 1).RowHeight = 9
            Cells(iline + 1, 1).RowHeight = 59.25
            Cells(iline + 2, 1).RowHeight = 38
            Cells(iline + 3, 1).RowHeight = 18
            Cells(iline + 4, 1).RowHeight = 18
            Cells(iline + 5, 1).RowHeight = 5
            Cells(iline + 6, 1).RowHeight = 18
            Cells(iline + 7, 1).RowHeight = 69
            Cells(iline + 8, 1).RowHeight = 5
            Cells(iline + 9, 1).RowHeight = 18
            Cells(iline + 10, 1).RowHeight = 69
            Cells(iline + 11, 1).RowHeight = 18
            Cells(iline + 12, 1).RowHeight = 9
            Rows(iline + 12).Select
            Selection.Borders(xlEdgeBottom).LineStyle = xlDash
        End If
    End If
End If

If (wdx = 1) Then
    range(Cells(iline + 2, nb), Cells(iline + 2, nd)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        If (Sheets("BG").Cells(25, "J").Value = True) Then
            .Size = 72
        ElseIf (Sheets("BG").Cells(24, "J").Value = True) Then
            .Size = 64
        ElseIf (Sheets("BG").Cells(23, "J").Value = True) Then
            .Size = 56
        Else
            .Size = 48
        End If
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = font_color
    End With
    With Selection.Interior
        .ColorIndex = interior_color
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
   
    range(Cells(iline + 3, nb), Cells(iline + 3, nd)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = font_color
    End With
    With Selection.Interior
        .ColorIndex = interior_color
    End With
    range(Cells(iline + 3, nb), Cells(iline + 3, nd)).Select
    Selection.Copy
    Cells(iline + 4, nb).Select
    ActiveSheet.Paste
    Cells(iline + 5, nb).Select
    ActiveSheet.Paste
    Cells(iline + 6, nb).Select
    ActiveSheet.Paste
   
    range(Cells(iline + 3, nb), Cells(iline + 6, nd)).Select
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
   
    range(Cells(iline + 7, nb), Cells(iline + 12, nb)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    Selection.Interior.ColorIndex = interior_color
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
   
    range(Cells(iline + 7, nd), Cells(iline + 12, nd)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    Selection.Interior.ColorIndex = interior_color
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    Cells(iline + 9, nc).Select
    Selection.Interior.ColorIndex = interior_color
    Cells(iline + 12, nc).Select
    Selection.Interior.ColorIndex = interior_color
   
    Cells(iline + 7, nc).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
   
    Cells(iline + 8, nc).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
   
    Cells(iline + 12, nc).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
   
    range(Cells(iline + 7, nc), Cells(iline + 8, nc)).Select
    Selection.Copy
    Cells(iline + 10, nc).Select
    ActiveSheet.Paste
   
    Cells(iline + 2, nb).Value = Sheets("D").Cells(i + 1, "A").Value
    Cells(iline + 3, nb).Value = Sheets("D").Cells(i + 1, "D").Value
    Cells(iline + 4, nb).Value = Sheets("D").Cells(i + 1, "C").Value
    Cells(iline + 5, nb).Value = Sheets("D").Cells(i + 1, "B").Value
    'Cells(iline + 7, nc).Value = "PERFORM ACTION:"
    If Sheets("D").range("BI11").Value <> "" Then
        Cells(iline + 7, nc).Value = Sheets("D").range("BI12").Value & ":"
    Else
        Cells(iline + 7, nc).Value = "PERFORM ACTION:"
    End If
    'end of ML
    Cells(iline + 8, nc).Value = Sheets("D").Cells(i + 1, "G").Value
    'Cells(iline + 10, nc).Value = "YOU MUST VERIFY:"
    If Sheets("D").range("BI12").Value <> "" Then
        Cells(iline + 10, nc).Value = Sheets("D").range("BI13").Value & ":"
    Else
        Cells(iline + 10, nc).Value = "YOU MUST VERIFY:"
    End If
    'end of ML
    If Sheets("D").Cells(i + 1, "H").Value = "" Then
        Cells(iline + 11, nc).Value = Sheets("D").Cells(i + 1, "H").Value
    Else
        a = Sheets("D").Cells(i + 1, "H").Value
        lng1 = Len(a) + 2
        b = "LOCK BOX LOC AT: " & Sheets("BG").Cells(62, "V").Value
        lng2 = Len(b)
        Cells(iline + 11, nc).Value = a & Chr(10) & b
        Cells(iline + 11, nc).Characters(Start:=lng1, Length:=lng2).Font.Color = -4165632
        Cells(iline + 11, nc).Characters(Start:=lng1, Length:=lng2).Font.Size = 9
        Cells(iline + 11, nc).Characters(Start:=lng1, Length:=lng2).Font.Bold = True
    End If
    If (k = 2) Then
        range("B" & CStr(iline + 2) & ":D" & CStr(iline + 12)).Select
        Selection.Copy
        range("G" & CStr(iline + 2)).Select
        ActiveSheet.Paste
    End If
   
    If (k <> 0) Then
        Cells(iline + 15, "K").Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
    End If
   
ElseIf (wdx = 2) Then
    range(Cells(iline + 2, nb), Cells(iline + 2, nd)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        If (Sheets("BG").Cells(25, "J").Value = True) Then
            .Size = 72
        ElseIf (Sheets("BG").Cells(24, "J").Value = True) Then
            .Size = 64
        ElseIf (Sheets("BG").Cells(23, "J").Value = True) Then
            .Size = 56
        Else
            .Size = 48
        End If
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = font_color
    End With
    With Selection.Interior
        .ColorIndex = interior_color
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
   
    range(Cells(iline + 3, nb), Cells(iline + 3, nd)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = font_color
    End With
    With Selection.Interior
        .ColorIndex = interior_color
    End With
    range(Cells(iline + 3, nb), Cells(iline + 3, nd)).Select
    Selection.Copy
    Cells(iline + 4, nb).Select
    ActiveSheet.Paste
    Cells(iline + 5, nb).Select
    ActiveSheet.Paste
   
    range(Cells(iline + 3, nb), Cells(iline + 5, nd)).Select
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
   
    range(Cells(iline + 6, nb), Cells(iline + 6, nd)).Select
    With Selection.Interior
        .ColorIndex = interior_color
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
   
    Cells(iline + 2, nb).Value = Sheets("D").Cells(i + 1, "A").Value
    Cells(iline + 3, nb).Value = Sheets("D").Cells(i + 1, "D").Value
    Cells(iline + 4, nb).Value = Sheets("D").Cells(i + 1, "C").Value
    Cells(iline + 5, nb).Value = Sheets("D").Cells(i + 1, "B").Value
   
    If (k = 2) Then
        range("B" & CStr(iline) & ":D" & CStr(iline + 6)).Select
        Selection.Copy
        range("G" & CStr(iline)).Select
        ActiveSheet.Paste
    End If
   
    If (k <> 0) Then
        Cells(iline + 16, "K").Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
    End If
   
ElseIf (wdx = 3 And cdx = 1) Then
    range(Cells(iline + 1, nb), Cells(iline + 1, nd)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        If (Sheets("BG").Cells(25, "J").Value = True) Then
            .Size = 72
        ElseIf (Sheets("BG").Cells(24, "J").Value = True) Then
            .Size = 64
        ElseIf (Sheets("BG").Cells(23, "J").Value = True) Then
            .Size = 56
        Else
            .Size = 48
        End If
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = font_color
    End With
    With Selection.Interior
        .ColorIndex = interior_color
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
   
    range(Cells(iline + 2, nb), Cells(iline + 14, nb)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    Selection.Interior.ColorIndex = interior_color
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
   
    range(Cells(iline + 2, nd), Cells(iline + 14, nd)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    Selection.Interior.ColorIndex = interior_color
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    Cells(iline + 2, nc).Select
    Selection.Interior.ColorIndex = interior_color
    Selection.Font.ColorIndex = font_color
    Cells(iline + 5, nc).Select
    Selection.Interior.ColorIndex = interior_color
    Cells(iline + 8, nc).Select
    Selection.Interior.ColorIndex = interior_color
    Cells(iline + 11, nc).Select
    Selection.Interior.ColorIndex = interior_color
    Cells(iline + 14, nc).Select
    Selection.Interior.ColorIndex = interior_color
    Selection.Font.ColorIndex = font_color
    Selection.Borders(xlEdgeBottom).Weight = xlThin
   
    Cells(iline + 2, nc).Select
    Selection.Font.FontStyle = "Bold"
    Selection.Font.Size = 10
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Cells(iline + 3, nc).Select
    Selection.Font.FontStyle = "Bold"
    Selection.Font.Size = 14
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 4, nc).Select
    Selection.WrapText = True
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 12
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 6, nc).Select
    Selection.Font.FontStyle = "Bold"
    Selection.Font.Size = 14
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 7, nc).Select
    Selection.WrapText = True
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 12
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 9, nc).Select
    Selection.Font.FontStyle = "Bold"
    Selection.Font.Size = 14
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 10, nc).Select
    Selection.WrapText = True
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 12
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 12, nc).Select
    Selection.Font.FontStyle = "Bold"
    Selection.Font.Size = 14
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 13, nc).Select
    Selection.WrapText = True
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 12
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 14, nc).Select
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 10
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.NumberFormat = "m/d/yy"
    Cells(iline + 15, nc).Select
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 10
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
   
    Cells(iline + 1, nb).Value = Sheets("D").Cells(i + 1, "A").Value
    Cells(iline + 2, nc).Value = Sheets("D").Cells(i + 1, "D").Value & Chr(10) & _
                                 Sheets("D").Cells(i + 1, "C").Value & Chr(10) & _
                                 Sheets("D").Cells(i + 1, "B").Value
'    'Cells(iline + 3, nc).Value = "SOURCE DESCRIPTION:"
'    If Sheets("D").Range("BI10").value <> "" Then
'        Cells(iline + 3, nc).value = Sheets("D").Range("BI10").value
'    Else
'        Cells(iline + 3, nc).value = "Fed By:"
'        'Cells(iline + 3, nc).Value = "Primary Energy Source:"
'    End If
   
    'Fix so the Lock Tag always receives Fed By: instead of source description
    Cells(iline + 3, nc).Value = "Fed By:"
   
   
    'end of ML
    Cells(iline + 4, nc).Value = Sheets("D").Cells(i + 1, "I")
    If Sheets("D").range("BI27").Value <> "" Then
        Cells(iline + 6, nc).Value = Sheets("D").range("BI27").Value
    Else
        Cells(iline + 6, nc).Value = "Captive Key Location:"
    End If
    'end of ML

    Cells(iline + 7, nc).Value = Sheets("BG").Cells(7, "V").Value

    If Sheets("D").range("BI1").Value <> "" Then
        Cells(iline + 9, nc).Value = Sheets("D").range("BI12").Value & ":"
    Else
        Cells(iline + 9, nc).Value = "PERFORM ACTION:"
    End If
    'end of ML
    Cells(iline + 10, nc).Value = Sheets("D").Cells(i + 1, "G").Value

    If Sheets("D").range("BI13").Value <> "" Then
        Cells(iline + 12, nc).Value = Sheets("D").range("BI13").Value & ":"
    Else
        Cells(iline + 12, nc).Value = "YOU MUST VERIFY:"
    End If
    'end of ML

    Cells(iline + 13, nc).Value = Sheets("D").Cells(i + 1, "H").Value
     
    Cells(iline + 14, nc).Value = Sheets("BG").Cells(8, "V").Value
    If (tag_name <> "") Then
        Cells(iline + 15, nc).Value = tag_name
    End If
   
    If (k = 2) Then
        range("B" & CStr(iline + 1) & ":D" & CStr(iline + 12)).Select
        Selection.Copy
        range("G" & CStr(iline + 1)).Select
        ActiveSheet.Paste
    End If
    If (k <> 0) And (idx Mod 2 = 1) And (kdx = 2) Then
        Cells(iline + 32, "K").Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
    ElseIf (k <> 0) And (idx Mod 4 = 2) And (kdx <> 2) Then
        Cells(iline + 32, "K").Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
    End If

ElseIf (wdx = 3 And cdx = 0) Then
    range(Cells(iline + 1, nb), Cells(iline + 1, nd)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        If (Sheets("BG").Cells(25, "J").Value = True) Then
            .Size = 72
        ElseIf (Sheets("BG").Cells(24, "J").Value = True) Then
            .Size = 64
        ElseIf (Sheets("BG").Cells(23, "J").Value = True) Then
            .Size = 56
        Else
            .Size = 48
        End If
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = font_color
    End With
    With Selection.Interior
        .ColorIndex = interior_color
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
   
    range(Cells(iline + 2, nb), Cells(iline + 11, nb)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    Selection.Interior.ColorIndex = interior_color
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
   
    range(Cells(iline + 2, nd), Cells(iline + 11, nd)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = True
        .MergeCells = True
    End With
    Selection.Interior.ColorIndex = interior_color
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    Cells(iline + 2, nc).Select
    Selection.Interior.ColorIndex = interior_color
    Selection.Font.ColorIndex = font_color
    Cells(iline + 5, nc).Select
    Selection.Interior.ColorIndex = interior_color
    Cells(iline + 8, nc).Select
    Selection.Interior.ColorIndex = interior_color
    Cells(iline + 11, nc).Select
    Selection.Interior.ColorIndex = interior_color
    Selection.Font.ColorIndex = font_color
    Selection.Borders(xlEdgeBottom).Weight = xlThin
   
    Cells(iline + 2, nc).Select
    Selection.Font.FontStyle = "Bold"
    Selection.Font.Size = 10
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Cells(iline + 3, nc).Select
    Selection.Font.FontStyle = "Bold"
    Selection.Font.Size = 14
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 4, nc).Select
    Selection.WrapText = True
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 12
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 6, nc).Select
    Selection.Font.FontStyle = "Bold"
    Selection.Font.Size = 14
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 7, nc).Select
    Selection.WrapText = True
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 12
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 9, nc).Select
    Selection.Font.FontStyle = "Bold"
    Selection.Font.Size = 14
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 10, nc).Select
    Selection.WrapText = True
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 12
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Borders(xlEdgeLeft).Weight = xlThin
    Selection.Borders(xlEdgeTop).Weight = xlThin
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    Selection.Borders(xlEdgeRight).Weight = xlThin
    Cells(iline + 11, nc).Select
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 10
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.NumberFormat = "m/d/yy"
    Cells(iline + 12, nc).Select
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 10
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
   
    Cells(iline + 1, nb).Value = Sheets("D").Cells(i + 1, "A").Value
    Cells(iline + 2, nc).Value = Sheets("D").Cells(i + 1, "D").Value & Chr(10) & _
                                 Sheets("D").Cells(i + 1, "C").Value & Chr(10) & _
                                 Sheets("D").Cells(i + 1, "B").Value

    Cells(iline + 3, nc).Value = "Fed By:"
   
    'end of ML
    Cells(iline + 4, nc).Value = Sheets("D").Cells(i + 1, "I")

    If Sheets("D").range("BI1").Value <> "" Then
        Cells(iline + 6, nc).Value = Sheets("D").range("BI12").Value & ":"
    Else
        Cells(iline + 6, nc).Value = "PERFORM ACTION:"
    End If
    'end of ML
    Cells(iline + 7, nc).Value = Sheets("D").Cells(i + 1, "G").Value

    If Sheets("D").range("BI13").Value <> "" Then
        Cells(iline + 9, nc).Value = Sheets("D").range("BI13").Value & ":"
    Else
        Cells(iline + 9, nc).Value = "YOU MUST VERIFY:"
    End If
    'end of ML

    Cells(iline + 10, nc).Value = Sheets("D").Cells(i + 1, "H").Value
     
    Cells(iline + 11, nc).Value = Sheets("BG").Cells(8, "V").Value
    If (tag_name <> "") Then
        Cells(iline + 12, nc).Value = tag_name
    End If
   
    If (k = 2) Then
        range("B" & CStr(iline + 1) & ":D" & CStr(iline + 12)).Select
        Selection.Copy
        range("G" & CStr(iline + 1)).Select
        ActiveSheet.Paste
    End If
    If (k <> 0) And (idx Mod 2 = 1) And (kdx = 2) Then
        Cells(iline + 26, "K").Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
    ElseIf (k <> 0) And (idx Mod 4 = 2) And (kdx <> 2) Then
        Cells(iline + 26, "K").Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
    End If
End If


Cells(iline + 3, 1).Select
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
one other way to do it is is instead of duplicating on the right side, it would duplicate on the next page so i can print them double sided with the same tag on the front and back
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
299
Micron
M
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
576
RoryA
RoryA
P
  • Question Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
360
6StringJazzer
6StringJazzer
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K
H
  • Question Question
Save and Close Excel after a 15min inactivity.
Replies
4
Views
710
Hopey87
H

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