File Save As (cell value)

Hi,

I have a macro that does some copy and pastes from one sheet to another
in the same workbook. I want to then save the destination sheet with the
value of a cell as the file name. So the if the entry in X4 is 101 then
I want to save the file with the name 101.xls

with activeworkbook
.saveas filename:=.worksheets("sheet1").range("x4").value
end with

If you really mean save a single sheet workbook, record a macro when you
move/copy that sheet to a new workbook. You'll have the code you need.



Gerard Goodland wrote:
>
> Hi,
>
> I have a macro that does some copy and pastes from one sheet to another
> in the same workbook. I want to then save the destination sheet with the
> value of a cell as the file name. So the if the entry in X4 is 101 then
> I want to save the file with the name 101.xls

--

Dave Peterson

Dave Peterson wrote:
> with activeworkbook
> .saveas filename:=.worksheets("sheet1").range("x4").value
> end with
>
> If you really mean save a single sheet workbook, record a macro when you
> move/copy that sheet to a new workbook. You'll have the code you need.
>
>
>
> Gerard Goodland wrote:
>
>>Hi,
>>
>> I have a macro that does some copy and pastes from one sheet to another
>>in the same workbook. I want to then save the destination sheet with the
>>value of a cell as the file name. So the if the entry in X4 is 101 then
>>I want to save the file with the name 101.xls
>
>
Thanks for the quick response. I have made a mistake. When I have the
sheet ready to save I really need to save the sheet in a new workbook
but just that sheet . I guess I would need a move or copy sheet type
function?

Record a macro when you rightclick on the worksheet tab.
choose move or copy sheet
(check the box depending on what you want)
and you'll see the code.

worksheets("sheet1").copy 'to a new workbook
'new workbook is now active
with activeworkbook
.saveas filename:=.worksheets("sheet1").range("x4").value
.close savechanges:=false 'if you're done with it.
end with



Gerard Goodland wrote:
>
> Dave Peterson wrote:
> > with activeworkbook
> > .saveas filename:=.worksheets("sheet1").range("x4").value
> > end with
> >
> > If you really mean save a single sheet workbook, record a macro when you
> > move/copy that sheet to a new workbook. You'll have the code you need.
> >
> >
> >
> > Gerard Goodland wrote:
> >
> >>Hi,
> >>
> >> I have a macro that does some copy and pastes from one sheet to another
> >>in the same workbook. I want to then save the destination sheet with the
> >>value of a cell as the file name. So the if the entry in X4 is 101 then
> >>I want to save the file with the name 101.xls
> >
> >
> Thanks for the quick response. I have made a mistake. When I have the
> sheet ready to save I really need to save the sheet in a new workbook
> but just that sheet . I guess I would need a move or copy sheet type
> function?

--

Dave Peterson