How do I make the 2nd macro execute the formatting and pasting on the subsequent values that is added by the 1st macro.

Smilin

Board Regular
Joined
Nov 28, 2019
Messages
67
Platform
  1. Windows
I have 2 macros, “hailshort” and “sort”

Hail short will updated my master sheet with 16 lines, pasting under the last row.

My “sort” macro is designed to sort and format the 16 lines that get posted. However, it doesn’t execute the formatting on the 2nd set of 16 lines. It will do it on the 1st 16 lines of my master sheet as that is where I created the macro, but it wont format/sort the 2nd set of 16 lines. I tried combining the two macros but it doesn’t work.

The 2 macros are:
Sub hailshort()
'define source range
Dim Lastrow As Long

With Worksheets("Macro")
Lastrow = .Range("E" & Rows.Count).End(xlUp).Row + 1
Worksheets("hail").Range("N2:N17").Copy
.Range("C" & Lastrow).PasteSpecial Paste:=xlValues

Worksheets("hail").Range("C2:C17").Copy
.Range("D" & Lastrow).PasteSpecial Paste:=xlValues

Worksheets("hail").Range("E2:E17").Copy
.Range("E" & Lastrow).PasteSpecial Paste:=xlValues

Worksheets("hail").Range("D2:D17").Copy
.Range("F" & Lastrow).PasteSpecial Paste:=xlValues
End With
End Sub
____________________________________________________________
Sub sort()
'
' sort Macro
Range("J2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "x"
Range("J4").Select
ActiveCell.FormulaR1C1 = "x"
Range("J6").Select
ActiveCell.FormulaR1C1 = "x"
Range("J8").Select
ActiveCell.FormulaR1C1 = "x"
Range("J10").Select
ActiveCell.FormulaR1C1 = "x"
Range("J12").Select
ActiveCell.FormulaR1C1 = "x"
Range("J14").Select
ActiveCell.FormulaR1C1 = "x"
Range("J16").Select
ActiveWindow.SmallScroll Down:=-18
Range("J16").Select
ActiveCell.FormulaR1C1 = "x"
Columns("C:J").Select
ActiveWorkbook.Worksheets("Macro").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Macro").sort.SortFields.Add2 Key:=Range("J2:J18") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Macro").sort
.SetRange Range("C1:J18")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C2:I17").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Range("J2:J9").Select
Selection.ClearContents
Range("D2:D17").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 2
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("F2:F17").Select
Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####"
Range("H2:H17").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Range("B2").Select
ActiveCell.FormulaR1C1 = "8:00 AM"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B5"), Type:=xlFillDefault
Range("B2:B5").Select
Range("B6").Select
ActiveCell.FormulaR1C1 = "1:00 PM"
Range("B6").Select
Selection.AutoFill Destination:=Range("B6:B9"), Type:=xlFillDefault
Range("B6:B9").Select
Range("B2:B9").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
Range("B2:G9").Select
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15071487
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B10:G17").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15648990
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("E21").Select
Range("A2:A9").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15071487
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A10:A17").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15648990
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("E25").Select
End Sub

How do I make the 2nd macro execute the formatting and pasting on each subsequent values that is added by the 1st macro? Can you help?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,855
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
The first 'With' statement in your Sort routine has:

VBA Code:
    With ActiveWorkbook.Worksheets("Macro").sort
        .SetRange Range("C1:J18")

Is that C1:J18 correct? Should it be C2:J18?
I asked about C1 or C2, not J1 or J2.

VBA Code:
    Public LastrowPlus1 As Long


Sub hailshort()
'
'   define source range
'
..........

Assuming the C1:J18 is correct, Try the code above to see if it does what you want.

Make sure you have that top line at the very top of your module. That will make it so the the LastRow variable will work in both of your routines.

Did you do that part?

If so, step through the second subroutine to verify the LastRowPlus1 variable still contains the proper value from the first subroutine.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Smilin

Board Regular
Joined
Nov 28, 2019
Messages
67
Platform
  1. Windows
I asked about C1 or C2, not J1 or J2.



Did you do that part?

