File Save As (cell value)

[Gerard Goodland]

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]

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]

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.

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]

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