Sub report()
Application.ScreenUpdating = False
Dim res As Variant, sName As String
With Worksheets("bid")
sName = .Range("b12").Text & " - " & .Range("b20").Text
res = Application.GetSaveAsFilename(InitialFileName:=sNa me & ".xls")
If res = False Then Exit Sub
End With
Workbooks.Open Filename:="C:\Documents and Settings\tim\Desktop\reports\
Book2.xls"
ActiveWorkbook.SaveAs res
Range("N19").FormulaR1C1 = "=R4072C12"
Range("N20").FormulaR1C1 = "=R4072C20"
Range("N21").FormulaR1C1 = "=R30C13+R273C13"
Range("N22").FormulaR1C1 = "=R415C13"
Range("N23").FormulaR1C1 = "=R416C13"
Range("F9").FormulaR1C1 = "=R12C2"
Range("J14:P14").FormulaR1C1 = "=R20C2"
Range("N9").FormulaR1C1 = "=R18C13"
Range("N10").FormulaR1C1 = "=R19C13"
Range("N11").Select
ActiveWorkbook.save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"brownti via OfficeKB.com" <u31540@uwe wrote in message
news:6e323cef7ba4b@uwe...
Below is my code:
Sub report()
Application.ScreenUpdating = False
Dim res As Variant, sName As String
With Worksheets("bid")
sName = .Range("b12").Text & " - " & .Range("b20").Text
res = Application.GetSaveAsFilename(InitialFileName:=sNa me & ".xls")
If res = False Then Exit Sub
End With
Workbooks.Open Filename:="C:\Documents and
Settings\tim\Desktop\reports\
Book2.xls"
ActiveWorkbook.SaveAs res
Range("N19").Select
ActiveCell.FormulaR1C1 = "='[TEST COPY.xls]BID'!R4072C12"
Range("N20").Select
ActiveCell.FormulaR1C1 = "='[TEST COPY.xls]BID'!R4072C20"
Range("N21").Select
ActiveCell.FormulaR1C1 = _
"='[TEST COPY.xls]BID'!R30C13+'[TEST COPY.xls]BID'!R273C13"
Range("N22").Select
ActiveCell.FormulaR1C1 = "='[TEST COPY.xls]BID'!R415C13"
Range("N23").Select
ActiveCell.FormulaR1C1 = "='[TEST COPY.xls]BID'!R416C13"
Range("F9").Select
ActiveCell.FormulaR1C1 = "='[TEST COPY.xls]BID'!R12C2"
Range("J14:P14").Select
ActiveCell.FormulaR1C1 = "='[TEST COPY.xls]BID'!R20C2"
Range("N9").Select
ActiveCell.FormulaR1C1 = "='[TEST COPY.xls]BID'!R18C13"
Range("N10").Select
ActiveCell.FormulaR1C1 = "='[TEST COPY.xls]BID'!R19C13"
Range("N11").Select
ActiveWorkbook.save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

What i want is to change the '[TEST COPY.xls]BID'! to which ever file was
open that started the macro. TEST COPY.xls is a template that all other
files are saved from. the file names will always be different. thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1