Excel - Saving

[ajw150]

Hi,

Please can you advise how it i possible to do the following.

I would like for a user to click a button on worksheet "Mail", and fo
worksheet "EAR" to save as value of Startdata!A1" and todays date.
would then like sheet EAR to print and the file to stay open. Also, th
contents of EAR are derived from being equal to other sheets, is i
possible to hold the data so EAR's content remains intact?

Thanks

PS. Got this so far:

Sub Save()
Dim strdate As String
Dim uname

With ActiveWorkbook.Worksheets("Sheet1")
uname = .Range("A1").Value & " " & _
.Range("A2").Value
End With
strdate = Format(Now, "dd-mm-yy")
ActiveWorkbook.SaveAs ThisWorkbook.Name _
& "" & strdate & " " & uname & ".xls"
ActiveWorkbook.Worksheets("EAR").PrintOut
ActiveWorkbook.Close False
End Su

 

[Dave Peterson]

This might get you closer:

Option Explicit
Sub SaveMe()

Dim myFileName As String
Dim newWks As Worksheet
Dim CurWkbk As Workbook

Set CurWkbk = ActiveWorkbook

myFileName = CurWkbk.Worksheets("startdata").Range("a1").Value _
& "_" & Format(Now, "dd-mm-yy") & ".xls"

CurWkbk.Worksheets("EAR").Copy 'to a new workbook
Set ActiveSheet = newWks

With newWks.UsedRange
'convert to values
.Value = .Value
End With

newWks.PrintOut preview:=True 'for testing!
newWks.Parent.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal

'either
CurWkbk.Close savechanges:=False 'true
'or
newWks.Parent.Close savechanges:=False

End Sub

(I changed the name of the sub. I don't like using reserved words as procedure
names.)

And your code seemed to differ from your description.

 

[ajw150]

Dave,

Thanks for your answer. I entered it, seems to make sense, but no
receive the following error. Please can you advise as to what it mean
and how it can be solved.

Object doesnt support this method or property?

Thanks

Andre

 

[Dave Peterson]

Oops.

change this:
Set ActiveSheet = newWks
to
set newwks = activesheet

(I should have taken time to set up a test workbook. Sorry.)

 

[ajw150]

Great Stuff Dave.

Thank

 

[Dave Peterson]

Glad you got it working. Sorry about the, er, typo.

 

[ajw150]

Hi,

Please can someone help???

Its been a while, but after using this script where a sheet is copied
and saved in the root directory of the original file, how can it be
changed to create a popup "save as" screen so the user can choose??



Sub SaveMe()

Dim myFileName As String
Dim newWks As Worksheet
Dim CurWkbk As Workbook

Set CurWkbk = ActiveWorkbook

myFileName = CurWkbk.Worksheets("Mail").Range("p17").Value _
& "_" & Format(Now, "dd-mm-yy") & ".xls"

CurWkbk.Worksheets("EAR").Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks.UsedRange
'convert to values
..Value = .Value
End With

newWks.PrintOut preview:=True 'for testing!
newWks.Parent.SaveAs Filename:=myFileName,
FileFormat:=xlWorkbookNormal

'either
CurWkbk.Close savechanges:=False 'true
'or
newWks.Parent.Close savechanges:=False

End Sub


Regards

Andrew