Similar SaveAs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brownti - 22 Feb 2007 17:47 GMT

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("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

Reply to this Message

Bob Phillips - 22 Feb 2007 18:13 GMT

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

Signature

---
HTH

Bob

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

> 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

Reply to this Message

brownti - 22 Feb 2007 18:22 GMT

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
[quoted text clipped - 27 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

Reply to this Message

Bob Phillips - 22 Feb 2007 18:13 GMT

Signature

---
HTH

Bob

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

Reply to this Message

Joel - 22 Feb 2007 18:23 GMT

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

Reply to this Message

brownti - 22 Feb 2007 18:32 GMT

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]
>> 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

Reply to this Message

brownti - 22 Feb 2007 18:57 GMT

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,
>
[quoted text clipped - 3 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

Reply to this Message

Joel - 22 Feb 2007 19:44 GMT

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,
>
[quoted text clipped - 8 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

Reply to this Message

Tom Ogilvy - 23 Feb 2007 00:54 GMT

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("J14:P14").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

Signature

Regards,
Tom Ogilvy

Reply to this Message