This assumes the sheet that contains the Date in cell A6 is named "Data" and
is located in the activeworkbook.

The archive.xls file also has a sheet named "Data" where the data is written

Sub Btn_click()
Dim sStr As String
Dim year As String
Dim month As String
Dim day As String
Dim newDate As Date
Dim sh As Worksheet, rng As Range, rng1 As Range
Dim rng2 As Range
Dim res As Variant
sStr = Worksheets("Data").Range("A6").Value
day = Mid$(sStr, 1, 2)
month = Mid$(sStr, 4, 2)
year = Mid$(sStr, 7, 4)
sStr = "p:\intranet\workbooks\info\" + year + month + day + ".xls"
'ActiveWorkbook.SaveAs (sStr)
newDate = Worksheets("Data").Range("A6").Value
Set sh = Workbooks("Archive.xls").Worksheets("Data")
Set rng = sh.Columns(1).Cells
res = Application.Match(CLng(newDate), _
rng, 0)

If Not IsError(res) Then
Set rng1 = rng(res)
With Worksheets("Data")
.Range("A6").Copy Destination:=rng1
.Range("H15:M15").Copy Destination:=rng1(1, 2)
End With
Else
Set rng2 = sh.Cells(Rows.Count, 1).End(xlUp)(2)
If rng2.Row = 2 And IsEmpty(rng2.Offset(-1, 0)) Then _
Set rng2 = rng2.Offset(-1, 0)
With Worksheets("Data")
.Range("A6").Copy _
Destination:=rng2
.Range("H15:M15").Copy Destination:=rng2(1, 2)
End With
End If

End Sub
"Colin Evans" wrote in message
...

Hi, many thanks for the prompt reply and example code.

Unfortunately not being knowledgeable in VBA I do find it difficult to
follow the example fully, I've entered the code linked to a command
button, and created a blank Archive.xls but it it doesn't seem to do
anything.

If I could try and explain a little more, following on from my original
problem I now have cell A6 which contains the date, this is reformated
to a value of YYYYMMDD which in turn is used as the filename when I
click on the save button.

What I want to happen is to have a range of cells notably A6, H15, I15,
J15, K15, L15, M15 to be saved to the archive.xls spreadsheet, which in
the first instance would be A1,B1,C1,D1,E1,F1,G1

Then as I enter new information in the original spreadsheet the date in
A6 would change along with entries in H15, I15,J15 etc, if the date in
A6 is a new date then another spreadsheet is saved using the reformated
value, and another entry is added to the archive.xls spreadsheet, i.e
A2,B2,C2,D2,E2,F2,G2

However if the date held in A6 is also present in the archive.xls
spreadsheet the row where the data isheld would be found and the
B,C,D,E,F,G cells would overwritten with the new or amended values.

Does this make it a little clearer ?

The reformatting of the date and saving of the workbook is sorted

Dim sStr As String
Dim year As String
Dim month As String
Dim day As String
sStr = Worksheets("Data").Range("A6").Value
day = Mid$(sStr, 1, 2)
month = Mid$(sStr, 4, 2)
year = Mid$(sStr, 7, 4)
sStr = "p:\intranet\workbooks\info\" + year + month + day + ".xls"
ActiveWorkbook.SaveAs (sStr)

as is my original problem of saving the spreadsheet as a html page, with
the use of another button

Dim sStr As String
Dim year As String
Dim month As String
Dim day As String
sStr = Worksheets("Data").Range("A6").Value
day = Mid$(sStr, 1, 2)
month = Mid$(sStr, 4, 2)
year = Mid$(sStr, 7, 4)
sStr = year + month + day
Worksheets("info").Copy
ActiveWorkbook.SaveAs "P:\INTRANET\colin\info\" & sStr & ".htm",
FileFormat:=xlHtml
ActiveWorkbook.Close savechanges:=False

I just need to master the archive.xls entries and then I'm nearly there.

Hope you can make sense of the above and continue to help, many thanks
once again

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!