saving a sheet in a new workbook
 

Using a button:

1) copy a sheet into a new workbook
2) save the workbook into the following directory:

"P:\Projects\Jobs\....

the next folder will be the same name as the original workbook

is this possible?

i also want to name the new workbook with today's date.

i appreciate any help

thanks,
steve

saving a sheet in a new workbook
 

steve:

try it,

Sub test()
rpath = "P:\Projects\Jobs\"
rname = Split(ThisWorkbook.Name, ".")
fpath = rpath & rname(0)
fname = fpath & "\" & Date & ".xls"
If Dir(fpath, vbDirectory) = "" Then
MkDir fpath
End If
Set wbk = Workbooks.Add
ThisWorkbook.Worksheets("Sheet1").Copy _
After:=wbk.Worksheets("Sheet1")
wbk.SaveAs fname
End Sub

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"steve" wrote:

Using a button:

1) copy a sheet into a new workbook
2) save the workbook into the following directory:

"P:\Projects\Jobs\....

the next folder will be the same name as the original workbook

is this possible?

i also want to name the new workbook with today's date.

i appreciate any help

thanks,
steve

saving a sheet in a new workbook
 

thanks for the reply chijanzen,

i got an error 400.

if this helps, i want the new workbook saved in the same directory as the
original workbook. this way, you don't need to find a folder with the same
name.

please let me knwo if you don't understand what i mean

Thanks,
steve

"chijanzen" wrote:

steve:

try it,

Sub test()
rpath = "P:\Projects\Jobs\"
rname = Split(ThisWorkbook.Name, ".")
fpath = rpath & rname(0)
fname = fpath & "\" & Date & ".xls"
If Dir(fpath, vbDirectory) = "" Then
MkDir fpath
End If
Set wbk = Workbooks.Add
ThisWorkbook.Worksheets("Sheet1").Copy _
After:=wbk.Worksheets("Sheet1")
wbk.SaveAs fname
End Sub

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"steve" wrote:

Using a button:

1) copy a sheet into a new workbook
2) save the workbook into the following directory:

"P:\Projects\Jobs\....

the next folder will be the same name as the original workbook

is this possible?

i also want to name the new workbook with today's date.

i appreciate any help

thanks,
steve

saving a sheet in a new workbook
 

steve:

i want the new workbook saved in the same directory as the
original workbook.

fname = ThisWorkbook.Path & "\" & Date & ".xls"
Set wbk = Workbooks.Add
ThisWorkbook.Worksheets("Sheet1").Copy _
After:=wbk.Worksheets("Sheet1")
wbk.SaveAs fname

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"steve" wrote:

thanks for the reply chijanzen,

i got an error 400.

if this helps, i want the new workbook saved in the same directory as the
original workbook. this way, you don't need to find a folder with the same
name.

please let me knwo if you don't understand what i mean

Thanks,
steve

"chijanzen" wrote:

steve:

try it,

Sub test()
rpath = "P:\Projects\Jobs\"
rname = Split(ThisWorkbook.Name, ".")
fpath = rpath & rname(0)
fname = fpath & "\" & Date & ".xls"
If Dir(fpath, vbDirectory) = "" Then
MkDir fpath
End If
Set wbk = Workbooks.Add
ThisWorkbook.Worksheets("Sheet1").Copy _
After:=wbk.Worksheets("Sheet1")
wbk.SaveAs fname
End Sub

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"steve" wrote:

Using a button:

1) copy a sheet into a new workbook
2) save the workbook into the following directory:

"P:\Projects\Jobs\....

the next folder will be the same name as the original workbook

is this possible?

i also want to name the new workbook with today's date.

i appreciate any help

thanks,
steve

saving a sheet in a new workbook
 

chijanzen,

it makes a new workbook, but just calls it workbook 2.

it inserts the sheet, but does not save the workbook.

it still says error 400.

"chijanzen" wrote:

steve:

i want the new workbook saved in the same directory as the
original workbook.

fname = ThisWorkbook.Path & "\" & Date & ".xls"
Set wbk = Workbooks.Add
ThisWorkbook.Worksheets("Sheet1").Copy _
After:=wbk.Worksheets("Sheet1")
wbk.SaveAs fname

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"steve" wrote:

thanks for the reply chijanzen,

i got an error 400.

if this helps, i want the new workbook saved in the same directory as the
original workbook. this way, you don't need to find a folder with the same
name.

please let me knwo if you don't understand what i mean

Thanks,
steve

"chijanzen" wrote:

steve:

try it,

Sub test()
rpath = "P:\Projects\Jobs\"
rname = Split(ThisWorkbook.Name, ".")
fpath = rpath & rname(0)
fname = fpath & "\" & Date & ".xls"
If Dir(fpath, vbDirectory) = "" Then
MkDir fpath
End If
Set wbk = Workbooks.Add
ThisWorkbook.Worksheets("Sheet1").Copy _
After:=wbk.Worksheets("Sheet1")
wbk.SaveAs fname
End Sub

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"steve" wrote:

Using a button:

1) copy a sheet into a new workbook
2) save the workbook into the following directory:

"P:\Projects\Jobs\....

the next folder will be the same name as the original workbook

is this possible?

i also want to name the new workbook with today's date.

i appreciate any help

thanks,
steve