problems getting this macro to work

[stephen.ditchfield]

Hello,
I am trying to copy a sheet, saving as a new workbook, values & number formats
also with the filename taken from (A1) all with the click of a button, sounds easy!
here is the macro

Dim myFileName As String
With ActiveWorkbook
   worksheets(1).Copy 'to a new workbook
  with active sheet with.UsedRange.
Copy.PasteSpecial Paste:=xlPasteValues 'remove formulas???  
'pick up the name from some cells???
 myfilename = .range("a1").value & ".xls" myfilename = "C:\Users\Ditchy\Desktop\" & myfilename  '????
..Parent.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
..Parent.Close savechanges:=False
End With
End Sub

any and all help appreciated

regards
Ditchy
Ballarat
Australia

 

[Claus Busch]

Hi Ditchy,

Am Mon, 12 May 2014 00:29:43 -0700 (PDT) schrieb
(e-mail address removed):

I am trying to copy a sheet, saving as a new workbook, values & number formats
also with the filename taken from (A1) all with the click of a button, sounds easy!
here is the macro

try:

Sub Test()
Dim myFileName As String
Dim myPath As String

myPath = "C:\Users\Ditchy\Desktop\"
Worksheets(1).Copy

With ActiveSheet
myFileName = .Range("A1").Value & ".xls"
With .UsedRange
.Value = .Value
End With
End With

With ActiveWorkbook
.SaveAs Filename:=myPath & myFileName, FileFormat:=xlWorkbookNormal
.Close
End With
End Sub


Regards
Claus B.

 

[stephen.ditchfield]

Hi Claus,
thanks for your help.
Macro comes up with '400 error'
and does not copy and paste values & number formats to desktop with file name?
have you any more suggestions please

regards
Ditchy

 

[Claus Busch]

Hi Ditchy,

Am Mon, 12 May 2014 01:37:38 -0700 (PDT) schrieb
(e-mail address removed):

Macro comes up with '400 error'
and does not copy and paste values & number formats to desktop with file name?
have you any more suggestions please

if you run the macro from another sheet than Worksheets(1) try:

Sub Test()
Dim myFileName As String
Dim myPath As String

myPath = "C:\Users\Ditchy\Desktop\"
myFileName = ThisWorkbook.ActiveSheet.Range("A1").Value & ".xls"

Worksheets(1).Copy
With ActiveSheet.UsedRange
.Value = .Value
End With

With ActiveWorkbook
.SaveAs Filename:=myPath & myFileName, FileFormat:=xlWorkbookNormal
.Close
End With
End Sub


Regards
Claus B.

 

[stephen.ditchfield]

Hi Claus,
I still have no luck getting it to work.
Is there a way you could modify this one to copy and save as values and number formats in C:\Users\Ditchy\Desktop\. ?
This macro works but copies to desktop & does not save as values and numberformats



Sub WorkbookSaveCopyAs2()
'use the Workbook.SaveCopyAs Method to save a copy of ThisWorkbook which your are working in, with a unique name everytime:


Dim fname As String, extn As String, MyStr As String
Dim i As Integer, lastDot As Integer

'change the current directory to the ThisWorkbook directory:
ChDir ThisWorkbook.Path

'find position of last dot, to distinguish file extension:
For i = 1 To Len(ThisWorkbook.Name)
If Mid(ThisWorkbook.Name, i, 1) = "." Then
lastDot = i
End If
Next i

'extract file extension and dot before extension:
extn = Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) - lastDot + 1)
'extract workbook name excluding its name extension and dot before extension:
MyStr = Left(ThisWorkbook.Name, lastDot - 1)

'specify name for the copy - the time part in the file name will help in indentifying the last backup, besides making the name unique:
fname = MyStr & "__S_Ditchfield__" & Format(Now(), "dd-mm-yyyy ---- hh-mmAMPM") & extn


'save a copy of ThisWorkbook which your are working in, specifying a file name - use this method to save your existing work, while your current workbook remains the active workbook:
ThisWorkbook.SaveCopyAs fname



'your current workbook remains the active workbook, the saved copy remains closed:
MsgBox ActiveWorkbook.Name

End Sub


very much appreciated
regards
Ditchy

 

[Claus Busch]

Hi Stephen,

Am Tue, 13 May 2014 00:45:59 -0700 (PDT) schrieb
(e-mail address removed):

Is there a way you could modify this one to copy and save as values and number formats in C:\Users\Ditchy\Desktop\. ?
This macro works but copies to desktop & does not save as values and number formats

try:

Sub WorkbookSaveCopyAs2()
'use the Workbook.SaveCopyAs Method to save a copy of ThisWorkbook which
your are working in, with a unique name everytime:

Dim fname As String, extn As String, MyStr As String
Dim lastDot As Integer

'find position of last dot, to distinguish file extension:
lastDot = InStrRev(ThisWorkbook.FullName, ".")

'extract file extension and dot before extension:
extn = Right(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) - lastDot
+ 1)
'extract workbook name excluding its name extension and dot before
extension:
MyStr = Left(ThisWorkbook.FullName, lastDot - 1)

'specify name for the copy - the time part in the file name will help in
indentifying the last backup, besides making the name unique:
fname = MyStr & "__S_Ditchfield__" & Format(Now(), "dd-mm-yyyy ----
hh-mm AMPM") & extn


'save a copy of ThisWorkbook which your are working in, specifying a
file name - use this method to save your existing work, while your
current workbook remains the active workbook:
ThisWorkbook.SaveCopyAs fname


'your current workbook remains the active workbook, the saved copy
remains closed:
MsgBox ActiveWorkbook.Name

End Sub


Regards
Claus B.

 

[GS]

Here's how I handle this when time-stamping...

Sub SaveWkbAsCopy3()
Dim sUniqueName$, vFileInfo
Const sMyName$ = "_S.Ditchfield_" '//a fixed value?
vFileInfo = Split(ThisWorkbook.FullName, ".")

'Build timestamp (unique filename)
sUniqueName = Format(Now(), "dd-mm-yyyy----hh-mm-AMPM.") '//varies

'Save a copy with the new unique name appended
ThisWorkbook.SaveCopyAs Join(vFileInfo, sMyName & sUniqueName)
MsgBox ActiveWorkbook.name
End Sub

...where your personal stamp is held in a constant (which I shortened),
and the fullname of the file running the code is split into a 2 element
array using the dot as the delimiter. (vFileInfo(0) contains everything
left of the dot, vFileInfo(1) contains the file extension.

The timestamp is then created in the desired format. (Note that I
replaced " AMPM" with "-AMPM." so there's no spaces in the (long)
filename, and the trailing dot is included here!)

Finally, the array is re-assembled using your personal stamp and the
timestamp as the delimter, and passed as the filename arg for the
SaveCopyAs method.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[stephen.ditchfield]

Thanks Garry
that worked a treat, is there any way to save the file as numbers & value formats only, and in a designated directory, eg "C:\Users\Ditchy\Work Related"

your help is much appreciated
regards
Ditchy

 

[GS]

is there any way to save the file as numbers & value formats only,

and in a designated directory, eg "C:\Users\Ditchy\Work Related"

Do you mean 'also' SaveCopyAs to here or 'instead of' SaveCopyAs to the
existing fullname path?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion