Share via

Excel macro not pulling in all rows

Anonymous
2013-10-11T12:55:45+00:00

Hello community,

I created a macro (the non professional way - i.e. didn't write the VBA myself) that copies data from one sheet into another and then applies a series of sorts and filters to come up with a "report" for our office.

The macro was running fine for a few weeks/months, but now somehow all of the data from the first sheet is not being brought into the data copied in the final sheet.

In the main sheet, new rows are constantly being added and it seems to bring in all rows that are inserted between rows, but not rows that have been added. Meaning, if I had rows 1-10, but at row 3, I inserted a new row, the macro would bring in that row into the final sheet. However, if I add a row 11, that will not be brought in. I believe that is what is happening based on the final product in the final sheet.

I have copied the VBA code below. Any insight would be appreciated and thank you.

Sub clipboard()

'

' clipboard Macro

'

' Keyboard Shortcut: Ctrl+m

'

    Sheets("CLIPBOARD").Select

    Cells.Select

    Selection.Delete Shift:=xlUp

    Selection.ClearContents

    Selection.Delete Shift:=xlUp

    Sheets("MasterLog10.20.10").Select

    Cells.Select

    Selection.Copy

    Sheets("CLIPBOARD").Select

    ActiveSheet.Paste

    Columns("A:A").Select

    Application.CutCopyMode = False

    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Columns("E:E").Select

    Selection.Cut

    Columns("A:A").Select

    ActiveSheet.Paste

    Columns("D:D").Select

    Selection.Cut

    Columns("D:F").Select

    Application.CutCopyMode = False

    Selection.Cut

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 4

    ActiveWindow.ScrollColumn = 6

    ActiveWindow.ScrollColumn = 7

    ActiveWindow.ScrollColumn = 8

    ActiveWindow.ScrollColumn = 10

    ActiveWindow.ScrollColumn = 11

    ActiveWindow.ScrollColumn = 12

    ActiveWindow.ScrollColumn = 13

    ActiveWindow.ScrollColumn = 14

    ActiveWindow.ScrollColumn = 16

    ActiveWindow.ScrollColumn = 17

    ActiveWindow.ScrollColumn = 18

    ActiveWindow.ScrollColumn = 19

    ActiveWindow.ScrollColumn = 20

    ActiveWindow.ScrollColumn = 21

    ActiveWindow.ScrollColumn = 23

    Columns("AG:AG").Select

    ActiveSheet.Paste

    Columns("AH:AH").Select

    Selection.Delete Shift:=xlToLeft

    Columns("AA:AF").Select

    Range("AF1").Activate

    Selection.Delete Shift:=xlToLeft

    ActiveWindow.ScrollColumn = 22

    ActiveWindow.ScrollColumn = 21

    ActiveWindow.ScrollColumn = 20

    ActiveWindow.ScrollColumn = 19

    ActiveWindow.ScrollColumn = 18

    ActiveWindow.ScrollColumn = 16

    ActiveWindow.ScrollColumn = 14

    ActiveWindow.ScrollColumn = 11

    ActiveWindow.ScrollColumn = 8

    ActiveWindow.ScrollColumn = 6

    ActiveWindow.ScrollColumn = 4

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 1

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 4

    ActiveWindow.ScrollColumn = 5

    ActiveWindow.ScrollColumn = 7

    ActiveWindow.ScrollColumn = 8

    ActiveWindow.ScrollColumn = 9

    ActiveWindow.ScrollColumn = 10

    ActiveWindow.ScrollColumn = 11

    Columns("N:P").Select

    Selection.Delete Shift:=xlToLeft

    Columns("R:T").Select

    Selection.Delete Shift:=xlToLeft

    Columns("T:T").Select

    Selection.Delete Shift:=xlToLeft

    Columns("S:S").Select

    Selection.Cut

    ActiveWindow.ScrollColumn = 10

    ActiveWindow.ScrollColumn = 8

    ActiveWindow.ScrollColumn = 7

    ActiveWindow.ScrollColumn = 5

    ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 1

    Columns("D:D").Select

    ActiveSheet.Paste

    Columns("E:F").Select

    Selection.Delete Shift:=xlToLeft

    Columns("G:I").Select

    Selection.Delete Shift:=xlToLeft

    Columns("K:L").Select

    Selection.Delete Shift:=xlToLeft

    Columns("L:L").Select

    Selection.Delete Shift:=xlToLeft

    Cells.Select

    With Selection.Interior

        .Pattern = xlNone

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlInsideVertical)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlInsideHorizontal)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    Rows("1:1").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorAccent3

        .TintAndShade = 0.599993896298105

        .PatternTintAndShade = 0

    End With

    Columns("A:A").Select

    With Selection

        .HorizontalAlignment = xlCenter

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

    With Selection

        .HorizontalAlignment = xlGeneral

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

    With Selection

        .HorizontalAlignment = xlCenter

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

    Dim lngRow As Long

    lngRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    ActiveWorkbook.Worksheets("CLIPBOARD").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("CLIPBOARD").Sort.SortFields.Add Key:=Range( _

        "F2:F" & lngRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

        xlSortNormal

    ActiveWorkbook.Worksheets("CLIPBOARD").Sort.SortFields.Add Key:=Range( _

        "A2:A" & lngRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

        xlSortNormal

    With ActiveWorkbook.Worksheets("CLIPBOARD").Sort

        .SetRange Range("A8:M" & lngRow)

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

     Rows("1:1").Select

    Selection.AutoFilter

    ActiveSheet.Range("$A$1:$AJ$" & lngRow).AutoFilter Field:=9, Criteria1:=Array( _

        "Active", "Active file", "Meeting Agenda", "Meeting Meeting Agenda", "Unclaimed", _

        "="), Operator:=xlFilterValues

    Columns("I:I").Select

    Selection.EntireColumn.Hidden = True

    ActiveSheet.Range("$A$1:$AJ$" & lngRow).AutoFilter Field:=10, Criteria1:="="

    Columns("J:J").Select

    Selection.EntireColumn.Hidden = True

    ActiveSheet.Range("$A$1:$AJ$" & lngRow).AutoFilter Field:=7, Criteria1:= _

        "Pending"

    Columns("G:G").Select

    Selection.EntireColumn.Hidden = True

    Range("C82").Select

    Rows("1:1").Select

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("A1").Select

    ActiveCell.FormulaR1C1 = "Date report generated"

    Range("B1").Select

    ActiveCell.FormulaR1C1 = "=TODAY()"

    Range("B1").Select

    With Selection.Font

        .Color = -16776961

        .TintAndShade = 0

    End With

    With Selection.Font

        .Name = "Arial"

        .Size = 8

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .Color = -16776961

        .TintAndShade = 0

        .ThemeFont = xlThemeFontNone

    End With

    Range("A1").Select

    Selection.Font.Italic = True

    With Selection.Font

        .Name = "Arial"

        .Size = 8

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ColorIndex = xlAutomatic

        .TintAndShade = 0

        .ThemeFont = xlThemeFontNone

    End With

    Cells.Select

    With Selection.Font

        .Size = 10

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .TintAndShade = 0

        .ThemeFont = xlThemeFontNone

    End With

    Range("A1:B1").Select

    Range("B1").Activate

    With Selection.Font

        .Name = "Arial"

        .Size = 8

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .TintAndShade = 0

        .ThemeFont = xlThemeFontNone

    End With

    Range("D83").Select

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2013-10-14T10:02:39+00:00

    As I ever said: Never use SELECT and SELECTION in your macros, it's slow and error prone.

    What I don't understand is why your macro sorts only the data below row 8!? The macro below works the same way.

    Andreas.

    Option Explicit

    Sub GenerateDateReport()

      Dim R As Range, i As Integer

      With Sheets("CLIPBOARD")

        'Show a message to the user

        .Select

        .Range("A1") = "Generating date report, please wait..."

        Application.Cursor = xlWait

        'Screen off, runs faster

        Application.ScreenUpdating = False

        'Be sure the autofilter is off

        .AutoFilterMode = False

        'Delete all data and formats

        .Cells.Clear

        'Copy the values only

        Sheets("MasterLog10.20.10").UsedRange.Copy .Range("A1")

        'Reset the last cell

        .UsedRange

        'Delete unused columns

        .Range("H:J,M:O,R:V,Y:AH").Delete

        'Move some data

        'D before A

        .Range("D:D").Cut

        .Range("A:A").Insert

        'X after B

        .Range("M:M").Cut

        .Range("D:D").Insert

        'C,E after W

        .Range("E:F").Cut

        .Range("N:N").Insert

        'Apply some formats

        Set R = .UsedRange

        R.Font.Size = 10

        For i = xlEdgeLeft To xlInsideHorizontal

          R.Borders(i).LineStyle = xlContinuous

        Next

        With R.Rows(1).Interior

          .ThemeColor = xlThemeColorAccent3

          .TintAndShade = 0.599993896298105

        End With

        With R.Columns(1)

          .HorizontalAlignment = xlCenter

        End With

        'Sort the data below row 8!?

        Set R = .UsedRange

        If R.Rows.Count > 8 Then

          Set R = R.Resize(R.Rows.Count - 8).Offset(8)

          R.Sort _

            Key1:=Range("F1"), Order1:=xlAscending, _

            Key2:=Range("A1"), Order2:=xlAscending, _

            Header:=xlNo

        End If

        'Apply a some filters

        Set R = .UsedRange

        R.AutoFilter 9, Array("Active", "Active file", "Meeting Agenda", _

          "Meeting Meeting Agenda", "Unclaimed", "="), xlFilterValues

        R.AutoFilter 10, "="

        R.AutoFilter 7, "Pending"

        'Hide some columns

        .Range("G:G,I:J").EntireColumn.Hidden = True

        'Done, final message

        .Rows(1).Insert

        With .Range("A1")

          .Value = "Date report generated"

          With .Font

            .Name = "Arial"

            .Size = 8

            .Italic = True

          End With

        End With

        With .Range("B1")

          .Value = Date

          With .Font

            .Name = "Arial"

            .Size = 8

            .Color = -16776961

          End With

        End With

      End With

      Application.Cursor = xlDefault

      Application.ScreenUpdating = True

    End Sub

    Was this answer helpful?

    0 comments