CODEWITHSUNDEEP
excelvba
Reetika Choudhary
Reetika Choudhary

Reputation: 107

Create temporary sheets in excel to perform calculations

First of all, I have searched and read similar threads and I have tried the solutions that were proposed and solved the other people's problem but it hasn't worked in my case.

I basically want to create temporary excel sheet where I want to do all the calculations in the back-end and display only the output to the user in the front-end sheet.

Can anyone tell me how do I go got creating the temporary sheets and deleting them as soon as numbers are copied in the front-end sheet i.e. the sheet that user will see.

My main motive is not let the user see the various calculations that are happening in the back-end.

Upvotes: 2

Views: 12088

Answers (3)

BrakNicku
BrakNicku

Reputation: 5990

Sample code to create temp sheet:

Sub Sample()

Dim TempSheet As Worksheet

Application.ScreenUpdating = False
On Error Goto ErrorHandler:

Set TempSheet = ThisWorkbook.Worksheets.Add
TempSheet.Visible = xlSheetHidden

'Do the calculations

Application.DisplayAlerts = False
TempSheet.Delete
Application.DisplayAlerts = True

ErrorHandler:
Application.ScreenUpdating = True

End Sub

Upvotes: -1

Raugmor
Raugmor

Reputation: 566

you can create a temp sheet in VBA by using Sheets.Add.Name = "Temp Sheet" or any other name really and after you calculations done on it, delete it with Sheets("Temp Sheet").Delete bear in mind if your alerts are on it will prompt user to agree to delete the sheet, you can avoid that by using Application.DisplayAlerts = False before deleting and follow by Application.DisplayAlerts = True after

Upvotes: 5

Maciej Los
Maciej Los

Reputation: 8591

I'd suggest to hide one sheet to perform calculations instead of creating temporary sheet.

Let's say you have input sheet, where user input his data. Another sheet is hidden. There the calculations are performed.

How to hide Sheet?

Go to VBA Code editor window (ALT+F11). Select sheet you want to hide. In a "Properties" window choose option: SheetHidden. Now, you can copy data from hidden sheet into destination sheet via using macro.

ThisWorkbook.Worksheets("HiddenSheetName").Range("A1:C56").Copy
ThisWorkbook.Worksheets("VisibleSheetName").Range("A66:C106").PasteSecial xlValues

Note: i wrote above code straight from my head. Didn't tested!

Upvotes: 2

Related Questions