VBA to AutoSave and Copy to diff location

[jeema]

Hi,

I am having a master excel sheet which needs to be saved automatically for every xx minutes.
And after every save I need to copy the sheet1 of my master book to a new workbook in a different share drive without the VBA Macro.

Any ideas???

Regards
Malai

[GTO]

Hopefully Sheet1 doesn't have any formulas that will get botched with the move?

As to ditching the code for just that sheet, I think its much easier just not to have any code in the particular sheet's module to begin with. If there is code under the sheet, move it to ThisWorkbook, and qualify as necessary.

Mark

[jeema]

Thanks for your reply.

After some tries I am able to do it. Now the problem is the new workbook is splashing on the screen.

I use the below code to create a new workbook and copy the sheet.


Set ThisWB = ThisWorkbook

Workbooks.Add
ActiveWorkBook.SaveAs Filename:=TargetFileName
Set NewWB = ActiveWorkBook
ThisWB.Sheets("Sheet1").Copy Before:=NewWB.Sheets("Sheet1")
NewWB.Close Savechanges:=True


Is there any way to hide the splash screen while creating a new workbook??

Regards
Malai

[GTO]

Hi Malai,

You can use ScreenUpdating to eliminate most of the user's awareness as to the copy. You will still see a button added to the taskbar momentariliy.

Soemthing like:


Option Explicit

Sub exa()
Dim wbNewCopy As Workbook
Dim TargetFileName As String

'// Kill screen repaints for the moment //
Application.ScreenUpdating = False

With ThisWorkbook
'// Change to suit, just for testing... //
TargetFileName = .Path & "\" & Left(.Name, InStrRev(.Name, ".") - 1) & _
"_" & Format(Time, "hhmmss") & ".xls"

'// Set a reference to the new wb upon creation. //
Set wbNewCopy = Workbooks.Add(Template:=xlWBATWorksheet)
'// Change 'Sheet1' name to something unlikely to equal the sheetname of the//
'// sheet being copied. //
wbNewCopy.Worksheets(1).Name = "TEMP"
'// Copy after the temp sheet. //
.Worksheets("Sheet1").Copy After:=wbNewCopy.Worksheets(1)
End With

'// Kill the TEMP sheet, so we only have the sheet we want in the new wb. //
Application.DisplayAlerts = False
wbNewCopy.Worksheets(1).Delete
Application.DisplayAlerts = True

'// Save, close new wb, turn repaints back on. //
wbNewCopy.SaveAs Filename:=TargetFileName
wbNewCopy.Close SaveChanges:=False

Application.ScreenUpdating = True
End Sub

Hope that helps,

Mark

[jeema]

Hi,

Thanks for your reply.

It woked well. As you said I still see the application open and close in the task bar. Is there any way to hide that as well?

Regards
Malai

[GTO]

Hi Malai,

As far as I know, there's not really a way to hide an individual wb, other that setting .IsAddin to True, which wouldn't help for this.

I suppose you could check to see if the original wb was alone and temporarily change ShowWindowsInTaskbar.


Option Explicit

Sub exa3()
Dim _
wbNewCopy As Workbook, _
TargetFileName As String, _
bolWinsInTaskbar As Boolean

'// Kill screen repaints for the moment //
Application.ScreenUpdating = False

With ThisWorkbook
'// Change to suit, just for testing... //
TargetFileName = .Path & "\" & Left(.Name, InStrRev(.Name, ".") - 1) & _
"_" & Format(Time, "hhmmss") & ".xls"

If Workbooks.Count = 1 And Application.ShowWindowsInTaskbar Then
bolWinsInTaskbar = True
Application.ShowWindowsInTaskbar = False
End If

'// Set a reference to the new wb upon creation. //
Set wbNewCopy = Workbooks.Add(Template:=xlWBATWorksheet)
'// Change 'Sheet1' name to something unlikely to equal the sheetname of the//
'// sheet being copied. //
wbNewCopy.Worksheets(1).Name = "TEMP"
'// Copy after the temp sheet. //
.Worksheets("Sheet1").Copy After:=wbNewCopy.Worksheets(1)
End With

'// Kill the TEMP sheet, so we only have the sheet we want in the new wb. //
Application.DisplayAlerts = False
wbNewCopy.Worksheets(1).Delete
Application.DisplayAlerts = True

'// Save, close new wb, turn repaints back on. //
wbNewCopy.SaveAs Filename:=TargetFileName
wbNewCopy.Close SaveChanges:=False

If bolWinsInTaskbar Then Application.ShowWindowsInTaskbar = True

Application.ScreenUpdating = True
End Sub

That said, I think (not checked) that this might make things "jumpier" looking, if for instance, you had a couple of Word docs open at the same time.