ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How do you run macro automatically each time workbook is saved? (https://www.excelbanter.com/excel-programming/346587-how-do-you-run-macro-automatically-each-time-workbook-saved.html)

Husker87

How do you run macro automatically each time workbook is saved?
 
I have a macro that copies some data to another worksheet in the workbook.
It copies the data as values only. (Some of the date in the first worksheet
is result of formulas and I just want the data to be copied) Anyway, how can
I run the macro each time the workbook is saved so that the second worksheet
always has update data in it? Any ideas?

chijanzen

How do you run macro automatically each time workbook is saved?
 
Husker87:

try , PasteSpecial method


Worksheets("Sheet1").Range("A1:D10").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues


--
天行健,君*以自強不息
地勢坤,君*以厚德載物

http://www.vba.com.tw/plog/


"Husker87" wrote:

I have a macro that copies some data to another worksheet in the workbook.
It copies the data as values only. (Some of the date in the first worksheet
is result of formulas and I just want the data to be copied) Anyway, how can
I run the macro each time the workbook is saved so that the second worksheet
always has update data in it? Any ideas?


Bill Martin[_2_]

How do you run macro automatically each time workbook is saved?
 
Husker87 wrote:
I have a macro that copies some data to another worksheet in the workbook.
It copies the data as values only. (Some of the date in the first worksheet
is result of formulas and I just want the data to be copied) Anyway, how can
I run the macro each time the workbook is saved so that the second worksheet
always has update data in it? Any ideas?


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

You need to write an event driven macro which will call the other routine that
does your actual work. Look in Walkenbach's VBA book, pg 616 for the
"BeforeSave" event and a code example that just a few lines long. I'd reproduce
it here for you, but it's obviously copyrighted.

Bill

Husker87

How do you run macro automatically each time workbook is saved
 
Thanks for the reply. I think that will work. How do I get the macro to run
each time the workbook is saved or can I get the date to be constantly
updated?

"chijanzen" wrote:

Husker87:

try , PasteSpecial method


Worksheets("Sheet1").Range("A1:D10").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues


--
天行健,君*以自強不息
地勢坤,君*以厚德載物

http://www.vba.com.tw/plog/


"Husker87" wrote:

I have a macro that copies some data to another worksheet in the workbook.
It copies the data as values only. (Some of the date in the first worksheet
is result of formulas and I just want the data to be copied) Anyway, how can
I run the macro each time the workbook is saved so that the second worksheet
always has update data in it? Any ideas?


Bill Martin[_2_]

How do you run macro automatically each time workbook is saved?
 
Bill Martin wrote:
Husker87 wrote:

I have a macro that copies some data to another worksheet in the workbook.
It copies the data as values only. (Some of the date in the first worksheet
is result of formulas and I just want the data to be copied) Anyway, how can
I run the macro each time the workbook is saved so that the second worksheet
always has update data in it? Any ideas?



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

You need to write an event driven macro which will call the other routine that
does your actual work. Look in Walkenbach's VBA book, pg 616 for the
"BeforeSave" event and a code example that just a few lines long. I'd reproduce
it here for you, but it's obviously copyrighted.

Bill

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

Actually, to avoid a trip to the library there are links on the web you can find
with a Google search on Excel and BeforeSave, or use Google to search this board
for the BeforeSave event. Here are a couple of the links:

http://msdn2.microsoft.com/en-us/lib...eforesave.aspx

http://www.mvps.org/dmcritchie/excel...htm#beforesave

Good luck...

Bill

chijanzen

How do you run macro automatically each time workbook is saved
 
Husker87:

tyr, Link:=True

Worksheets("Sheet1").Range("A1:D10").Copy
Sheets("Sheet2").Range("A1").Activate
ActiveSheet.Paste Link:=True

--
天行健,君*以自強不息
地勢坤,君*以厚德載物

http://www.vba.com.tw/plog/


"Husker87" wrote:

Thanks for the reply. I think that will work. How do I get the macro to run
each time the workbook is saved or can I get the date to be constantly
updated?

"chijanzen" wrote:

Husker87:

try , PasteSpecial method


Worksheets("Sheet1").Range("A1:D10").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues


--
天行健,君*以自強不息
地勢坤,君*以厚德載物

http://www.vba.com.tw/plog/


"Husker87" wrote:

I have a macro that copies some data to another worksheet in the workbook.
It copies the data as values only. (Some of the date in the first worksheet
is result of formulas and I just want the data to be copied) Anyway, how can
I run the macro each time the workbook is saved so that the second worksheet
always has update data in it? Any ideas?



All times are GMT +1. The time now is 08:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com