Compile error: Duplicate declaration in current scope

[hinesgg]

I have a workbook with multiple sheets. I'm trying to save each sheet as a separate workbook.

Here's my code:

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


    End With
    
   Dim wb As Workbook
     
    Sheets("728103").Copy
     
    Set wb = ActiveWorkbook
     
    With wb
     ActiveWorkbook.SaveAs Filename:="V:\VMI Reports\Reports\VMI Report - Supplier 1" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False


   Dim wb As Workbook
     
    Sheets("728104").Copy
     
    Set wb = ActiveWorkbook
     
    With wb
     ActiveWorkbook.SaveAs Filename:="V:\VMI Reports\Reports\VMI Report - Supplier 2" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False


   Dim wb As Workbook
     
    Sheets("728105").Copy
     
    Set wb = ActiveWorkbook
     
    With wb
     ActiveWorkbook.SaveAs Filename:="V:\VMI Reports\Reports\VMI Report - Supplier 3" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False


   Dim wb As Workbook
     
    Sheets("728106").Copy
     
    Set wb = ActiveWorkbook
     
    With wb
     ActiveWorkbook.SaveAs Filename:="V:\VMI Reports\Reports\VMI Report - Supplier 4" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False

The debugger is stopping on the second entry of Dim wb As Workbook

Any help you can offer would be great.

 

[Chris Macro]

You don't need to re-declare your variable (wb)....You should only have one instance of "Dim wb as Workbook"

 

[circledchicken]

Hi,

You don't need to repeat the 'Dim wb...' and 'Set wb...' statements, just do it once at the top.

Also, for what you are trying to achieve perhaps try the more streamlined approach described here:
VBA Express : Excel - Save All Worksheets As Individual Workbooks In a Special Folder

 

[hinesgg]

And how should I end the code? I'm getting another error at End Sub.

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


    




End Sub

[CODE]

 

[Norie]

hinesgg

Can you post the code you have now?

 

[Chris Macro]

It looks like you haven't closed your With statement with and "End with"

Try this:

[COLOR=#333333]

Sheets("728107").Copy[/COLOR]

[COLOR=#333333]Set wb = ActiveWorkbook[/COLOR]

[COLOR=#333333]With wb[/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs Filename:="V:\VMI Reports\Reports\VMI Report - AVC" & Format(Now, " mm-dd-yyyy"), FileFormat:=51[/COLOR]
[COLOR=#333333].Close False[/COLOR]
End With

[COLOR=#333333]End Sub[/COLOR]

 

[hinesgg]

It looks like you haven't closed your With statement with and "End with"

Here's my code:

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 Reports\amanda_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 Reports\amanda_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 Reports\amanda_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 Reports\On-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 Reports\VMI 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 Reports\Reports\VMI 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 Reports\Reports\VMI Report - AVC" & Format(Now, " mm-dd-yyyy"), FileFormat:=51
      .Close False




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


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


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




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


End Sub

Even with an End With in the code I get a Compile Error: Expected End With

 

[Norie]

Each With needs an End With and you are missing at least 4 End Withs.

This,

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

should be this.

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