I have what started out as a simple little code that i played with to
much and made too complicated for me to wrap my little head around.
Can you take gander at it and clean it up a little.
It may be hard for you to tell without running the same program as i d
at work. Basically i run a report, then export it to my clipboard.
the code does (is supposed to do) the rest.
A few problems i am having include: the last msgbox doesn't pop up. i
P&H Sales or anything listed after it is 0, it returns Cust Serv'
numbers instead of "n/c" like i asked.
Also, I want there to be an error message like 'Please export data t
your clipboard". I'm new to the 'on error' function so i don't kno
how to do that.
Any help would be very much appreciated!

here is the code:

Private Sub CommandButton1_Click()
If Sheet1.Name = "armdore" Then
Sheet1.Name = "Ardmore"
Else
End If

Msg = "Did you Export the CMS Data to your Clipboard?"
Style = vbYesNo + vbDefaultButton2
Title = "QUESTION"
Ctxt = 1000
response = MsgBox(Msg, Style, Title, Help, Ctxt)

If response = vbYes Then

Application.ScreenUpdating = False

Worksheets("RECAP").Select
On Error GoTo cancelled
Columns("aa:iv").ClearContents
Sheet2.Paste Destination:=Sheet2.Range("aA1")
On Error GoTo cancelled
Sheets("RECAP").Range("ah7").Select
Sheets("ardmore").Select
Range("c9").Select

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(0, 1).Select

End If

Loop Until IsEmpty(ActiveCell) = True
'inbound
Sheets("RECAP").Select
Selection.Copy
Sheets("ardmore").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'customer service
Sheets("RECAP").Select
ActiveCell.Offset(1, 0).Select
Selection.Copy
Sheets("ardmore").Select
ActiveCell.Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'TPF sales
Sheets("RECAP").Select
ActiveCell.Offset(1, -1).Select
Selection.Copy
Sheets("ardmore").Select
ActiveCell.Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'TPF corp Sales
Sheets("RECAP").Select
ActiveCell.Offset(1, 0).Select
Selection.Copy
Sheets("ardmore").Select
ActiveCell.Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'P&H sales
Sheets("RECAP").Select
ActiveCell.Offset(2, 1).Select
Selection.Copy
Sheets("ardmore").Select
ActiveCell.Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'MC sales
Sheets("RECAP").Select
ActiveCell.Offset(-1, 0).Select
Selection.Copy
Sheets("ardmore").Select
ActiveCell.Offset(6, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'HS Sales
Sheets("RECAP").Select
ActiveCell.Offset(2, 0).Select
Selection.Copy
Sheets("RECAP").Range("ah7").Select
Sheets("ardmore").Select
ActiveCell.Offset(6, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'HS Service
Sheets("RECAP").Select
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
End If
Else
Selection.Copy
Sheets("RECAP").Range("ah7").Select
Sheets("ardmore").Select
ActiveCell.Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'MC Serv
Sheets("RECAP").Select
ActiveCell.Offset(1, 0).Select
Selection.Copy
Sheets("RECAP").Range("ah7").Select
Sheets("ardmore").Select
ActiveCell.Offset(-6, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
'P&H Serv
Sheets("RECAP").Select
ActiveCell.Offset(1, 0).Select
Selection.Copy
Sheets("RECAP").Range("ah7").Select
Sheets("ardmore").Select
ActiveCell.Offset(-6, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False

MsgBox "Service Levels and ASA's posted for " &
Sheets("RECAP").Range("ab2").Value, vbInformation, "POSTED"

Application.ScreenUpdating = True

Sheets("RECAP").Columns("z:iv").ClearContents

'ElseIf response = vbNo Then

'MsgBox "Run Daily Ops Report and Export Data to Clipboard"

End If

cancelled:

Sheets("ardmore").Select

End Sub


---
Message posted from http://www.ExcelForum.com/