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

S

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?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Few questions for you:

Sheets mentioned: "hail", "Macro", master sheet? <-- is this an additional named sheet, or one of the first two mentioned

Sub 'hailshort': Are you wanting to copy from sheet "hail" to different section of sheet "hail" or a different sheet?

Sub sort: which sheet name are you wanting to do the sort? "Macro?


Can you supply an example of some before/after of what you are starting with and want to end with, or upload a sample of the workbook?
 
Upvote 0
johnnyL said:
Few questions for you:

Sheets mentioned: "hail", "Macro", master sheet? <-- is this an additional named sheet, or one of the first two mentioned

Sub 'hailshort': Are you wanting to copy from sheet "hail" to different section of sheet "hail" or a different sheet?

Sub sort: which sheet name are you wanting to do the sort? "Macro?


Can you supply an example of some before/after of what you are starting with and want to end with, or upload a sample of the workbook?
Click to expand...
I have 5 tabs, named A, B, C, D, E in one workbook. Each tab is a mastersheet. The sheet I copy over to this work book is called hail. I would like to click on a given tab, regardless of its name, and run the macro. If I click on tab A, I would like to run the macro (the one I posted but both combined and working) If I click on tab B, I would like to click on the same macro. So basically running the macro from "this sheet"(being any tab I am on). All tabs use the same marcro, and all copy the information from sheet named hail. (up to know for practice purposes only, I created a workbook with a sheet called macro and I moved "hail" sheet over to it ) that is when I realized that my sort /format macro was not working for the 2nd set of 16 rows. My "hail" sheet only has 16 rows of values. I hope my answer makes sense.
 
Upvote 0
Ok so the sheet named 'hail' that has data that you want to copy data from is from a different workbook?

You have 5 master sheets in a different workbook named "A, B, C, D, E" that you want the ability to copy data to from the 'hail' sheet?

Are both of these workbooks open?

Do all the 'master sheets' have the same layout? ie. column setup and whatnot?

This brings me back to my last question above ... the 1st sub ... 'hailshort', you are wanting that to copy data from a sheet called 'hail' from a different workbook to ranges on a 'master sheet' in a different workbook?

If you are dealing with different workbooks, we need to know that, what the path and file name are to those. You can make up the names, but that is important info that we need to offer suggestions to you.

Finally the 'sort' subroutine ... I assume you want that done on the 'master sheet' that had data copied to it?
 
Upvote 0
johnnyL said:
Ok so the sheet named 'hail' that has data that you want to copy data from is from a different workbook?

You have 5 master sheets in a different workbook named "A, B, C, D, E" that you want the ability to copy data to from the 'hail' sheet? - YES

Are both of these workbooks open? not sure what you mean. see my answer below

Do all the 'master sheets' have the same layout? ie. column setup and whatnot? - yes

This brings me back to my last question above ... the 1st sub ... 'hailshort', you are wanting that to copy data from a sheet called 'hail' from a different workbook to ranges on a 'master sheet' in a different workbook?- master sheet being the 5 tabs, each tab I consider master sheet.

If you are dealing with different workbooks, we need to know that, what the path and file name are to those. You can make up the names, but that is important info that we need to offer suggestions to you.

Finally the 'sort' subroutine ... I assume you want that done on the 'master sheet' that had data copied to it?- YES
Click to expand...
Yes, "hail" sheet is a different work book, and it is the only sheet in that work book. The date from it needs to be copied to the one that has the 5 tabs. . I move the "hail" sheet to the workbook of 5 tabs, so when the macro is ran, it needs to copy the information from the "hail" sheet.

Not sure what you mean if both workbooks are open, The one with the 5 tabs is open, and when I move the "hail" sheet over to it, I have 5 tabs, and the "hail" tab. The 1st macro "hailshort" is copying the data from the "hail" sheet. The reference to "with worksheets ("macro") needs to change. The reference will the be workbook that has the 5 tabs. All the 5 tabs, (master sheets are set up exactly the same.
"
The Hailshort Marcro copies and pastes data from the "Hail" sheet correctly. Each time I run it, it copies and pastes 16 lines, then 16 lines under that and so on. However, my "sort macro (which is sorting and formatting) doesn't do that, it will do it for the 1st 16 lines in the worksheet, but it will not apply it to the 2nd time it run it to the 2nd 16 lines. The macro just executes it for the 1st set of 16 lines, over and over, each time I run it. My sort macro needs to apply to each of the 16 lines that gets copied and pasted. (it sorts it, adds borders, does color fill in, adds times, does aligning and formats the phone number.)

So I have only one workbook, 5 tabs, and the other workbook containing one sheet called "hail sheet" data is copied from the hail sheet to any of the tabs I have to use. If at all possible, I would like to have one common Macro shared by the 5 tabs. Could the path be from "hail" to this "sheet". This sheet being the tab A, b, c , d, f, (not being a specific name) click on and run the macro? (something like that?) The tabs are actually names of companies, and sometimes I have to add a tab, so that is why I don't want to reference the companies name for the macro, but instead , if I click on the tab refer it to "this sheet"? ,if that is even possible.
 
Upvote 0
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?
 
Upvote 0
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
End Sub

Sub sort()
'
' sort Macro
'
    Application.CutCopyMode = False
'
    Range("J & LastrowPlus1,J & LastrowPlus1 + 2,J & LastrowPlus1 + 4,J & LastrowPlus1 + 6,J & LastrowPlus1 + 8,J & LastrowPlus1 + 10,J & LastrowPlus1 + 12,J & LastrowPlus1 + 14") = "x"
'
    Columns("C:J").Select
    ActiveWorkbook.Worksheets("Macro").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Macro").sort.SortFields.Add2 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

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.
 
Upvote 0
johnnyL said:
The first 'With' statement in your Sort routine has:
johnnyL said:
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
End Sub

Sub sort()
'
' sort Macro
'
    Application.CutCopyMode = False
'
    Range("J & LastrowPlus1,J & LastrowPlus1 + 2,J & LastrowPlus1 + 4,J & LastrowPlus1 + 6,J & LastrowPlus1 + 8,J & LastrowPlus1 + 10,J & LastrowPlus1 + 12,J & LastrowPlus1 + 14") = "x"
'
    Columns("C:J").Select
    ActiveWorkbook.Worksheets("Macro").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Macro").sort.SortFields.Add2 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

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.
Click to expand...

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

Is that C1:J18 correct? Should it be C2:J18?
Click to expand...
 
Upvote 0
Thank you Johny, I am going to test it this weekend when I have time and support from my daughter. I really appreciate the time you put into this. Your help is invaluable to me. Gratitude! I will let you know if I am successful.
 
Upvote 0
Smilin said:
Thank you Johny, I am going to test it this weekend when I have time and support from my daughter. I really appreciate the time you put into this. Your help is invaluable to me. Gratitude! I will let you know if I am successful.
Click to expand...
Hi Johny, I am getting a range error on this Line:
Range("J & LastrowPlus1,J & LastrowPlus1 + 2,J & LastrowPlus1 + 4,J & LastrowPlus1 + 6,J & LastrowPlus1 + 8,J & LastrowPlus1 + 10,J & LastrowPlus1 + 12,J & LastrowPlus1 + 14") = "x"

Also not sure if this makes difference, but to answer your question wit reference to J1 or J2. It is J2. Thank you

1627868735463.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
324
Micron
M
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
598
RoryA
RoryA
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
P
  • Question Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
379
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,759
Messages
6,180,813
Members
452,996
Latest member
nelsonsix66

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