How do I save a workbook as a new workbook by using macros?

K

KingKarl

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
 
P

Peter Jausovec

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:
 
K

KingKarl

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.
 
P

Peter Jausovec

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:
 
K

KingKarl

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*
 
Top