Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Similar SaveAs
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 103
Default Similar SaveAs
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Similar SaveAs
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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Similar SaveAs
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 103
Default Similar SaveAs
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.

Bob Phillips wrote:
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

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

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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Similar SaveAs
did you try
MyfileName = thisworkbook.name

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

"brownti via OfficeKB.com" wrote:

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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 103
Default Similar SaveAs
No i didnt try that. Should i just add that at the begining? Thanks,

Joel wrote:
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

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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 103
Default Similar SaveAs
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


brownti wrote:
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

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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Similar SaveAs
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.

"brownti via OfficeKB.com" wrote:

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

Joel wrote:
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

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


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Similar SaveAs
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:=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 = "='[" & 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

--
Regards,
Tom Ogilvy

"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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA SaveAs Value charlie Excel Discussion (Misc queries) 4 August 27th 07 11:33 PM
More help with SaveAs Glen Mettler[_2_] Excel Programming 2 February 14th 04 02:50 AM
Help with SaveAs Glen Mettler[_2_] Excel Programming 1 February 14th 04 02:48 AM
Help with saveas Glen Mettler[_2_] Excel Programming 2 February 12th 04 11:00 PM
Help with SaveAs Glen Mettler[_2_] Excel Programming 1 January 29th 04 05:32 PM


All times are GMT +1. The time now is 09:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement