code to run once on first open after save

P

pswanie

i got 2 computers...
lets name computer A janphi
and computer B pswanie

i use pswanie to write and edit a workbook wich i then email a excel
workbook to janphi. with the name editinventry.xls. this i got saved in
c:\windows\myfolder

janphi has a c:\windows\myfolder\inventry.xls

i need a macro to run when janphi open the editinventry.xls from the email.
this macro then needs to copy\paste info from inventry.xls {sheet1 cell
b2} to editinventry {sheet1 cell b2}.

next step will be to save as {editinventry.xls} to
c:\windows\myfolder\inventry.xls on janphi...

this need to run once only.
 
B

Bernie Deitrick

pswanie,

Name a cell

RunMacro

and then put "Yes" into that cell before emailing the workbook.

In the workbook's open code, use this

Private Sub Workbook_Open()
Dim myB As Workbook
If Environ("COMPUTERNAME") = "janphi" Then
If Range("RunMacro").Value = "Yes" Then
Range("RunMacro").Value = "No"
Set myB = Workbooks.Open("C:\windows\myfolder\inventry.xls")
myB.Worksheets("Sheet1").Range("B2").Copy _
ThisWorkbook.Worksheets("Sheet1").Range("B2")
myB.Close False
Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\windows\myfolder\inventry.xls"
Application.DisplayAlerts = True
End If
End If

End Sub

HTH,
Bernie
MS Excel MVP
 
P

pswanie

thanx... i think that is my solution but...

i got about 7 computers that i email the workbook to.. how to from there?
the macro that needs to run are named

download
 
B

Bernie Deitrick

You could remove the computername check and simply use the value of "Yes" as the flag for running
the macro.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top