Macro Error

DebbieFost
DebbieFost used Ask the Experts™
on
I need assistance with the following macro error.

Sub QuickbaseNEWARExport()
'
' QuickbaseNEWARExport Macro
'

'
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "INVOICE"
    ActiveCell.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Selection.Copy
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveCell.Offset(-218, -1).Range("A1:A218").Select
    ActiveCell.Offset(-1, -1).Range("A1").Activate
    ActiveSheet.Paste
    ActiveCell.Offset(0, 8).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Accounts Receivable"
    ActiveCell.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Selection.Copy
    ActiveCell.Offset(-218, -2).Range("A1:C219").Select
    ActiveCell.Activate
    ActiveCell.Select
    Application.CutCopyMode = False
    Selection.Cut
    ActiveCell.Offset(0, -2).Range("A1").Select
    ActiveSheet.Paste
    Selection.Copy
    ActiveCell.Offset(-218, 0).Range("A1:A219").Select
    ActiveCell.Activate
    ActiveSheet.Paste
    ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select
    ActiveCell.Offset(-19, -1).Range("A1").Activate
    ActiveWindow.ScrollRow = 203
    ActiveWindow.ScrollRow = 193
    ActiveWindow.ScrollRow = 184
    ActiveWindow.ScrollRow = 175
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 1
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.Select
    ActiveSheet.Paste
    ActiveCell.Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Split"
    ActiveCell.Offset(0, -8).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Type"
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Accounts Receivable"
    ActiveCell.Offset(0, 4).Columns("A:E").EntireColumn.Select
    Selection.Cut
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(0, -10).Columns("A:J").EntireColumn.Select
    Selection.Columns.AutoFit
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveWindow.FreezePanes = True
    ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
    Selection.AutoFilter
    Selection.Font.Bold = True
    ActiveCell.Offset(0, 13).Columns("A:A").EntireColumn.Select
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveCell.Cells.Select
    ActiveWorkbook.Worksheets("Quickbase NEW AR Export").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Quickbase NEW AR Export").Sort.SortFields.Add Key _
        :=ActiveCell.Offset(0, 3).Range("A1:A1999"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Quickbase NEW AR Export").Sort
        .SetRange ActiveCell.Offset(-1, 0).Range("A1:N2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Quickbase NEW AR Export").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Quickbase NEW AR Export").Sort.SortFields.Add Key _
        :=ActiveCell.Offset(0, 2).Range("A1:A1999"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Quickbase NEW AR Export").Sort
        .SetRange ActiveCell.Offset(-1, 0).Range("A1:N2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveCell.Offset(-1, 0).Range("A1").Select
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What error? What line?

Author

Commented:
Selection.SpecialCells(xlCellTypeBlanks).Select
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I may be missing it but I still don't see where you specify what the error is. If it's 'No cells were found' then it's because there's no data in the column to the right of the active cell.
Hi, Debbie.

You have a lot of code of the form...
ActiveCell.Offset(-19, -1).Range("A1")
The Range("A1") is irrelevant here - it's simply taking the top-left cell of the single-cell range represented by ActiveCell.Offset(-19, -1).

I assume that your code originally came from the macro recorder and was then heavily tweaked. Because of all the hard-coded offsets and ranges and the use of a constantly changing active cell, this macro is going to be a nightmare for you to maintain. If you would like to post a copy of both the original data and the final layout you're looking for, we could produce a cleaner version of the code.

Regards,
Brian.
Thanks, Debbie.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial