Method 'Range' of object '_Worksheet' failed when adding a key to a sort command in vba

lmoseley7

Board Regular
Joined
Jul 9, 2013
Messages
151
I have written a macro that goes out to a network folder, opens a .prn file and delimits it. It then copies the data and pastes it into my workbook on a specific sheet, sorts the data and then applies subtotals to the data. It repeats this action on 4 additional .prn files. I'm testing my code and it works fine on the first pass, but I get a 'Range of object worksheet failed' error when the code attempts to sort the second set of data. All the code is the same with the exception of the related named ranges and variables. After a quick Google search, I decided to test forcing the macro to select the second sheet specifically before the sort to see if there was a problem with the focus of VBA or Excel that was causing the problem, but that didn't change things. Also, I can see the affects of other portions of the code correctly taking place on the sheet in question. I have also verified that the named range on the sheet exists and when selecting the range in Name Manager, it selects the correct area. My code as it stands is below. Can anyone help figure out why the line group that starts with
"sh_ETCD.Sort.SortFields. _
Add Key:=sh_ETCD.Range("EQTRC_All").Columns(1)..."
gives me the error?

Code:
Sub Delimit_PRN_Files()'Portions of the code below will work for all prn files, while other portions _
need to be updated for differences between the prn files.  Segments of code that _
begin with '********Begin_Changes******** will require that portions of the code _
are updated to use for other prn files.  The portions that need updating will be _
documented and the sections will end with '********End_Changes********.  Sections _
that begin and end with '********No_Changes******** should work for all prn files. _
The Begin/End/No_Changes comments will be the only ones that have no indention.


    'First: Declare the range variables for the cells that contain the location of the PRN files, _
    the range that makes up the data source range and the data destination range. _
    Declare the worksheet variables for the APS2 download worksheets. _
    Finally we set up variables so that we can switch between the Cash Move and the PRN _
    files.
Dim prn_Loc As Range, EOSD_Loc As Range, EQTRC_Loc As Range, EQTRS_Loc As Range, _
    FTRC_Loc As Range, FTRS_Loc As Range, data_src As Range, data_dest As Range
Dim sh_ETCD As Worksheet, sh_ETSD As Worksheet, sh_FTCD As Worksheet, sh_FTSD As Worksheet, _
    sh_EOSD As Worksheet
Dim ControlFile As String
Dim prnFileName As String, prn_Sheet As String
Dim LastRow As Long


    'The following two lines will supress the screen refreshing with each line of macro code _
    and the majority of informational pop-ups that might appear.
'    Application.ScreenUpdating = False
'    Application.DisplayAlerts = False
    'Here we set the ControlFile Variable so that we can navigate back to the parent workbook.
    ControlFile = ActiveWorkbook.Name
    'Next we set the following variables equal to the values in the named ranges.  This will _
    allow for changes to the macro without getting into VBA.
Set prn_Loc = ThisWorkbook.Sheets("Ranges").Range("prn_Directory")
Set EOSD_Loc = ThisWorkbook.Sheets("Ranges").Range("rng_EOSD")
Set EQTRC_Loc = ThisWorkbook.Sheets("Ranges").Range("rng_EQTRC")
Set EQTRS_Loc = ThisWorkbook.Sheets("Ranges").Range("rng_EQTRS")
Set FTRC_Loc = ThisWorkbook.Sheets("Ranges").Range("rng_FTRC")
Set FTRS_Loc = ThisWorkbook.Sheets("Ranges").Range("rng_FTRS")
    'I'm setting variables for the sheets to reduce the code needed later.  These sheet names _
    are the names that VBA sees, not the names on the Tabs.  For these sheets names, see the _
    project explorer widow to the left.
Set sh_ETCD = [ETCD]
Set sh_ETSD = [ETSD]
Set sh_FTRC = [FTCD]
Set sh_FTRS = [FTSD]
Set sh_EOSD = [EOSD]
    'Opening the first file and delimiting.  Next we set up a named range to use to pull the data _
    back to the Cash Move.
    'First we use ChDir to set the location where the files will be found.
    ChDir prn_Loc
    'Next we open the file using one of the variables set up previously.  When delimiting, it appears _
    that in the arrays, the second number is the data type for that column.  1 is General and 3 must _
    be the default Date data type.  It looks to show the First column as general and then skip until _
    it reaches another column that isn't set as general, it lists those that are different, and then _
    continues until it reaches another column that is a general data type.


'*****EQTRS*****


