Search code examples
excelvbasaveworksheet

How to save multiple spreadsheets based on all possible values for a cell?

For concreteness, say, a user should enter a value from 1 to 99 in A1 cell of sheet1. Then sheet2 gets recalculated.

How can I generate, ex ante, a collection of all different sheet2 that may arise (with values, don't want the formulas to show)?

Solution

  • If you put (say) 99 in Sheet1 "A1" then Sheet2 will be saved as Sheet2_99.xlsx on desktop (change the path as suitable) with the procedure below in VBA Sheet1 object

    Private Sub Worksheet_Change(ByVal Target As Range)
Application.DisplayAlerts = True
If Not Intersect(Range("A1"), Target) Is Nothing Then
    Sheets("Sheet2").Copy
    ActiveWorkbook.SaveAs Filename:="C:\Users\naresh\Desktop\Sheet2_" & Target.Value & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    With ActiveSheet.UsedRange
    .Value = .Value
    End With

ActiveWorkbook.Close True
End If

End Sub