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

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

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

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

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