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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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