Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
KingKarl
 
Posts: n/a
Default 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   Report Post  
Peter Jausovec
 
Posts: n/a
Default 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   Report Post  
KingKarl
 
Posts: n/a
Default 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   Report Post  
Peter Jausovec
 
Posts: n/a
Default 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   Report Post  
KingKarl
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

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
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
want to 'share' a workbook but Says Read only'when others try save Danielle Excel Worksheet Functions 2 October 25th 05 12:55 AM
How to copy a workbook so the buttons run the macros? - Excel Discussion (Misc queries) 4 May 15th 05 11:24 PM
Unprotect Workbook Kent Excel Discussion (Misc queries) 1 February 4th 05 02:07 AM
How do you only save 4 pages of the workbook Cathy Excel Worksheet Functions 1 February 1st 05 08:27 PM


All times are GMT +1. The time now is 06:16 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"