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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
You don't need to re-declare your variable (wb)....You should only have one instance of "Dim wb as Workbook"
 

hinesgg

New Member
Joined
Jul 11, 2012
Messages
30
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]
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

hinesgg

Can you post the code you have now?
 

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
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]
 

hinesgg

New Member
Joined
Jul 11, 2012
Messages
30
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

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
Top