Compile error: Duplicate declaration in current scope

hinesgg

New Member
Joined
Jul 11, 2012
Messages
30
I have a workbook with multiple sheets. I'm trying to save each sheet as a separate workbook.

Here's my code:
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You don't need to re-declare your variable (wb)....You should only have one instance of "Dim wb as Workbook"
 
Upvote 0
And how should I end the code? I'm getting another error at End Sub.

Code:
    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]
 
Upvote 0
hinesgg

Can you post the code you have now?
 
Upvote 0
It looks like you haven't closed your With statement with and "End with"

Try this:
Code:
[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]
 
Upvote 0
It looks like you haven't closed your With statement with and "End with"

Here's my code:
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
 
Upvote 0
Each With needs an End With and you are missing at least 4 End Withs.

This,
Code:
 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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,370
Messages
6,171,686
Members
452,418
Latest member
kennettz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top