Return All Sheets to Cell A1 before save

  • Thread starter Thread starter Darryl
  • Start date Start date
D

Darryl

Hi

I have a workbook which comprises 11 worksheets. Before saving the workbook,
I would like to return all sheets to cell A1 except for two worksheets - one
called "Database-address" and the other "Database - Name" which asre to be
returned to cells A4 and A6 respectively.

How can I do this with vba code.

Thanks

Darryl
 
Darryl
Place this macro in the ThisWorkbook module. Note that you have to have
2 sheets named exactly as they are named in this macro, spaces and all. HTH
Otto

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
If ws.Name <> "Database-address" And ws.Name <> "Database - Name"
Then
Range("A1").Select
Else
If ws.Name = "Database-address" Then Range("A4").Select
If ws.Name = "Database - Name" Then Range("A6").Select
End If
Next ws
End Sub
 
Put this in the ThisWorkbook module and change sheet names and range to
suit.

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
sheets.Select
Range("a1").Select
Application.Goto sheets("sheet2").Range("a6")
Application.Goto sheets("sheet3").Range("a8")
End Sub
 
Back
Top