Name file automatically

[KingKarl]

I'm struggling with a problem that I bet is easy as ****, but anyway...

I want to make a macro that saves only sheet number five (named "giro") of
my workbook, and at the same time gives the file the name of the value of
cell Q1 and cell B9 on this sheet. Is this possible?

Thanx a lot in advance...

[Dave Peterson]

I'm not sure what you have in Q1 and B9, but maybe...

Option Explicit
Sub testme()

Dim wks As Worksheet

Set wks = ActiveWorkbook.Worksheets("giro")

wks.Copy 'to a new workbook

With ActiveSheet
.Parent.SaveAs Filename:="C:\" _
& .Range("q1").Value & .Range("b9").Value & ".xls", _
FileFormat:=xlWorkbookNormal
.Parent.Close savechanges:=False
End With

End Sub

There is no error checking -- make sure those values in Q1 and B9 will make a
nice name.

(Dates are problems. Windows files can't have /'s in them.)

.Parent.SaveAs Filename:="C:\" _
& format(.Range("q1").Value, "yyyy_mm_dd") _
& .Range("b9").Value & ".xls", _
FileFormat:=xlWorkbookNormal

Might work for you.

KingKarl wrote:
>
> I'm struggling with a problem that I bet is easy as ****, but anyway...
>
> I want to make a macro that saves only sheet number five (named "giro") of
> my workbook, and at the same time gives the file the name of the value of
> cell Q1 and cell B9 on this sheet. Is this possible?
>
> Thanx a lot in advance...

--

Dave Peterson

[KingKarl]

Thanx a lot...

This worked fine. I only had to add the line in the macro that tells it to
print :-)

Still, after using this I encoutered another problem: when opening the saved
file and having the original workbook open, the saved file automatically
updated itself with the latest cell values (because alle the cells in "giro"
are linked to another sheet in the workbook). Is there a way to get around
this problem?

Karl Erik Kvalsund

Dave Peterson skrev:

> I'm not sure what you have in Q1 and B9, but maybe...
>
> Option Explicit
> Sub testme()
>
> Dim wks As Worksheet
>
> Set wks = ActiveWorkbook.Worksheets("giro")
>
> wks.Copy 'to a new workbook
>
> With ActiveSheet
> .Parent.SaveAs Filename:="C:\" _
> & .Range("q1").Value & .Range("b9").Value & ".xls", _
> FileFormat:=xlWorkbookNormal
> .Parent.Close savechanges:=False
> End With
>
> End Sub
>
> There is no error checking -- make sure those values in Q1 and B9 will make a
> nice name.
>
> (Dates are problems. Windows files can't have /'s in them.)
>
> .Parent.SaveAs Filename:="C:\" _
> & format(.Range("q1").Value, "yyyy_mm_dd") _
> & .Range("b9").Value & ".xls", _
> FileFormat:=xlWorkbookNormal
>
> Might work for you.
>
> KingKarl wrote:
> >
> > I'm struggling with a problem that I bet is easy as ****, but anyway...
> >
> > I want to make a macro that saves only sheet number five (named "giro") of
> > my workbook, and at the same time gives the file the name of the value of
> > cell Q1 and cell B9 on this sheet. Is this possible?
> >
> > Thanx a lot in advance...
>
> --
>
> Dave Peterson
>

[Dave Peterson]

You could convert to values.

Option Explicit
Sub testme()

Dim wks As Worksheet

Set wks = ActiveWorkbook.Worksheets("giro")

wks.Copy 'to a new workbook

With ActiveSheet
With .UsedRange
.Value = .Value
End With
.Parent.SaveAs Filename:="C:\" _
& .Range("q1").Value & .Range("b9").Value & ".xls", _
FileFormat:=xlWorkbookNormal
.Parent.Close savechanges:=False
End With

End Sub

You'll have to play catchup with your changes.

I only added:

With .UsedRange
.Value = .Value
End With

Not sure if it's easier to add this change or your print statements.


KingKarl wrote:
>
> Thanx a lot...
>
> This worked fine. I only had to add the line in the macro that tells it to
> print :-)
>
> Still, after using this I encoutered another problem: when opening the saved
> file and having the original workbook open, the saved file automatically
> updated itself with the latest cell values (because alle the cells in "giro"
> are linked to another sheet in the workbook). Is there a way to get around
> this problem?
>
> Karl Erik Kvalsund
>
> Dave Peterson skrev:
>
> > I'm not sure what you have in Q1 and B9, but maybe...
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim wks As Worksheet
> >
> > Set wks = ActiveWorkbook.Worksheets("giro")
> >
> > wks.Copy 'to a new workbook
> >
> > With ActiveSheet
> > .Parent.SaveAs Filename:="C:\" _
> > & .Range("q1").Value & .Range("b9").Value & ".xls", _
> > FileFormat:=xlWorkbookNormal
> > .Parent.Close savechanges:=False
> > End With
> >
> > End Sub
> >
> > There is no error checking -- make sure those values in Q1 and B9 will make a
> > nice name.
> >
> > (Dates are problems. Windows files can't have /'s in them.)
> >
> > .Parent.SaveAs Filename:="C:\" _
> > & format(.Range("q1").Value, "yyyy_mm_dd") _
> > & .Range("b9").Value & ".xls", _
> > FileFormat:=xlWorkbookNormal
> >
> > Might work for you.
> >
> > KingKarl wrote:
> > >
> > > I'm struggling with a problem that I bet is easy as ****, but anyway...
> > >
> > > I want to make a macro that saves only sheet number five (named "giro") of
> > > my workbook, and at the same time gives the file the name of the value of
> > > cell Q1 and cell B9 on this sheet. Is this possible?
> > >
> > > Thanx a lot in advance...
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson