copy excel

[Guest]

Hello

I am a newbie and would like to learn.
What can I do if I want to detect any changes made to an excel sheet, make a
copy of it and save the copy to another location? Do I create a batch file, a
VB script, a macro, or what?

Can someone help me please?

 

[Guest]

It is called a sheet change event. Below is the VBA help instructions for
the event. this is a VBA macro. the best way of creating the macro is with
the Macro learn function. Excel will generate a macro called MACRO1 (the
number automatically increaments with each new learned macro). Then the code
(not including the first line with the SUB and the last line END SUB) into
the Workbook_SheetChange macro so it automatically runs.

To generate the learn macro
1) Go to the Tools Menu - Macro - Start Recording Macro.
2) Do the copy functions you want to be included in the macro.
3) Stop Recording by going back to the Tools Menu - Maco - Stop Recording.
4) Get the macro. Right click the Tab on the bottom of the worksheet with
the worksheet name. Select view Code. On the VBAProject Window look for
module. the recorded macro will be in the Module folder.
5) Copy the macro.
6) Worksheet change Macros must ber place in the worksheet were they are
required. Look in the VBAProject window for the correct Worksheet. Click
the correct worksheet and a new VBA code window will come up. Paste the code
in this window.
7) The first line of the code starting with SUB has to replaced with the
following line

Private Sub Workbook_SheetChange(ByVal Target As Range)



---------------------------------------------------------------------------------------------

SheetChange Event
See AlsoApplies ToExampleSpecificsOccurs when cells in any worksheet are
changed by the user or by an external link.

Private Sub object_SheetChange(ByVal Sh As Object, ByVal Source As Range)
object Application or Workbook. For more information about using events
with the Application object, see Using Events with the Application Object.

Sh A Worksheet object that represents the sheet.

Source The changed range.

Remarks
This event doesn't occur on chart sheets.

Example
This example runs when any worksheet is changed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
' runs when a sheet is changed
End Su

 

[Sharad]

Do you mean only a worksheet in a workbook or entire workbook?
In any case I guess you would like to make a copy, when the file is being
saved.

If you want to save copy of only a particular worksheet in a workbook:
Define a Public boolean in a module
Insert a module and at the top write as under:

Public myShtChanged as boolean

In the worksheet_change event procedure write code:

myShtChanged = True

Thenin Workbook_BeforeSave event you can write the code:

Dim myBook as Workbook, myName as String

If myShtChanged Then

Set myBook = Sheet("[name_of_the_sheet_w/o_brackets]").Copy
myName = GetSaveAsFilename
'Above line will prompt user to enter the file name

myBook.SaveAs Filename:=myName
myBook.Close

End if

 

[Sharad]

Do you mean only a worksheet in a workbook or entire workbook?
In any case I guess you would like to make a copy, when the file is being
saved.

If you want to save copy of only a particular worksheet in a workbook:
Define a Public boolean in a module
Insert a module and at the top write as under:

Public myShtChanged as boolean

In the worksheet_change event procedure write code:

myShtChanged = True

Thenin Workbook_BeforeSave event you can write the code:

Dim myBook as Workbook, myName as String

If myShtChanged Then

Set myBook = Sheet("[name_of_the_sheet_w/o_brackets]").Copy
myName = GetSaveAsFilename
'Above line will prompt user to enter the file name

myBook.SaveAs Filename:=myName
myBook.Close

End if

 

[Guest]

Thank you very much Joel & Sharad.

I'm going to try your suggestions.