Worksheet as file

[C3]

Hi!

First, I have a .xls file (AAA.xls) with a single (base) worksheet
("Baza") - content of one cell (B1) is "0001". I want to copy this worksheet
(because I want to save data unchanged in base worksheet) and run macro
(etc."xxx") who will take some changes, save this "changed" worksheet as a
..xls file in format "Name0001.xls" and erase that "changed" worksheet. For
next time when I'll open AAA.xls cell (B1) must grow for one number (0002)
and next saved file must have this number "Name0002.xls" and so on...

Any suggestions?

C3

 

[Tom Ogilvy]

Dim sh as Worksheet, sh1 as Worksheet
set sh = workbooks("AAA.xls").Worksheets("Baza")
sh.copy
set sh1 = Activesheet
Call xxx
sh1.parent.SaveAs Filename:=sh.parent.Path & "\Name" & _
sh1.Range("A1").Text & ".xls", Fileformat:=xlNormal
Sh1.parent.Close SaveChanges:=False
sh.Range("B1").Value = clng(sh.Range("B1").Value) + 1
sh.Parent.Close SaveChanges:=True

 

[Bob Kilmer]

'---------------------------
' In ThisWorkbook module
'---------------------------
Option Explicit

Private Sub Workbook_Open()
With ThisWorkbook.Sheets("Sheet1")
.Range("A1").Value = _
Val(.Range("A1").Text) + 1
End With
ThisWorkbook.Save
End Sub


'---------------------------
' In a standard module
'---------------------------
Option Explicit

'---------------------------
Sub ProcessWorksheet()
Application.ScreenUpdating = False
ChangeWorksheet
SaveWorksheetAsNewWorkbook
EraseWorksheet
Application.ScreenUpdating = True
End Sub

'---------------------------
Private Sub ChangeWorksheet()
'put code to change worksheet here
End Sub

'---------------------------
Private Sub SaveWorksheetAsNewWorkbook()
Dim strSavePath As String
Dim strSaveName As String
strSavePath = "K:\"

Dim wkb As Workbook
With ThisWorkbook.Sheets("Sheet1")
strSaveName = "Name"
strSaveName = strSaveName & _
Format(.Range("A1").Text, "0000")
strSaveName = strSaveName & ".xls"
.Copy
End With
Set wkb = ActiveWorkbook
wkb.SaveAs Filename:= _
strSavePath & strSaveName, _
FileFormat:=xlNormal
wkb.Close
End Sub

'---------------------------
Private Sub EraseWorksheet()
With ThisWorkbook.Worksheets("Sheet1")
Range(.Rows(2), .Rows(.Rows.Count)).Clear
End With
End Sub