VBA - Prevent the Adding of Multiple Sheets

Question

The purpose of my macro is to allow a user to select a range in their model that they want to check for hard codes. The macro then prints the worksheet, cell address, and value of the hard code on a summary sheet. The macro currently works great if you're selecting only from one sheet; however, if you extend your selection to multiple sheets, the macro will create multiple sheets instead of just one which it is intended to do. Thank you in advance for your time and help

Set RngCon = Selection.SpecialCells(xlCellTypeConstants, xlNumbers)

Set SumWS = Worksheets.Add
Username = InputBox("Please create a name for the output sheet (i.e. Whs Industry Hard Codes)")
SumWS.Name = Username

x = 1
    SumWS.Cells(x, 1) = "Worksheet"
    SumWS.Cells(x, 2) = "Address"
    SumWS.Cells(x, 3) = "Value"

For Each c In RngCon
    x = x + 1
    SumWS.Cells(x, 1) = c.Worksheet.Name
    SumWS.Cells(x, 2) = c.Address(False, False)
    SumWS.Cells(x, 3) = c.Value
Next c

Stefan Stefan · Accepted Answer · 2016-11-12T11:12:05

you could do something like that:

Sub test()

    Dim SumWS As Worksheet
    Dim ws As Worksheet
    Dim SelectedSheets() As String
    Dim n As Long
    Dim i As Long

    n = 0
    For Each ws In ActiveWindow.SelectedSheets
        ReDim Preserve SelectedSheets(n)
        SelectedSheets(n) = ws.Name
        n = n + 1
    Next

    Sheets(SelectedSheets(0)).Select
    Set SumWS = Worksheets.Add

    Debug.Print "Sum Sheet: " & SumWS.Name
    For i = LBound(SelectedSheets) To UBound(SelectedSheets)
        Debug.Print "Selected Sheet #" & i & ": " & SelectedSheets(i)
    Next i
End Sub

In the first for you save the selected sheets in an array. Then you can select one specific sheet and add your sum sheet. The second for shows how to work with the stored information. You can loop the selected sheets to get all values and - if needed - select them again.

credits to Siddharth Rout (Similar case)

VBA - Prevent the Adding of Multiple Sheets

1 Answers