If so, step through the second subroutine to verify the LastRowPlus1 variable still contains the proper value from the first subroutine.
 

Smilin

Board Regular
Joined
Nov 28, 2019
Messages
67
Platform
  1. Windows
Hi Johnny, I ran a msgbox and the variable is 2. Same variable as in the 1st part "shorthail" macro. c1 is correct. Not sure if this would make a difference, but column I is empty.
 

Smilin

Board Regular
Joined
Nov 28, 2019
Messages
67
Platform
  1. Windows
Hi Johnny, I ran a msgbox and the variable is 2. Same variable as in the 1st part "shorthail" macro. c1 is correct. Not sure if this would make a difference, but column I is empty.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,855
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

@Smilin perhaps my testing was different than yours. Can you upload a version of the workbook that you are using that doesn't have any sensitive in it so we can see exactly how you have set up everything? You can upload the workbook to a place of your choosing, I normally use this link, but it is your choice, just make sure you provide us with a link to download the workbook,
 

Smilin

Board Regular
Joined
Nov 28, 2019
Messages
67
Platform
  1. Windows
@Smilin perhaps my testing was different than yours. Can you upload a version of the workbook that you are using that doesn't have any sensitive in it so we can see exactly how you have set up everything? You can upload the workbook to a place of your choosing, I normally use this link, but it is your choice, just make sure you provide us with a link to download the workbook,
I uploaded the worksheet to the link you provided, but I am not sure how you will be able to access it. I havent used this before. My file is called hail test. If you are able to access it, the one thing i notices, is that column A & B doesnt have borders after I ran the Sort macro. Can you work that in ? . The headers I will insert each time I ran the hailshort macro. Thank you.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,855
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

When you upload the file it gives you a link to copy and paste. The link will have a bunch of random letters on the end of it.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,855
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Anyways, I took another look at the code and I found 3 problems:
1) I didn't put the call from the first subroutine to the 2nd subroutine, now the LastRowPlus1 will keep its value when the 2nd subroutine is called to run
2) That line you were getting the error on is because it wasn't liking the variables I put in, so I changed that to a For/Next Loop to solve that issue
3) In your sort line you had '.add2' so I changed that to just '.add' to resolve that issue.

Let us know how it goes for you now with the changes I made:

VBA Code:
    Public LastrowPlus1 As Long


Sub hailshort()
'
'   define source range
'
    With Worksheets("Macro")
        LastrowPlus1 = .Range("E" & Rows.Count).End(xlUp).Row + 1           ' Find LastRow of Column E on 'Macro' sheet and add 1 to the count
'
        Worksheets("hail").Range("N2:N17").Copy                             ' Copy N2:N17 from 'hail' sheet to 'macro' sheet Column C LastRowPlus1
        .Range("C" & LastrowPlus1).PasteSpecial Paste:=xlValues
'
        Worksheets("hail").Range("C2:C17").Copy                             ' Copy C2:C17 from 'hail' sheet to 'macro' sheet Column D LastRowPlus1
        .Range("D" & LastrowPlus1).PasteSpecial Paste:=xlValues
'
        Worksheets("hail").Range("E2:E17").Copy                             ' Copy E2:E17 from 'hail' sheet to 'macro' sheet Column E LastRowPlus1
        .Range("E" & LastrowPlus1).PasteSpecial Paste:=xlValues
'
        Worksheets("hail").Range("D2:D17").Copy                             ' Copy D2:D17 from 'hail' sheet to 'macro' sheet Column F LastRowPlus1
        .Range("F" & LastrowPlus1).PasteSpecial Paste:=xlValues
    End With
'
    Call sort
End Sub

Sub sort()
'
' sort Macro
'
    Application.CutCopyMode = False
'
    For RowOffset = LastrowPlus1 To LastrowPlus1 + 14 Step 2
        Range("J" & RowOffset) = "x"
    Next
'
    Columns("C:J").Select
    ActiveWorkbook.Worksheets("Macro").sort.SortFields.Clear
