CODEWITHSUNDEEP
vbaexcelprintingcompiler-errors
Ben Smith
Ben Smith

Reputation: 829

Excel VBA: Print sheet code not working

Can someone please help me, I have the following code:

Sub CreateDealerCopy()
    Dim centreid
    Dim sheetno As Integer
    Dim modelno
    Dim modellist As New Collection

    Application.ScreenUpdating = False
    Sheets("-Summary").Select
    centreid = Range("B5").Value
    Sheets("-Summary").Copy Before:=Sheets(1)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Validation.Delete
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
    Selection.Delete
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2")).Select
    Selection.Delete
    ActiveSheet.Name = "Summary"
    Range("A1").Select

    Sheets("Model Summary").Select

    modellist.Add "field1"
    modellist.Add "field2"
    modellist.Add "field3"
    modellist.Add "field3"
    modellist.Add "field5"

    sheetno = 1

    For Each modelno In modellist
        Sheets("Model Summary").Select
        Range("B11").Value = modelno
        Sheets("Model Summary").Copy After:=Sheets(sheetno)
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Selection.Validation.Delete
        ActiveSheet.Name = modelno
        Range("A1").Select

        sheetno = sheetno + 1

    Next

    Sheets(Array("sheet1", "sheet2", "sheet3", "sheet4", "sheet5", "sheet6")).Copy

    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\NewFolder\" & centreid & "_" & Format$(Date, "(dd-mm-yyyy)") & "_SP" & ".xlsx", _
    FileFormat:=51

    ActiveWorkbook.Close

    Windows("Stock.xlsm ").Activate ""
    Sheets(Array"sheet1", "sheet2", "sheet3", "sheet4", "sheet5", "sheet6")).Select
    Sheets("sheet1").Activate
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True

    'Dim message As Integer
    'message = MsgBox("Copy created", vbOKOnly)

    End Sub


Sub CreateAllPeople()

Dim people

Sheets("-sheet1").Select

Range("B5").Select

Set people = Range("B5")

Range("AE10").Select

Do While ActiveCell.Value <> ""

people = ActiveCell.Value

Range("B5").Value = people

CreateDealerCopy

ActiveCell.Offset(1, 0).Select

Loop            
End Sub

And when I try to run it I get the following error:

enter image description here

I think it has something to do with the following line but am not sure how to solve the issue or what the issue is:

Windows("Stock.xlsm ").Activate ""

Upvotes: 0

Views: 327

Answers (1)

paul bica
paul bica

Reputation: 10705

The error refers to this line:

Sheets(Array"sheet1", "sheet2", "sheet3", "sheet4", "sheet5", "sheet6")).Select

Replace it with this:

Sheets(Array("sheet1", "sheet2", "sheet3", "sheet4", "sheet5", "sheet6")).Select

As @David W pointed out, this line generates the same error:

Windows("Stock.xlsm ").Activate ""

It should be:

Windows("Stock.xlsm").Activate

The extra space in "Stock.xlsm " generates a Subscript out of range error

Upvotes: 1

Related Questions