'********Begin_Changes******** _
The following code would need to be updated for each APS2 download format. _
The location variable (e.g.EQTRS_Loc) and the Array information needs to be updated. _


    Workbooks.OpenText Filename:= _
        "" & EQTRS_Loc, origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(8, 3), Array(9, 3), Array(10 _
        , 3), Array(11, 1)), TrailingMinusNumbers:=True
'********End_Changes********


'********No_Changes********
    'We set the variables for the sheet name of the PRN file and the file name.
    prnFileName = ActiveWorkbook.Name
    prn_Sheet = ActiveSheet.Name
    'We add a named range so that we can pull the data back to the Cash Move workbook.
    ActiveWorkbook.Names.Add Name:="data", RefersToR1C1:= _
        "=OFFSET('" & prn_Sheet & "'!R1C1,0,0,COUNTA('" & prn_Sheet & "'!C),COUNTA('" & prn_Sheet & "'!R))"
        ActiveWorkbook.Names("data").Comment = ""
    Set data_src = Sheets(prn_Sheet).Range("data")
    'We go back to the Cash Move file.
    Windows(ControlFile).Activate
'********No_Changes********


'********Begin_Changes******** _
The sheet name using the "sh_@@@@" variable, and the "_Delete_Range", "@@@@@_Data", "@@@@@_Formulas", _
"@@@@@_All" and "@@@@@_All_H" ranges need to be updated.
    'This code selects all cells on the named sheet to remove the subtotals.  If we are coming from a _
    template, we wouldn't have subtotals, but this code doesn't seem to error out when subtotals aren't _
    present and this allows us to re-pull the data in an existing workbook.
        With sh_ETSD.Cells
            .RemoveSubtotal
        End With
    'The '_Delete_Range' is defined within Excel as starting on row 3 and going down to the end of the _
    data.  If there is no data on row 3 or below, it selects row 3 only.  This allows us to remove all _
    but the first row, which includes our formulas, so that we never have a situation where the data _
    being pulled in doesn't completely overwrite the existing data.
    sh_ETSD.Range("EQTRS_Delete_Range").ClearContents
     
    'The following code sets the data_dest variable as the @@@@_Data range.  That _Data range at this _
    point will be made up of only one row since we've removed everything starting with row 3.  Since _
    our source range "data_src" and destination range "data_dest" aren't the same size, we can't make _
    the values equal each other, which is a way to avoid copying and pasting so we can avoid the pop-up _
    messages that come along with copying and pasting.  By using 'Resize' and basing the number of columns _
    and rows on the source document, we can set the values equal and bypass copying and pasting.  Note, that _
    resize doesn't change the formula for the dynamic range within Excel, it only resizes the range for the _
    purposes of this macro.
    Set data_dest = sh_ETSD.Range("EQTRS_Data").Resize(data_src.Rows.Count, data_src.Columns.Count)
    data_dest.Value = data_src.Value
    
    'If there is only one row of data, FillDown will actually copy the header down and overwrite the formulas. _
    If only one row exists then we skip the filldown, sort and subtotal code.
    If data_dest.Rows.Count = 1 Then
        GoTo 1
        Else
        Debug.Print data_dest.Rows.Count
    sh_ETSD.Range("EQTRS_Formulas").FillDown
    'First we ensure the information is sorted by APS2 Fund Number (Column A), but we clear the sort first _
    to ensure we aren't adding additional sort keys.
    sh_ETSD.Sort.SortFields.Clear
    sh_ETSD.Sort.SortFields. _
        Add Key:=sh_ETSD.Range("EQTRS_All").Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With sh_ETSD.Sort
        .SetRange Range("EQTRS_All_H")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    'Next is the code where we add the subtotals.
'********Continued_Changes******** _
This section would need to be updated for each sheet so that the correct columns are being subtotaled and formatted.
    Range("EQTRS_All_H").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 12, _
        13, 14, 15, 16, 17, 18, 19, 20, 21, 22), Replace:=True, PageBreaks:=False, _
        SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Columns("F:G").Select
    Selection.Style = "Comma"
    End If
1:    Windows(prnFileName).Close False
    'Windows(prnFileName).Activate
    'ActiveWindow.WindowState = xlNormal
    'ActiveWorkbook.Close
    
    Windows(ControlFile).Activate
        