'
    ActiveWorkbook.Worksheets("Macro").sort.SortFields.Add Key:=Range("J" & LastrowPlus1 & ":J" & LastrowPlus1 + 16), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
    With ActiveWorkbook.Worksheets("Macro").sort
        .SetRange Range("C" & LastrowPlus1 - 1 & ":J" & LastrowPlus1 + 16)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'
    Range("C" & LastrowPlus1 & ":I" & LastrowPlus1 + 15).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
'
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    Range("J" & LastrowPlus1 & ":J" & LastrowPlus1 + 7).ClearContents
'
    Range("D" & LastrowPlus1 & ":D" & LastrowPlus1 + 15).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 2
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'
    Range("F" & LastrowPlus1 & ":F" & LastrowPlus1 + 15).NumberFormat = "[<=9999999]###-####;(###) ###-####"
'
    Range("H" & LastrowPlus1 & ":H" & LastrowPlus1 + 15).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
'
    Range("B" & LastrowPlus1).Select
    ActiveCell.FormulaR1C1 = "8:00 AM"
    Range("B" & LastrowPlus1).AutoFill Destination:=Range("B" & LastrowPlus1 & ":B" & LastrowPlus1 + 3), Type:=xlFillDefault
'
    Range("B" & LastrowPlus1 & ":B" & LastrowPlus1 + 3).Select
    Range("B" & LastrowPlus1 + 4).Select
    ActiveCell.FormulaR1C1 = "1:00 PM"
    Range("B" & LastrowPlus1 + 4).AutoFill Destination:=Range("B" & LastrowPlus1 + 4 & ":B" & LastrowPlus1 + 7), Type:=xlFillDefault
'
    Range("B" & LastrowPlus1 + 4 & ":B" & LastrowPlus1 + 7).Select
    Range("B" & LastrowPlus1 & ":B" & LastrowPlus1 + 7).Copy
'
    Range("B" & LastrowPlus1 + 8).Select
    ActiveSheet.Paste
'
    Application.CutCopyMode = False
'
    Range("B" & LastrowPlus1 & ":G" & LastrowPlus1 + 7).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15071487
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
    Range("B" & LastrowPlus1 + 8 & ":G" & LastrowPlus1 + 15).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15648990
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
    Range("E" & LastrowPlus1 + 19).Select
    Range("A" & LastrowPlus1 & ":A" & LastrowPlus1 + 7).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15071487
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
    Range("A" & LastrowPlus1 + 8 & ":A" & LastrowPlus1 + 15).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15648990
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
    Range("E" & LastrowPlus1 + 23).Select
End Sub

So the way it is set up now, when you run the 'hailshort' subroutine, it will automatically run the 'sort' subroutine for you.
 

Smilin

Board Regular
Joined
Nov 28, 2019
Messages
67
Platform
  1. Windows
When you upload the file it gives you a link to copy and paste. The link will have a bunch of random letters on the end of it.
Anyways, I took another look at the code and I found 3 problems:
1) I didn't put the call from the first subroutine to the 2nd subroutine, now the LastRowPlus1 will keep its value when the 2nd subroutine is called to run
2) That line you were getting the error on is because it wasn't liking the variables I put in, so I changed that to a For/Next Loop to solve that issue
3) In your sort line you had '.add2' so I changed that to just '.add' to resolve that issue.

Let us know how it goes for you now with the changes I made:

VBA Code:
    Public LastrowPlus1 As Long


Sub hailshort()
'
'   define source range
'
    With Worksheets("Macro")
        LastrowPlus1 = .Range("E" & Rows.Count).End(xlUp).Row + 1           ' Find LastRow of Column E on 'Macro' sheet and add 1 to the count
'
        Worksheets("hail").Range("N2:N17").Copy                             ' Copy N2:N17 from 'hail' sheet to 'macro' sheet Column C LastRowPlus1
        .Range("C" & LastrowPlus1).PasteSpecial Paste:=xlValues
'
        Worksheets("hail").Range("C2:C17").Copy                             ' Copy C2:C17 from 'hail' sheet to 'macro' sheet Column D LastRowPlus1
        .Range("D" & LastrowPlus1).PasteSpecial Paste:=xlValues
