Method or data member not found

gbaker · #1 07-05-2012, 11:01 AM

I have a problem with some code that I wrote. It gives me an error called Method of data member not found. Don't understand and can't fix it.
Can you help???

Code:

With Sheet2
 
        'get rid of any null strings stored in the cells for Print
        With .Range("Cp3:Df3980")
            .Value = .Value
        End With
 
            With .Sort
 
           'set up the sort fields for Print on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Cp3:Cp3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Cq3:Cq3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
     End With
            'set up the rest of the sort and apply for Print
            
            .SetRange Sheet2.Range("Cp3:Df3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
    End With

macropod · #2 07-05-2012, 01:34 PM

What line is highlighted when the error occurs? How is 'Sheet2' defined?

Colin Legg · #3 07-05-2012, 03:17 PM

Scan your code and keep your eyes peeled for an "End With" which shouldn't be there.

macropod · #4 07-05-2012, 03:28 PM

Hi Colin,

Simply taking out the errant 'End With' will produce another error:
"Expected End With"
The 3rd 'End With' is in the wrong place - it should be moved to the last line.

Colin Legg · #5 07-05-2012, 03:35 PM

Hi Paul,

Yes indeed, you're right.

gbaker · #6 07-06-2012, 05:20 AM

Hi Paul and Colin,
Thanks for all your help. I figured it out and it works perfectly. I'm learning thanks to you.
Here is the final code. Workbook is too large to send.

Code:

Sub UPDATEALL()
'
' sortinput Macro
'
'
Range("C7:H3980").Select
    ActiveWorkbook.Worksheets("Input Calendar (2012)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Input Calendar (2012)").Sort.SortFields.Add Key:= _
        Range("C7:C3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Input Calendar (2012)").Sort.SortFields.Add Key:= _
        Range("D7:D3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Input Calendar (2012)").Sort.SortFields.Add Key:= _
        Range("E7:E3980"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Input Calendar (2012)").Sort
        .SetRange Range("C7:H3980")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
     End With
  
' printbnywildedsgpastevalue Macro
'
'
    Range("C2").Select
    Sheets("DATA").Visible = True
    Sheets("DATA").Select
    Range("t2:ai3976").Select
    Selection.Copy
    Range("cq2:df3976").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("dh2:du3976").Select
    Selection.Copy
    Range("dw2:ej3976").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("El2:Ey3976").Select
    Selection.Copy
    Range("fa2:fn3976").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("fp2:gc3976").Select
    Selection.Copy
    Range("ge2:gr3976").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    
'Sorts all sheets
 With Sheet2
 
        'get rid of any null strings stored in the cells for Print
        With .Range("Cp2:Df3980")
            .Value = .Value
        End With
 
          With .Sort
 
           'set up the sort fields for Print on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Cp2:Cp3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Cq2:Cq3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
     
            'set up the rest of the sort and apply for Print
            
            .SetRange Sheet2.Range("Cp2:Df3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
  End With
  With Sheet2
 
        'get rid of any null strings stored in the cells for BNY
        With .Range("Dw2:Ej3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on BNY on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Dv2:Dv3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Dw2:Dw3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
             End With
             'set up the rest of the sort and apply for BNY
            .SetRange Sheet2.Range("Dw2:Ej3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
       End With
  End With
  With Sheet2
 
        'get rid of any null strings stored in the cells for Wilde
        With .Range("fa2:fn3980")
            .Value = .Value
        End With
 
       With .Sort
            'set up the sort fields on Wilde on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Ez2:Ez3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("fa2:fa3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply for Wilde
            .SetRange Sheet2.Range("fa2:fn3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
        End With
   End With
    With Sheet2
 
        'get rid of any null strings stored in the cells for DS Graphics
        With .Range("ge2:gr3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on DS Graphics on (data tab)
 
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("gd2:gd3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("ge2:ge3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
       
            'set up the rest of the sort and apply for DS Graphics
            .SetRange Sheet2.Range("ge2:gr3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
        End With
   End With
' UPDATEPRINT Macro
'
'
    Application.CutCopyMode = False
    Sheets("PRINT data").Visible = True
    Sheets("PRINT VIEWING CALENDAR").Visible = True
    Sheets("PRINT data").Select
    Columns("E:ca").Select
    Selection.Copy
    Sheets("PRINT VIEWING CALENDAR").Select
    Columns("E:ca").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("PRINT VIEWING CALENDAR").Copy
    ChDir "C:\Users\gbaker\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\gbaker\Desktop\PRINT VIEWING Calendar.xlsm.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
    Sheets("PRINT VIEWING CALENDAR").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("PRINT data").Select
    ActiveWindow.SelectedSheets.Visible = False
    
' UPDATEBNY Macro
'
'
    Sheets("BNY data").Visible = True
    Sheets("BNY VIEWING CALENDAR").Visible = True
    Sheets("BNY data").Select
    Columns("E:Bq").Select
    Selection.Copy
    Sheets("BNY VIEWING CALENDAR").Select
    Columns("E:Bq").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("BNY VIEWING CALENDAR").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\gbaker\Desktop\BNY VIEWING Calendar.xlsm.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
    Sheets("BNY VIEWING CALENDAR").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("BNY data").Select
    ActiveWindow.SelectedSheets.Visible = False
    
' UPDATEWILDE Macro
'
'
    Range("B5").Select
    Sheets("Input Calendar (2012)").Select
    Sheets("WILDE data").Visible = True
    Sheets("WILDE data").Select
    Sheets("WILDE VIEWING CALENDAR").Visible = True
    Sheets("WILDE data").Select
    Columns("E:Bq").Select
    Selection.Copy
    Sheets("WILDE VIEWING CALENDAR").Select
    Columns("E:Bq").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ChDir "C:\Users\gbaker\Desktop"
    Sheets("WILDE VIEWING CALENDAR").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\gbaker\Desktop\WILDE VIEWING Calendar.xlsm.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
    Sheets("WILDE VIEWING CALENDAR").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("WILDE data").Select
    ActiveWindow.SelectedSheets.Visible = False
    
' UPDATEDSG Macro
'
'
    Sheets("Input Calendar (2012)").Select
    Sheets("DS Graphics data").Visible = True
    Sheets("DS Graphics data").Select
    Sheets("DS Graphics VIEWING CALENDAR").Visible = True
    Sheets("DS Graphics data").Select
    Columns("E:Bq").Select
    Selection.Copy
    Sheets("DS Graphics VIEWING CALENDAR").Select
    Columns("E:Bq").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("DS Graphics VIEWING CALENDAR").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\gbaker\Desktop\DS Graphics VIEWING Calendar.xlsm.xlsx", FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
    Sheets("DS Graphics VIEWING CALENDAR").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("DS Graphics data").Select
    ActiveWindow.SelectedSheets.Visible = False
    
    ' NUMBERPV Macro
'
   Sheets("DATA").Select
    Columns("AJ:BI").Select
    Selection.Copy
    Columns("BK:CJ").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
' NUMBERSSORT
'get rid of any null strings stored in the cells for NUMBERS VIEWING
Sheets("DATA").Visible = True
With Sheet2
        
        With .Range("bj2:cj3981")
            .Value = .Value
        End With
 
        With .Sort
 
 'set up the sort fields for NUMBERS VIEWING on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("bj2:bj3981"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Bk2:bk3981"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply for Print
            .SetRange Sheet2.Range("bj2:cj3981")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            
         End With
       
End With
            Columns("Bj:CJ").Select
            Selection.Copy
Sheets("NUMBERS VIEWING").Visible = True
 Sheets("NUMBERS VIEWING").Select
            Columns("a:AA").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
       
Sheets("NUMBERS VIEWING").Select
    Sheets("NUMBERS VIEWING").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\gbaker\Desktop\Print Prod List View.xlsx.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close
Sheets("NUMBERS VIEWING").Visible = False
    
'Sub LISTPV()
'
' Paste Values List Data to List View Macro
'
'
    Sheets("LIST DATA").Visible = True
    Range("b7:j3981").Select
    Selection.Copy
    Sheets("LIST VIEW").Select
    Range("b7:j3981").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("LIST DATA").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Visible = False
    
'Sorts List View

'
'
'
 With Sheet4
 
        'get rid of any null strings stored in the cells for Print
        With .Range("b7:j3981")
            .Value = .Value
        End With
 
      With .Sort
 
           'set up the sort fields for Print on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("E7:E3981"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("C7:C3981"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                
            End With
 
            'set up the rest of the sort and apply for Wilde
            .SetRange Sheet2.Range("b7:j3981")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
      End With
  End With
    Sheets("LIST VIEW").Select
    Sheets("LIST VIEW").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\gbaker\Desktop\Print Prod List View.xlsx.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close
    Sheets("DATA").Visible = False
    Sheets("Input Calendar (2012)").Select
    Range("A7").Select
 
End Sub

Thanks again Gary

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
If two geographical data match in two sheets, copy unique id/code found in one sheet	alliage	Excel	1	09-01-2011 05:23 AM
Outlook 2010 problem (new member)	Cullers	Outlook	0	01-21-2011 10:18 AM
How can I use a colom of data if a specific value is found.	Grapejuice	Excel	1	08-11-2006 11:44 PM
New Member with a problem	davieG	Outlook	0	06-03-2006 02:45 PM
Retrict Method Condition	markp	Outlook	0	12-22-2005 05:38 PM