CODEWITHSUNDEEP
vbaexcel
george
george

Reputation: 3221

create a new workbook and save in folder location

I've got the following code snippet which copies three specific sheets and puts them in a new workbook which is saved in a folder. However, for each run there will be about 20 different workbooks created and saved. However, the particular sheets which should be saved cannot remain hidden in the process and each time I would have to make them visible and after saving make them again hidden. This of course gets reflected on the user's screen who can observe how the sheets appear and disappear. Is there any chance to avoid the appearance of the sheets before saving?

       'make the sheets visible before saving
       Sheets("Statement").Visible = True
       Sheets("Current Run").Visible = True
       Sheets("Stock").Visible = True


     Sheets(Array("Statement", "Current Run", "Stock")).Copy
                ActiveWorkbook.SaveAs Filename:="C:\solved\" & ringi & "-" & date_from & "-" &    
      date_to & ".xlsx", Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
                ActiveWorkbook.Close SaveChanges:=False


      'hide them again

       Sheets("Statement").Visible = xlSheetVeryHidden
       Sheets("Current Run").Visible = xlSheetVeryHidden
       Sheets("Stock").Visible = xlSheetVeryHidden

Upvotes: 0

Views: 126

Answers (1)

djikay
djikay

Reputation: 10628

Before starting the operation that you want to keep "hidden", set this:

Application.ScreenUpdating = False

and then once you're done with that part of the code, don't forget to revert it back like this:

Application.ScreenUpdating = True

Not sure how well it'll work for you, but it's worth a quick try.

Upvotes: 1

Related Questions