Hi Ron
Not good with VBA but finally worked it out and it does just what I need.
Thanks.
I do have 1 problem and 1 question.
When I copy a cell which says 1 June 2009, after copying, it says 1 January
2009. The source cell is =DATE(YEAR(ys!D4)+C4,MONTH(ys!D4),DAY(ys!D4))
The formatting in source and destinantiuon is the same - English UK. This
is quite a critical point becaiuse the spreadsheet works off the dates.
My question is - how can I "Save As" after the transfer over.
I have modified the code to
Sub Copy_To_Another_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Master Copy.xls") Then
Set DestWB = Workbooks("Master Copy.xls")
Else
Set DestWB = Workbooks.Open("C:\Nevs stuff\Back and front office
23-2-9\Block Management\Master Copy.xls")
End If
'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("si").Range("a1:t500")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("ys")
Set DestRange = DestSh.Range("a1:t500")
'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Many thanks in advance for your help - its invaluable
Nev
