Macro to copy formula on worksheet close

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a macro that will copy a formulas when the workbook is closed. Is this possible and how do I get the formulas to copy down the entire column that has data. The formulas would be on worksheet 2 and would be copied to worksheet 1

Thanks1
 
If you can rely on a column always having data (to determine the last row of
that worksheet):

Option Explicit
Sub testme()

Dim FromWks As Worksheet
Dim ToWks As Worksheet

Set FromWks = Worksheets("sheet1")
Set ToWks = Worksheets("sheet2")

With ToWks
.Range("a1:a" & .Cells(.Rows.Count, "B").End(xlUp).Row).FormulaR1C1 _
= FromWks.Range("a1").FormulaR1C1
End With

End Sub

You can fill a range with the formula in one fell swoop. It's like selecting a
big range and writing the formula, but hitting ctrl-enter instead of just enter
(when you're doing it by hand.)

And I used column B to get the last row with data.

You could put this code into the workbook_beforeclose, but then you'd have to
add some code for saving.

And what happens if someone opens the workbook just to review and doesn't want
to change it?

As a user, I'd rather have more control of when things get updated. You could
put a button on the worksheet that said: "Click here to update formulas"
 
Thanks Dave!

The workbook is being downloaded from an Oracle database and imported into Access for report writing. A messy situation for sure. I wanted to automatically run the formulas so that when the Access macro imports the information I would have less work to do in Access. What code would I need to add to save the workbook?

Thanks again.

Lowel

----- Dave Peterson wrote: ----

If you can rely on a column always having data (to determine the last row o
that worksheet)

Option Explici
Sub testme(

Dim FromWks As Workshee
Dim ToWks As Workshee

Set FromWks = Worksheets("sheet1"
Set ToWks = Worksheets("sheet2"

With ToWk
.Range("a1:a" & .Cells(.Rows.Count, "B").End(xlUp).Row).FormulaR1C1
= FromWks.Range("a1").FormulaR1C
End Wit

End Su

You can fill a range with the formula in one fell swoop. It's like selecting
big range and writing the formula, but hitting ctrl-enter instead of just ente
(when you're doing it by hand.

And I used column B to get the last row with data

You could put this code into the workbook_beforeclose, but then you'd have t
add some code for saving

And what happens if someone opens the workbook just to review and doesn't wan
to change it

As a user, I'd rather have more control of when things get updated. You coul
put a button on the worksheet that said: "Click here to update formulas



Lowell wrote
 
Dave - Could you explain the code? I'm really new at this and could use some help. How do I get this to run on close?

Thanks - Lowell

----- Dave Peterson wrote: -----

If you can rely on a column always having data (to determine the last row of
that worksheet):

Option Explicit
Sub testme()

Dim FromWks As Worksheet
Dim ToWks As Worksheet

Set FromWks = Worksheets("sheet1")
Set ToWks = Worksheets("sheet2")

With ToWks
.Range("a1:a" & .Cells(.Rows.Count, "B").End(xlUp).Row).FormulaR1C1 _
= FromWks.Range("a1").FormulaR1C1
End With

End Sub

You can fill a range with the formula in one fell swoop. It's like selecting a
big range and writing the formula, but hitting ctrl-enter instead of just enter
(when you're doing it by hand.)

And I used column B to get the last row with data.

You could put this code into the workbook_beforeclose, but then you'd have to
add some code for saving.

And what happens if someone opens the workbook just to review and doesn't want
to change it?

As a user, I'd rather have more control of when things get updated. You could
put a button on the worksheet that said: "Click here to update formulas"


 
The code looks at column B of the ToWks to find the last used row. Then it puts
the formula you have in A1 of the FromWks.

Try it manually.

Select A1:A99
type =B1
hit ctrl-enter
That's what the code is doing, too.

There's an event called worbook_beforeclose that is called right before you
close the workbook. There's another event that's called workbook_beforesave.
Since you can save via File|Save, this can run more often.

Chip Pearson has some nice notes about workbook/worksheet events at:
http://www.cpearson.com/excel/events.htm

If you decide to put it in the workbook_beforeclose, you could add a line like:
me.save
to the code

This is placed under the ThisWorkbook module (not under a worksheet, not in a
general module):

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim FromWks As Worksheet
Dim ToWks As Worksheet

Set FromWks = Worksheets("sheet1")
Set ToWks = Worksheets("sheet2")

With ToWks
.Range("a1:a" & .Cells(.Rows.Count, "B").End(xlUp).Row).FormulaR1C1 _
= FromWks.Range("a1").FormulaR1C1
End With

Me.Save

End Sub
 
Back
Top