'*****EQTRC*****
'********Begin_Changes******** _
The following code would need to be updated for each APS2 download format. _
The location variable (e.g.EQTRC_Loc) and the Array information needs to be updated. _


    Workbooks.OpenText Filename:= _
        "" & EQTRC_Loc, origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(8, 3), Array(9, 3), Array(10 _
        , 3), Array(11, 1)), TrailingMinusNumbers:=True
'********End_Changes********


'********No_Changes********
    'We set the variables for the sheet name of the PRN file and the file name.
    prnFileName = ActiveWorkbook.Name
    prn_Sheet = ActiveSheet.Name
    'We add a named range so that we can pull the data back to the Cash Move workbook.
    ActiveWorkbook.Names.Add Name:="data", RefersToR1C1:= _
        "=OFFSET('" & prn_Sheet & "'!R1C1,0,0,COUNTA('" & prn_Sheet & "'!C),COUNTA('" & prn_Sheet & "'!R))"
        ActiveWorkbook.Names("data").Comment = ""
    Set data_src = Sheets(prn_Sheet).Range("data")
    'We go back to the Cash Move file.
    Windows(ControlFile).Activate


'********No_Changes********


'********Begin_Changes******** _
The sheet name using the "sh_@@@@" variable, and the "_Delete_Range", "@@@@@_Data", "@@@@@_Formulas", _
"@@@@@_All" and "@@@@@_All_H" ranges need to be updated.
    'This code selects all cells on the named sheet to remove the subtotals.  If we are coming from a _
    template, we wouldn't have subtotals, but this code doesn't seem to error out when subtotals aren't _
    present and this allows us to re-pull the data in an existing workbook.
        With sh_ETCD.Cells
            .RemoveSubtotal
        End With
    'The '_Delete_Range' is defined within Excel as starting on row 3 and going down to the end of the _
    data.  If there is no data on row 3 or below, it selects row 3 only.  This allows us to remove all _
    but the first row, which includes our formulas, so that we never have a situation where the data _
    being pulled in doesn't completely overwrite the existing data.
    sh_ETCD.Range("EQTRC_Delete_Range").ClearContents
     
    'The following code sets the data_dest variable as the @@@@_Data range.  That _Data range at this _
    point will be made up of only one row since we've removed everything starting with row 3.  Since _
    our source range "data_src" and destination range "data_dest" aren't the same size, we can't make _
    the values equal each other, which is a way to avoid copying and pasting so we can avoid the pop-up _
    messages that come along with copying and pasting.  By using 'Resize' and basing the number of columns _
    and rows on the source document, we can set the values equal and bypass copying and pasting.  Note, that _
    resize doesn't change the formula for the dynamic range within Excel, it only resizes the range for the _
    purposes of this macro.
    Set data_dest = sh_ETCD.Range("EQTRC_Data").Resize(data_src.Rows.Count, data_src.Columns.Count)
    data_dest.Value = data_src.Value
    
    'If there is only one row of data, FillDown will actually copy the header down and overwrite the formulas. _
    If only one row exists then we skip the filldown, sort and subtotal code.
    If data_dest.Rows.Count = 1 Then
        GoTo 2
        Else
        Debug.Print data_dest.Rows.Count
    sh_ETCD.Range("EQTRC_Formulas").FillDown
    'First we ensure the information is sorted by APS2 Fund Number (Column A), but we clear the sort first _
    to ensure we aren't adding additional sort keys.
    sh_ETCD.Sort.SortFields.Clear
    sh_ETCD.Sort.SortFields. _
        Add Key:=sh_ETCD.Range("EQTRC_All").Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With sh_ETCD.Sort
        .SetRange Range("EQTRC_All_H")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    'Next is the code where we add the subtotals.
'********Continued_Changes******** _
This section would need to be updated for each sheet so that the correct columns are being subtotaled and formatted.
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 12, _
        13, 14, 15, 16, 17, 18, 19, 20, 21, 22), Replace:=True, PageBreaks:=False, _
        SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Columns("F:G").Select
    Selection.Style = "Comma"
    End If
2:    Windows(prnFileName).Close False






    Application.ScreenUpdating = True
    Application.DisplayAlerts = True


End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
After exhausting all other options including verifying that the named ranges were selecting the correct areas of the correct worksheets, I decided to compare two similar ranges to see if they had been set up differently. Wouldn't you know it, the one that was giving the error was set to a scope of worksheet, not workbook. Since I try not to 'Select' as much as possible, this must have been causing my issue. Changing the scope to Workbook level solved the error. Posting just in case someone else comes across a similar issue.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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