Option Explicit
Sub myCopyValue4Turnover()
Rem damit Code net so breit wird kürzere Variable
Const StrPath As String = "D:\LB_Offline\LB_2014\Umsatzliste.xlsm"
Dim sSh As Worksheet
Dim tSh As Worksheet
Dim lstCell As Range
Dim v As Long
Set sSh = Sheets("LB Offline")
Set tSh = Application.Workbooks.Open(StrPath).Sheets("2014")
Set lstCell = tSh.Cells(Rows.Count, 1).End(xlUp)
Select Case MsgBox("Zeile anfügen - ja" & Chr(13) & _
"Zeile überschreiben - wenn nein" & Chr(13) & _
"oder Abbruch", vbYesNoCancel, _
"Wohin mit den Daten")
Case 2
GoTo errorhandler
Case 7
Case 6
Set lstCell = lstCell.Offset(1, 0)
End Select
lstCell.Value = sSh.[LBvalKDNr].Value
lstCell.Offset(0, 1).Value = sSh.[LBvalKDName].Value
lstCell.Offset(0, 2).Value = sSh.[LBvalAP].Value
lstCell.Offset(0, 3).Value = sSh.[LBvalAPmail]
lstCell.Offset(0, 4).Value = sSh.[B55].Value
lstCell.Offset(0, 5).Value = sSh.[F33].Value
lstCell.Offset(0, 6).Value = sSh.[F37].Value
lstCell.Offset(0, 7).Value = sSh.[F38].Value
errorhandler:
Select Case MsgBox("Speichern - ja" & Chr(13) & _
"Speichern - nein" & Chr(13) & _
"oder Abbruch", vbYesNoCancel, _
"Schließen / Speichern")
Case 2
Exit Sub
Case 7
Application.DisplayAlerts = Not Application.DisplayAlerts
ActiveWorkbook.Close
Application.DisplayAlerts = Not Application.DisplayAlerts
Case 6
ActiveWorkbook.Save
ActiveWorkbook.Close
End Select
End Sub