Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I save a workbook as a new workbook by using macros?
I want to save my workbook as a new workbook with the name from cell b3 and
cell b2 in the folder c:\my spreadsheets. In addition I want Excel to alert me if there is nothing in these cells. The workbook has two sheets called "fordeling" and "statistikk" and it is the cells in "fordeling" I want to use as the filename. The last thing is that I dont want the new workbook to get cell values from the original document when opening both of them and vice versa. Is this possible? Thanx in advance |
#2
|
|||
|
|||
How do I save a workbook as a new workbook by using macros?
Hi,
If I understand correctly: you want to create a new file which gets the name from some cell and this cell value should be empty in the new file. Try it like this: Dim sFilename As String sFilename = Range("B3").Value ' new file name is in cell B3 If (Len(sFilename) = 0) Then ' check if cell is empty MsgBox ("Cell is empty!") Else Range("B3").Value = "" 'delete the contents of the cell Dim path As String path = "C:\" & sFilename & ".xls" ' path to new file e.g. C:\MyFile.xls ThisWorkbook.SaveCopyAs path ' save the copy Range("b3").Value = sFilename ' restore the cell value End If Hope this helps. -- Lep pozdrav/Best regards, Peter Jaušovec http://blog.jausovec.net http://office.jausovec.net "KingKarl" je napisal: I want to save my workbook as a new workbook with the name from cell b3 and cell b2 in the folder c:\my spreadsheets. In addition I want Excel to alert me if there is nothing in these cells. The workbook has two sheets called "fordeling" and "statistikk" and it is the cells in "fordeling" I want to use as the filename. The last thing is that I dont want the new workbook to get cell values from the original document when opening both of them and vice versa. Is this possible? Thanx in advance |
#3
|
|||
|
|||
How do I save a workbook as a new workbook by using macros?
Hi...
No, not quite right... I want the new file to be named after b3 and b2 (b3=year, b2=month) and all of the cells shall keep their values after the save, but they shall not change if I open the original document. "Peter Jausovec" wrote: Hi, If I understand correctly: you want to create a new file which gets the name from some cell and this cell value should be empty in the new file. Try it like this: Dim sFilename As String sFilename = Range("B3").Value ' new file name is in cell B3 If (Len(sFilename) = 0) Then ' check if cell is empty MsgBox ("Cell is empty!") Else Range("B3").Value = "" 'delete the contents of the cell Dim path As String path = "C:\" & sFilename & ".xls" ' path to new file e.g. C:\MyFile.xls ThisWorkbook.SaveCopyAs path ' save the copy Range("b3").Value = sFilename ' restore the cell value End If Hope this helps. -- Lep pozdrav/Best regards, Peter Jaušovec http://blog.jausovec.net http://office.jausovec.net "KingKarl" je napisal: I want to save my workbook as a new workbook with the name from cell b3 and cell b2 in the folder c:\my spreadsheets. In addition I want Excel to alert me if there is nothing in these cells. The workbook has two sheets called "fordeling" and "statistikk" and it is the cells in "fordeling" I want to use as the filename. The last thing is that I dont want the new workbook to get cell values from the original document when opening both of them and vice versa. Is this possible? Thanx in advance |
#4
|
|||
|
|||
How do I save a workbook as a new workbook by using macros?
Hi,
Well then the solution is similar Dim sFilename As String sFilename = Range("B3").Value & Range("B2").Value If (Len(Range("B3").Value) = 0 OR Len(Range("B2").Value) = 0) Then MsgBox ("One of the cells is empty!") Else Dim path As String path = "C:\" & sFilename & ".xls" ThisWorkbook.SaveCopyAs path End If -- Lep pozdrav, Peter Jaušovec http://blog.jausovec.net http://office.jausovec.net "KingKarl" je napisal: Hi... No, not quite right... I want the new file to be named after b3 and b2 (b3=year, b2=month) and all of the cells shall keep their values after the save, but they shall not change if I open the original document. "Peter Jausovec" wrote: Hi, If I understand correctly: you want to create a new file which gets the name from some cell and this cell value should be empty in the new file. Try it like this: Dim sFilename As String sFilename = Range("B3").Value ' new file name is in cell B3 If (Len(sFilename) = 0) Then ' check if cell is empty MsgBox ("Cell is empty!") Else Range("B3").Value = "" 'delete the contents of the cell Dim path As String path = "C:\" & sFilename & ".xls" ' path to new file e.g. C:\MyFile.xls ThisWorkbook.SaveCopyAs path ' save the copy Range("b3").Value = sFilename ' restore the cell value End If Hope this helps. -- Lep pozdrav/Best regards, Peter Jaušovec http://blog.jausovec.net http://office.jausovec.net "KingKarl" je napisal: I want to save my workbook as a new workbook with the name from cell b3 and cell b2 in the folder c:\my spreadsheets. In addition I want Excel to alert me if there is nothing in these cells. The workbook has two sheets called "fordeling" and "statistikk" and it is the cells in "fordeling" I want to use as the filename. The last thing is that I dont want the new workbook to get cell values from the original document when opening both of them and vice versa. Is this possible? Thanx in advance |
#5
|
|||
|
|||
How do I save a workbook as a new workbook by using macros?
Thanx a lot... This worked fine :-) The only thing I needed to do was to
delete the line "ThisWorkbook.SaveCopyAs path " and replace it with "ThisWorkbook.SaveCopyAs path ' save the copy" Thanx again :-) *Karl Erik* "Peter Jausovec" wrote: Hi, Well then the solution is similar Dim sFilename As String sFilename = Range("B3").Value & Range("B2").Value If (Len(Range("B3").Value) = 0 OR Len(Range("B2").Value) = 0) Then MsgBox ("One of the cells is empty!") Else Dim path As String path = "C:\" & sFilename & ".xls" ThisWorkbook.SaveCopyAs path End If -- Lep pozdrav, Peter Jaušovec http://blog.jausovec.net http://office.jausovec.net "KingKarl" je napisal: Hi... No, not quite right... I want the new file to be named after b3 and b2 (b3=year, b2=month) and all of the cells shall keep their values after the save, but they shall not change if I open the original document. "Peter Jausovec" wrote: Hi, If I understand correctly: you want to create a new file which gets the name from some cell and this cell value should be empty in the new file. Try it like this: Dim sFilename As String sFilename = Range("B3").Value ' new file name is in cell B3 If (Len(sFilename) = 0) Then ' check if cell is empty MsgBox ("Cell is empty!") Else Range("B3").Value = "" 'delete the contents of the cell Dim path As String path = "C:\" & sFilename & ".xls" ' path to new file e.g. C:\MyFile.xls ThisWorkbook.SaveCopyAs path ' save the copy Range("b3").Value = sFilename ' restore the cell value End If Hope this helps. -- Lep pozdrav/Best regards, Peter Jaušovec http://blog.jausovec.net http://office.jausovec.net "KingKarl" je napisal: I want to save my workbook as a new workbook with the name from cell b3 and cell b2 in the folder c:\my spreadsheets. In addition I want Excel to alert me if there is nothing in these cells. The workbook has two sheets called "fordeling" and "statistikk" and it is the cells in "fordeling" I want to use as the filename. The last thing is that I dont want the new workbook to get cell values from the original document when opening both of them and vice versa. Is this possible? Thanx in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
want to 'share' a workbook but Says Read only'when others try save | Excel Worksheet Functions | |||
How to copy a workbook so the buttons run the macros? | Excel Discussion (Misc queries) | |||
Unprotect Workbook | Excel Discussion (Misc queries) | |||
How do you only save 4 pages of the workbook | Excel Worksheet Functions |