Copy Workbook, Saveas NewFile, Edit NewFile

[cu08senior]

Hey All,

I'm new here and starting to scratch the surface of VBA for excel. I've done a few simple commands and I know this one is going to be easy, so it'd be great to get some pointers real quick.

Essentially, I have a work book... I have several macros run, but when I'm done, I'd like to present a button to "Save as today's file" or something along those lines".

I'd like to Copy the Workbook, Open it in a New Window, Run some other code that I would record macro for (then plug it into the code), save and close workbook as filename =Range"A1" & format(now, dd-mm-yyyy).

etc..

How can I go about doing this? I don't seem to know the right commands. Thus far I can just saveas and close, but I dont want the original workbook to go away, its almost like a template I'm usuing to create daily outputs...

Thanks!

[cu08senior]

Ok. I reanalyzed:

I wish to Create A New workbook, copy 3 sheets from the old workbook (as values) and then save the new work book as OldWorkbook.Worksheet.Range("A1") & format(date, mm-dd-yyy)

then close workbook bringing me back to my original workbook.

Thoughts?

Thanks!

[cu08senior]

Hey all,

I understand that I am new, but I know this is a really simple code to execute, I'm just having trouble putting it together. Would someone mind helping out just for a few?

Thanks!

[Benzadeus]

[VBA]Sub CopySaveAsClose()
Dim _
wb As Workbook

Set wb = Workbooks.Add

ThisWorkbook.Sheets(Array("SheetName1", "SheetName2", "SheetName3")).Copy _
Before:=wb.Sheets(1)

wb.SaveAs ThisWorkbook.Path & ThisWorkbook.Sheets("SheetNameSource").Range("A1")
wb.Close
Set wb = Nothing
End Sub
[/VBA]

[mdmackillop]

Welcome to VBAX
Post #1
[vba]
Sub SaveCopy()
Dim Pth As String
Pth = "C:\AAA\" '<=== Change to suit
ActiveWorkbook.SaveCopyAs Sheets(1).Range("A1") & Format(Date, " mm-dd-yyyy") & ".xls"
End Sub
[/vba]

Post #2
[vba]Sub SaveCopy2()
Dim Pth As String, fname as string, i as Long
Pth = "C:\AAA\" '<=== Change to suit
fname = Sheets(1).Range("A1") & Format(Date, " mm-dd-yyyy") & ".xls"
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
With ActiveWorkbook
For i = 1 To 3
Sheets(i).UsedRange.Value = Sheets(i).UsedRange.Value
Next
.SaveAs Pth & fname
.Close
End With
End Sub
[/vba]

[cu08senior]

mdmackillop, thanks for that breakdown, I'm trying the second code (from post 2) but im running into a problem with my pivots.

VB is giving me this error: Run-time error '1004' Cannot enter a null value as an item or field in a PivotTable report.

Essentially I'd like to unpivot my tables and just make em values, evidently this is causing the error.

The error occurs on line "Sheets(i).UsedRange.Value = Sheets(i).UsedRange.Value

Thanks again.