Hi,

I'm not good in programming in VBA, but I managed to put together the
following (thanks to Susan, Ron, JLGwhiz and Dave Miller):
The purpose of this is to copy a sheet to a backupfile, so I can gather lots
of data to be put in an excelchart.
In the inputsheet there's lots of data that's the same, hence the
"-character. In the code there's a part that replaces these "-characters by
the value above.

(see topic:
http://www.microsoft.com/communities...2-35d9d717cd39 )

Yet I can't find the way to do this without making a selection.
Also, the original sheet may not by altered (which is the case now).
My third problem is the static range. I'd like to have a range that is
dynamic, cause the data in the input sheet is not always the same amount of
rows.
And I don't want any blanks in my backupfile.

Also, if you can do something with lesser code or if you can simplify the
code, it would be of much importance to me.

Thanks in advance, and here is the code:
-----

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

'change the apostrophe to previous value
For Each SourceRange In Selection
If SourceRange.Value Like Chr(34) Then
SourceRange.Value = SourceRange.Offset(-1, 0).Value
End If
Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Backup.xls") Then
Set DestWB = Workbooks("Backup.xls")
Else
Set DestWB = Workbooks.Open("o:\data\Backup.xls")
End If

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("Sheet1").Range("A11:k50")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("Sheet1")


Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)

'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

DestWB.Close savechanges:=True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function


Function bIsBookOpen_RB(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen_RB = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

-----