Similar SaveAs

[brownti via OfficeKB.com]

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:=sName & ".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("J1414").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

 

[Bob Phillips]

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:=sName & ".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("J1414").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)

 

[Bob Phillips]

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:=sName & ".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("J1414").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]

That didnt quite work, maybe i wasnt very clear. The code that you supplied
uses the newly created workbook for the cell refrences to enter into "N19"
"N20" etc...i need those to be entered from the original workbook. The macro
will fire from the workbook with all the information, open book2.xls, save as
something new, enter data from original workbook and then save and close.

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:=sName & ".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("J1414").FormulaR1C1 = "=R20C2"
Range("N9").FormulaR1C1 = "=R18C13"
Range("N10").FormulaR1C1 = "=R19C13"
Range("N11").Select
ActiveWorkbook.save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

Below is my code:
Sub report()

[quoted text clipped - 37 lines]

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

 

[Joel]

did you try
MyfileName = thisworkbook.name

It is missing the extension xls. So you may have to add it back.

 

[brownti via OfficeKB.com]

No i didnt try that. Should i just add that at the begining? Thanks,

did you try
MyfileName = thisworkbook.name

It is missing the extension xls. So you may have to add it back.

Below is my code:
Sub report()

[quoted text clipped - 36 lines]

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

 

[brownti via OfficeKB.com]

Well i got it to work by setting each value the same way as the "sName" works,
by setting them as a string at the begining and then refering to them that
way. I am wondering how i can set one of those strings equal to the sum of
two different cells.
This:
base = .Range("m539")

To:
base = .Range("m539" + "m245")

Thanks

No i didnt try that. Should i just add that at the begining? Thanks,

did you try
MyfileName = thisworkbook.name

[quoted text clipped - 6 lines]

 

[Joel]

You probably want to set LocalFilename = thisworkboo.name at the beginning.
the I would open the files a little diffferent than you arre presently doing

Set wb = Workbooks
wb.Open Filename:="C:\temp\book2.xls"

Then you can get the opened file name with Newfilename = wb.filename.

Now you will know which is the original worksheet Name and the new worksheet
names.

No i didnt try that. Should i just add that at the begining? Thanks,

did you try
MyfileName = thisworkbook.name

It is missing the extension xls. So you may have to add it back.

Below is my code:
Sub report()

[quoted text clipped - 36 lines]

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

 

[Tom Ogilvy]

Sub report()
Application.ScreenUpdating = False
Dim res As Variant, sName As String
Dim s as String
s = thisworkbook.Name
With Worksheets("bid")
sName = .Range("b12").Text & " - " & .Range("b20").Text
res = Application.GetSaveAsFilename(InitialFileName:=sName & ".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 = "='[" & s & "]BID'!R4072C12"
Range("N20")..FormulaR1C1 = "='[" & s & "]BID'!R4072C20"
Range("N21").FormulaR1C1 = _
"='[" & s & "]BID'!R30C13+'[" & s & "]BID'!R273C13"
Range("N22").FormulaR1C1 = "='[" & s & "]BID'!R415C13"
Range("N23").FormulaR1C1 = "='[" & s & "]BID'!R416C13"
Range("F9").FormulaR1C1 = "='[" & s & "]BID'!R12C2"
Range("J1414").FormulaR1C1 = "='[" & s & "]BID'!R20C2"
Range("N9").FormulaR1C1 = "='[" & s & "]BID'!R18C13"
Range("N10").FormulaR1C1 = "='[" & s & "]BID'!R19C13"
Range("N11").Select
ActiveWorkbook.save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub