Macros to Sort, Insert rows then copy and paste formulas only form cell ranges to worksheet

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a spreadsheet I am look for a macros to some thing in this. I am very new to macros and this was originally written by some one else. The macro is below

The Macros is to sort the dispatch worksheet by date Newest to Oldest range A13:L38 after being updated
' it is then to put in a row between each day of the week to seperate these
' It will then copies the formulas in cell range C14:I14 and K14:L14 and paste the formula only to C14:I38 and K14:L38
' so that the formulas in cell C14:I38 And K14:L38 get repopulated into all rows
'
' Active workbooks may use different names these to be updated as names change in the code for workbook.
I would attach a picture to show the final output but do not know how.
Data is populated in a row for each delivery with the date for dispatch
This is copied form spreadsheet Column A and cell 14 is the Day below and column "C" is hidden, Column "L" is the Customer or ship.

Hope this help to give a picture for what I am needing for the final output

Cheers
NZAS
Week Commencing :- Monday, 5 August 2019Version:1
DayManifestProduct TypeTonnes ReqLots RequiredOn TruckOff SiteLots to GoOff Site YardColourCustomer or Ship
Mon--Aug--05802082215178 x 679530012300123 REDInex - Ham
Mon--Aug--05802082216202 x 700030012800128 BLAInex - Ham
Mon--Aug--05802082649202 x 7000964115026 GRN / ORAAltus - Auck
Mon--Aug--05802082319INGOT x 49480048 BLA / TRQNavios Summer
Tue--Aug--06802082215178 x 679530012300123 REDInex - Ham
Tue--Aug--06802082216202 x 700030012800128 BLAInex - Ham
Tue--Aug--06802082649202 x 7000964115026 GRN / ORAAltus - Auck
Tue--Aug--06802082325178 x 5800116500050 RED / PINNavios Summer
Wed--Aug--07802082215178 x 679530012300123 REDInex - Ham
Wed--Aug--07802082216202 x 700030012800128 BLAInex - Ham
Wed--Aug--07802082649202 x 7000964115026 GRN / ORAAltus - Auck
Wed--Aug--07802082270INGOT x 272264400224 BLANavios Summer

<colgroup><col><col><col span="6"><col><col><col></colgroup><tbody>
</tbody>




The macro I did have does not do all that is required as I do not want to have the data to extend past row 38 and only insert the rows to fit no wider than column "L"

Code:
 Range("A14:I38").Select
    ActiveWorkbook.Worksheets("This Week").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("This Week").Sort.SortFields.Add Key:=Range( _
        "A14:A43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("This Week").Sort
        .SetRange Range("A13:j38")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A14").Select
    With Sheets("This Week")
        .Range("a14").End(xlDown).Select
        While ActiveCell.Row <> 14
            If Day(ActiveCell.Value) = Day(ActiveCell.Offset(-1, 0)) Then
                ActiveCell.Offset(-1, 0).Select
            Else
                ActiveCell.EntireRow.Select
                Selection.Insert Shift:=xlDown
                .Range("e14:J14").Copy
                ActiveCell.Offset(0, 4).PasteSpecial (xlFormulas)
                .Range("44:44").EntireRow.Delete
                ActiveCell.Offset(-1, -4).Select
            End If
        Wend
    End With
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,889
Please provide a few lines of the dispatch worksheet before the code is run, be sure to state what worksheet rows they are in.
What row on that worksheet contains the headers? What is the last possible data row? Is there EVER any blank rows in that data?
Are the values in column A dates that are formatted with the double dashes, or are they text?

Inserting blank rows in the data between the dates can add either 4 lines (for Mon-Fri weeks) or 6 lines for Mon-Sun weeks) is there enough data in the dispatch worksheet that the additional rows will move the bottom row past line 38?

I assume formulas should not be placed in otherwise blank rows.

What sheet contains the formulas?
Be sure to show the formulas location(s)

As I read your description it seems as if formulas and headers are in row 14. Please expand your comments to clear up my confusion.

Replace both of these rows:
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
with this row:
Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "L")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
to only insert cells in columns A:L
 

Watch MrExcel Video

Forum statistics

Threads
1,128,172
Messages
5,629,134
Members
416,365
Latest member
dof

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