Duplicate declaration in current scope ошибка vba

Ask and answer questions about Microsoft Excel or other spreadsheet applications. Or discuss anything Excel.

Members

Online

by

ss90kim

Sub PerformAllTasks()

' Turn off screen updating
     Application.ScreenUpdating = False
    
    
' Create new workbook called amanda_receipts and convert content from amanda_receipts.csv to xlsx
    Workbooks.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;V:VMI Reportsamanda_receipts.csv", Destination:=Range("$A$1"))
        .Name = "amanda_receipts"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.SaveAs Filename:="V:VMI Reportsamanda_receipts.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
' UnhideAmandaReceipts
    Sheets("728101").Select
    Sheets("amanda_receipts").Visible = True


' OneOpenandCopyamanda_receiptsxlsx Macro
    Workbooks.Open Filename:= _
        "V:VMI Reportsamanda_receipts.xlsx"
    Columns("A:Q").Select
    Selection.Copy
    Windows("MASTER - Amanda Receipts.xlsm").Activate
    Sheets("amanda_receipts").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Windows("amanda_receipts.xlsx").Activate
    Application.CutCopyMode = False
    ActiveWorkbook.Close
        
' TwoSearchReplaceHLK
    Windows("MASTER - Amanda Receipts.xlsm").Activate
    Columns("E:E").Select
    Selection.Replace What:="*HLK", Replacement:="HLK", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
    
' ThreeSortbyQthenA
        ActiveWorkbook.Worksheets("amanda_receipts").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("amanda_receipts").Sort.SortFields.Add Key:=Range( _
        "Q2:Q3625"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("amanda_receipts").Sort.SortFields.Add Key:=Range( _
        "A2:A3625"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("amanda_receipts").Sort
        .SetRange Range("A1:Q3625")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    
    
' FourUnhide Tabs
    Sheets("amanda_receipts").Select
    Sheets("WhsList1").Visible = True
    Sheets("WhsList1").Select
    Sheets("Pivot").Visible = True
    Sheets("Pivot").Select
    Sheets("WhsList2").Visible = True
    Sheets("amanda_receipts").Select




' FiveOpenOnHandandCopy
    Workbooks.Open Filename:= _
        "V:VMI ReportsOn-Hand.xlsx"
    Columns("I:J").Select
    Selection.Copy
    Windows("MASTER - Amanda Receipts.xlsm").Activate
    Sheets("WhsList1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("amanda_receipts").Select
    
' SixRefreshPivot
    Sheets("Pivot").Select
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.RefreshAll
    Sheets("amanda_receipts").Select
    
' SevenPivotRemoveBlanks
    Sheets("Pivot").Select
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Warehouse")
        .PivotItems("(blank)").Visible = False
    End With
    Sheets("amanda_receipts").Select
    
' EightCopyPivottoWhsList2
    Sheets("Pivot").Select
    Columns("A:B").Select
    Selection.Copy
    Sheets("WhsList2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:B").EntireColumn.AutoFit
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("amanda_receipts").Select
    Range("A1").Select
    
    
' NineRefreshPivots
    Sheets("Pivot").Select
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.RefreshAll
    Sheets("amanda_receipts").Select
    
' TenHide Macro
    Sheets(Array("WhsList1", "Pivot", "WhsList2")).Select
    Sheets("WhsList2").Activate
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("amanda_receipts").Select
    Range("A1").Select
    
' ElevenCloseOnHandDetails
    Windows("On-Hand.xlsx").Activate
    Range("A2").Select
    ActiveWorkbook.Close
    
' HideAmandaReceiptsTab
    Sheets("amanda_receipts").Select
    ActiveWindow.SelectedSheets.Visible = False
    Range("A1").Select
    
' Save workbook as XLSX file and append date
  ActiveWorkbook.SaveAs Filename:="V:VMI ReportsVMI Report - ALL" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
    
' Turn on screen updating
    Application.ScreenUpdating = True


    
' Save each tab as a different workbook
    Dim wb As Workbook
     
    Sheets("728101").Copy
     
    Set wb = ActiveWorkbook
     
    With wb
     ActiveWorkbook.SaveAs Filename:="V:VMI ReportsReportsVMI Report - ACBEL" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False


    End With
    
 
    Sheets("728103").Copy
     
    Set wb = ActiveWorkbook
     
    With wb
     ActiveWorkbook.SaveAs Filename:="V:VMI ReportsReportsVMI Report - AVC" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False




     
    Sheets("728104").Copy
     
    Set wb = ActiveWorkbook
     
    With wb
     ActiveWorkbook.SaveAs Filename:="V:VMI ReportsReportsVMI Report - CAREER" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False


     
    Sheets("728105").Copy
     
    Set wb = ActiveWorkbook
     
    With wb
     ActiveWorkbook.SaveAs Filename:="V:VMI ReportsReportsVMI Report - DELTA" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False


     
    Sheets("728106").Copy
     
    Set wb = ActiveWorkbook
     
    With wb
     ActiveWorkbook.SaveAs Filename:="V:VMI ReportsReportsVMI Report - HAMANAKA" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False




     
    Sheets("728107").Copy
     
    Set wb = ActiveWorkbook
     
    With wb
     ActiveWorkbook.SaveAs Filename:="V:VMI ReportsReportsVMI Report - AVC" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False
    End With


End Sub