+ Reply to Thread
Results 1 to 3 of 3

VBA 400 Error when switching sheets

Hybrid View

  1. #1
    Registered User
    Join Date
    10-19-2009
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    2

    VBA 400 Error when switching sheets

    Here is my code below and I keep getting an error 400 message as soon as the code goes to the Priority Unique tab. It doesnt do anything on the tab except select it and then sends off the error. Thank you for the help.

    Sub Email_Formats()
    Dim wsTemplate As Worksheet: Set wsTemplate = Worksheets("Template (2)")
    Dim iRowCount As Integer: iRowCount = wsTemplate.Range("H22").End(xlDown).Row
    Dim a As Integer
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Macro Test")
    Dim iRow1 As Integer, iRow2 As Integer, iRow3 As Integer, iRow4 As Integer, iRow5 As Integer
    iRow1 = 250: iRow2 = 550: iRow3 = 950: iRow4 = 1250: iRow5 = 1500
    
    
    For a = iRowCount To 2 Step -1
      If wsTemplate.Range("AG" & a).Value = 1 Then
          iRow1 = iRow1 + 1
          wsTemplate.Range("C" & a & ":S" & a).Copy ws1.Range("A" & iRow1)
          wsTemplate.Range("S" & a & ":S" & a).Copy ws1.Range("Q" & iRow1)
          wsTemplate.Rows(a).EntireRow.Delete
      ElseIf wsTemplate.Range("AG" & a).Value = 2 Then
          iRow2 = iRow2 + 1
          wsTemplate.Range("C" & a & ":S" & a).Copy ws1.Range("A" & iRow2)
          wsTemplate.Range("S" & a & ":S" & a).Copy ws1.Range("Q" & iRow2)
          wsTemplate.Rows(a).EntireRow.Delete
      ElseIf wsTemplate.Range("AG" & a).Value = 3 Then
          iRow3 = iRow3 + 1
          wsTemplate.Range("C" & a & ":S" & a).Copy ws1.Range("A" & iRow3)
          wsTemplate.Range("S" & a & ":S" & a).Copy ws1.Range("Q" & iRow3)
          wsTemplate.Rows(a).EntireRow.Delete
      ElseIf wsTemplate.Range("AG" & a).Value = 4 Then
          iRow4 = iRow4 + 1
          wsTemplate.Range("C" & a & ":S" & a).Copy ws1.Range("A" & iRow4)
          wsTemplate.Range("S" & a & ":S" & a).Copy ws1.Range("Q" & iRow4)
          wsTemplate.Rows(a).EntireRow.Delete
      ElseIf wsTemplate.Range("AG" & a).Value = 5 Then
          iRow5 = iRow5 + 1
          wsTemplate.Range("C" & a & ":S" & a).Copy ws1.Range("A" & iRow5)
          wsTemplate.Range("S" & a & ":S" & a).Copy ws1.Range("Q" & iRow5)
          wsTemplate.Rows(a).EntireRow.Delete
      End If
    Next a
     
    ' Once our data is on the page it is time to start manipulating it to turn it into the right for for the emails
    '
    
        Sheets("Priority Unique").Select
        Range("F251").Select
        Range(Selection, Selection.End(xlDown)).Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
            :=xlBetween
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        Selection.Copy
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "Name"
        Range("A2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        Range("A2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets("Macro Test").Select
        Range("A2").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Selection.End(xlDown).Select
        Range("A80").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A160").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Priority Unique").Delete
        Sheets.Add().Name = "Priority Unique"
     
     Range("F551").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets("Sales Unique").Select
        Range("A2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A1").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "Name"
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        Range("A2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets("Macro Test").Select
        Range("A350").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Selection.End(xlDown).Select
        Range("A420").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sales Unique").Delete
        Sheets.Add().Name = "Sales Unique"
            
            
     Range("F951").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets("Deleted Unique").Select
        Range("A2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A1").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "Name"
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        Range("A2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets("Macro Test").Select
        Range("A750").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Selection.End(xlDown).Select
            Range("A820").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Deleted Unique").Delete
        Sheets.Add().Name = "Deleted Unique"
            
        Application.DisplayAlerts = True
    End Sub
    Last edited by gugy27; 10-19-2009 at 11:28 AM.

  2. #2
    Registered User
    Join Date
    10-19-2009
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: VBA 400 Error when switching sheets

    I am taking data from the template (2) sheet to the Macro Test sheet. Once that data is copied in I am trying to generate a unique list of data for each section by copying the section over to another tab and running an advance filter on it for uniques only. My code is below and this is a very important project I am trying to do so any help would be greatly appreciated. I keep getting an error 400 message as soon as the code goes to the Priority Unique tab. It doesnt do anything on the tab except select it and then sends off the error. Thank you for the help.
    Last edited by shg; 10-19-2009 at 03:14 PM. Reason: deleted spurious quote

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: VBA 400 Error when switching sheets

    Is that a sheet in the active workbook? If not, activate the workbook, and then select the sheet.

    That said, it would be better if you removed all the Selecting from your code.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1