'
        Worksheets("hail").Range("E2:E17").Copy                             ' Copy E2:E17 from 'hail' sheet to 'macro' sheet Column E LastRowPlus1
        .Range("E" & LastrowPlus1).PasteSpecial Paste:=xlValues
'
        Worksheets("hail").Range("D2:D17").Copy                             ' Copy D2:D17 from 'hail' sheet to 'macro' sheet Column F LastRowPlus1
        .Range("F" & LastrowPlus1).PasteSpecial Paste:=xlValues
    End With
'
    Call sort
End Sub

Sub sort()
'
' sort Macro
'
    Application.CutCopyMode = False
'
    For RowOffset = LastrowPlus1 To LastrowPlus1 + 14 Step 2
        Range("J" & RowOffset) = "x"
    Next
'
    Columns("C:J").Select
    ActiveWorkbook.Worksheets("Macro").sort.SortFields.Clear
'
    ActiveWorkbook.Worksheets("Macro").sort.SortFields.Add Key:=Range("J" & LastrowPlus1 & ":J" & LastrowPlus1 + 16), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
    With ActiveWorkbook.Worksheets("Macro").sort
        .SetRange Range("C" & LastrowPlus1 - 1 & ":J" & LastrowPlus1 + 16)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'
    Range("C" & LastrowPlus1 & ":I" & LastrowPlus1 + 15).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
'
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
'
    Range("J" & LastrowPlus1 & ":J" & LastrowPlus1 + 7).ClearContents
'
    Range("D" & LastrowPlus1 & ":D" & LastrowPlus1 + 15).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 2
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'
    Range("F" & LastrowPlus1 & ":F" & LastrowPlus1 + 15).NumberFormat = "[<=9999999]###-####;(###) ###-####"
'
    Range("H" & LastrowPlus1 & ":H" & LastrowPlus1 + 15).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
'
    Range("B" & LastrowPlus1).Select
    ActiveCell.FormulaR1C1 = "8:00 AM"
    Range("B" & LastrowPlus1).AutoFill Destination:=Range("B" & LastrowPlus1 & ":B" & LastrowPlus1 + 3), Type:=xlFillDefault
'
    Range("B" & LastrowPlus1 & ":B" & LastrowPlus1 + 3).Select
    Range("B" & LastrowPlus1 + 4).Select
    ActiveCell.FormulaR1C1 = "1:00 PM"
    Range("B" & LastrowPlus1 + 4).AutoFill Destination:=Range("B" & LastrowPlus1 + 4 & ":B" & LastrowPlus1 + 7), Type:=xlFillDefault
'
    Range("B" & LastrowPlus1 + 4 & ":B" & LastrowPlus1 + 7).Select
    Range("B" & LastrowPlus1 & ":B" & LastrowPlus1 + 7).Copy
'
    Range("B" & LastrowPlus1 + 8).Select
    ActiveSheet.Paste
'
    Application.CutCopyMode = False
'
    Range("B" & LastrowPlus1 & ":G" & LastrowPlus1 + 7).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15071487
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
    Range("B" & LastrowPlus1 + 8 & ":G" & LastrowPlus1 + 15).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15648990
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
    Range("E" & LastrowPlus1 + 19).Select
    Range("A" & LastrowPlus1 & ":A" & LastrowPlus1 + 7).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15071487
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
    Range("A" & LastrowPlus1 + 8 & ":A" & LastrowPlus1 + 15).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15648990
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'
    Range("E" & LastrowPlus1 + 23).Select
End Sub

So thde way it is set up now, when you run the 'hailshort' subroutine, it will automatically run the 'sort' subroutine for you.
 

Smilin

Board Regular
Joined
Nov 28, 2019
Messages
67
Platform
  1. Windows
Johnny, this works like a charm. Thank you for all your effort and the time you put into this. I am very very grateful. You and everyone here helping us out make a huge difference. Time is a huge commodity and you are giving it freely. Gratitude.
 

Forum statistics

Threads
1,148,364
Messages
5,746,272
Members
424,002
Latest member
anon341

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top