workbook issue

  • Thread starter Thread starter DENISE
  • Start date Start date
D

DENISE

Hello

I have a workbook A a sheet in which a user entered
information.
What I would like to happen is that when they save
Workbook A, the sheet is saved into workbook B

In other words a user enters stuff in one workbook and the
sheets are stored as a sort of archive in another
workbook, so that workbook A only has one sheet, but B has
many sheets.

Please help!
 
Denise
Put this macro in the workbook module of the first file. I called the
first file Alpha.xls and the second file Baker.xls. I called the lone sheet
in Alpha.xls "AlphaSheet". This macro works as follows:
The macro is triggered by the save command to save Alpha.xls.
The macro copies the AlphaSheet and pastes it as the last sheet in
Baker.xls.
The macro then saves Baker.xls.
Note that Baker.xls must be open for this to work.
Watch out for line wrapping in this message. This macro has 6 lines. The
second line starts with "With". The fourth line is a period and one word
".Save".
You access the workbook module by right-clicking on the Excel icon to the
left of the "File" in the menu across the top of the Excel screen, and
selecting View Code. Paste this macro into that module. X-out of the VBE
to return to the worksheet view. HTH Otto
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With Workbooks("Baker.xls")
Sheets("AlphaSheet").Copy after:=.Sheets(.Sheets.Count)
.Save
End With
End Sub
 
Back
Top