|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 11:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com