NEED HELP with a macro to make file copy without macro

[spk1009]

I have a workbook with 2 sheets (with macros) and I'd like to save the workbook as it to a specific location without macros...


This is what I have but the file being saved is not copying as excel.

Sub makecopy()


Dim FileSaveAsName As String


'~~> Change to relevant Path

FileSaveAsName = "L:AAA ShareAMR - Kaiser2012Kaiser OH#43.xls" & Format(Now, "yyyy-mm-dd")


On Error GoTo ErrH:


If Dir(FileSaveAsName) <> "" Then Kill FileSaveAsName


ActiveWorkbook.SaveAs Filename:=FileSaveAsName


Exit Sub


ErrH:

MsgBox FileSaveAsName & " is either readonly or open. You cannot save the file with that name."


End Sub


Sub DelMod()


Dim vbCom As Object

Set vbCom = Application.VBE.ActiveVBProject.VBComponents


vbCom.Remove VBComponent:= _

vbCom.Item("Module1")

vbCom.Item ("thisworkbook")


End Sub


thank you so much in advance....

 

[SirJB7]

Hi, spk1009!


Placing this code within the original file and running the macro will create a new workbook with the same worksheets but no macro stuff at all.

Just one consideration: it doesn't handle named ranges; it's possible to add that feature but as it was just an example my lazy side won.


If needed give a look at this file:

http://dl.dropbox.com/u/60558749/NEED%20HELP%20with%20a%20macro%20to%20make%20file%20copy%20without%20macro%20%28for%20spk1009%20at%20chandoo.org%29.xlsm


-----

[pre]

Option Explicit

Sub SaveNoMacroX()
' constants
Const ksDot = "."
Const ksDoc = "_NoMacro"
Const ksXLSX = "xlsx"
' declarations
Dim I As Integer
Dim sName As String, sFileName As String
Dim sNewName As String, sNewFileName As String
' start
'  source workbook
With ActiveWorkbook
sName = .Name
sFileName = .FullName
I = InStr(StrReverse(sName), ksDot)
sNewFileName = Left$(sName, _
Len(sName) - I - 1) & ksDoc & ksDot & ksXLSX
I = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = .Worksheets.Count
End With
'  delete
If Dir(sNewFileName, vbNormal) <> "" Then Kill sNewFileName
'  add
Workbooks.Add
Application.SheetsInNewWorkbook = I
With ActiveWorkbook
.SaveAs sNewFileName
sNewName = .Name
End With
Workbooks(sName).Activate
' process
With ActiveWorkbook
For I = 1 To .Worksheets.Count
.Worksheets(I).Cells.Copy Workbooks(sNewName).Worksheets(I).Range("A1")
Next I
End With
' end
End Sub

[/pre]
-----


Regards!

 

[Montrey]

@sirjb7

You win this time Sirjb7, I am still learning VBA.

 

[SirJB7]

@Montrey

Hi!

Everybody wins, nobody loses, if we all share our two cents.

Regards!

PS: I'd recommend you to record macros for anything you want and then open them and analyze the different object structures, it's the hardest part of all, Mike Rutherford said. The VB language itself isn't difficult.

PS2: BTW, are you going to take much long to learn it?

 

[spk1009]

thank you, SirJB7! works like a charm...I just figure out how to remove the command button?

 

[spk1009]

I can't figure out...how to remove the remove the button

 

[SirJB7]

Hi, spk1009!


The command button is placed only in the original book and not in the created without macros, so as to make easier for you to perform the operation.


If you remove it (Programmer tab, Control group, Design Mode button, right click on command button, Cut, ... et voila!... no more button) you'll have to manually run the macro (Alt-F8, select macro, Excecute).


Regards!

 

[spk1009]

Awesome....thank you!

 

[SirJB7]

Hi, spk1009!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!