error in code, Clear Clipboard Memory

[Corey]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Other Code Precedes this code
Range("K2").Copy
Sheets("Enter - Exit").Select
If Range("I14").Value = "" Then ' <=========== Option 1
Range("I14").PasteSpecial xlPasteValues
Application.CutCopyMode = False ' <================= Think this code does not clear the clipboard memory, as cell in other sheet is still copied, causing the error. ??
Sheet4.Select
Range("A1").Select
Else
If Range("I16").Value = "" Then ' <========== Option 2
Range("I16").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheet4.Select
Range("A1").Select
Else
If Range("I18").Value = "" Then ' <========== Option 3
Range("I18").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheet4.Select
Range("A1").Select
Application.DisplayAlerts = True
Else ' <============================ Option 4
Application.DisplayAlerts = True
msgbox "Copy/Paste Not Required!!!"
' More Code follows this line
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I get a error when this runs for some reason.

I think it is because the clipboard is not clearing for some reason.

Is there another method to clear the clipboard memeory (After Copy) ?


Ctm.

 

[Guest]

Hi Corey -

Try adding three "End If" statements after your last line (the msgbox
statement) to close out your if...then...else structure:

MsgBox "Copy/Paste Not Required!!!"
End If <<<|
End If <<<|Add these 3 lines one after the other and see what happens.
End If <<<|

 

[Corey]

Still getting an error message.
I have placed a : Application.CutCopyMode = False in between every line after the paste occurs to try to empty the clipboard, but to no avail.



Getting frustrated now :-(

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Other Code Precedes this code
Range("K2").Copy
Sheets("Enter - Exit").Select
If Range("I14").Value = "" Then ' <=========== Option 1
Range("I14").PasteSpecial xlPasteValues
Application.CutCopyMode = False ' <================= Think this code does not clear the clipboard memory, as cell in other sheet is still copied, causing the error. ??
Sheet4.Select
Range("A1").Select
Else
If Range("I16").Value = "" Then ' <========== Option 2
Range("I16").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheet4.Select
Range("A1").Select
Else
If Range("I18").Value = "" Then ' <========== Option 3
Range("I18").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheet4.Select
Range("A1").Select
Application.DisplayAlerts = True
Else ' <============================ Option 4
Application.DisplayAlerts = True
msgbox "Copy/Paste Not Required!!!"
' More Code follows this line
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I get a error when this runs for some reason.

I think it is because the clipboard is not clearing for some reason.

Is there another method to clear the clipboard memeory (After Copy) ?


Ctm.

 

[NickHK]

Not sure what you are expecting to happen, but I suspect your logic is
flawed somewhere with your If blocks.
Does this make it more clear ?

Range("K2").Copy

With Sheets("Enter - Exit")
Select Case True
Case .Range("I14").Value = ""
.Range("I14").PasteSpecial xlPasteValues
Case .Range("I16").Value = ""
.Range("I16").PasteSpecial xlPasteValues
Case .Range("I18").Value = ""
.Range("I18").PasteSpecial xlPasteValues
Case Else
'Do nothing
MsgBox "Copy/Paste Not Required!!!"
End Select
End With

Application.CutCopyMode = False

It would help if you said which error.

NikcHK

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Other Code Precedes this code
Range("K2").Copy
Sheets("Enter - Exit").Select
If Range("I14").Value = "" Then ' <=========== Option 1
Range("I14").PasteSpecial xlPasteValues
Application.CutCopyMode = False ' <================= Think this code does
not clear the clipboard memory, as cell in other sheet is still copied,
causing the error. ??
Sheet4.Select
Range("A1").Select
Else
If Range("I16").Value = "" Then ' <========== Option 2
Range("I16").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheet4.Select
Range("A1").Select
Else
If Range("I18").Value = "" Then ' <========== Option 3
Range("I18").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheet4.Select
Range("A1").Select
Application.DisplayAlerts = True
Else ' <============================ Option 4
Application.DisplayAlerts = True
msgbox "Copy/Paste Not Required!!!"
' More Code follows this line
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I get a error when this runs for some reason.

I think it is because the clipboard is not clearing for some reason.

Is there another method to clear the clipboard memeory (After Copy) ?


Ctm.

 

[Corey]

Here is my entire code, i still get an ERROR in it after a value is copied
and pasted to another sheet.


Code Purpose,
Creates a copy of sheet1 and places the INPUTBOX Value into cell K2,
Then prompts you for 2 x rates of pay,
then copies the value in K2 and names the sheet the same value,
then copied the K2 value and places that value in the next blank cell in a
designated range in the "Enter - Exit" (named) sheet.
From there i can (with other code) click on the cell and be navigated to the
required sheet the cell is named.

The code below gives me an error of:

Run Time Error 424:

Object Required.

I notice that the K2 cell back in the copied sheet, still is Copied
(Highlighted border on it)

ALSO, trying to have the code pick up if a Sheet with the INPUTBOX Value
ALREADY as a Sheet Name to msgbox an alert.


Any help is appreciated.

~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub InputNewName()
' ADD New Employee TimeSheet Here
res = InputBox("Please Enter the Name for the New Employee", "Title....")
If res = "" Then
Exit Sub
Else
ActiveWorkbook.Activate
Sheets("AAA").Select
Worksheets("AAA").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = res ' Name the TimeSheet the Value in the Input Box
Range("K2").Value = res
Application.CutCopyMode = False
Range("X3").ClearContents
Range("X6").ClearContents
Range("A1").Select
res = InputBox("What is the NORMAL Hourly Rate of Pay to be Set
At(Inclusive of Casual Loading) ?", "Title....")
If res = "" Then
MsgBox "There MUST be a Value Placed in Cell(X3) Before the 1st
Payweek.", , "Title...."
GoTo here
Else
Range("X3").Value = res
End If
res = InputBox("What is the SITE Hourly rate of Pay to be set at ? ",
"Title....")
If res = "" Then
MsgBox "There MUST be a Value Placed in Cell(X6) Before the 1st
Payweek.", , "Title...."
Else
Range("X6").Value = res
End If
Application.DisplayAlerts = False
Call ClearTimeSheetValues
End If
here:
Range("K2").Copy
' Error in this section when pasting value into Enter - Exit Sheet.
<================ ERROR SECTION
With Sheets("Enter - Exit")
Select Case True
Case .Range("I14").Value = ""
.Range("I14").PasteSpecial xlPasteValues
Application.CutCopyMode = False ' <==== Not sure if these lines are
working properly, as copied cell is still highlighted, when i get error.
Sheets("Enter - Exit").Select
Case .Range("I16").Value = ""
.Range("I16").PasteSpecial xlPasteValues
Application.CutCopyMode = False ' <==== Not sure if these lines are
working properly, as copied cell is still highlighted, when i get error.
Sheets("Enter - Exit").Select
Case .Range("I18").Value = ""
.Range("I18").PasteSpecial xlPasteValues
Application.CutCopyMode = False ' <==== Not sure if these lines are
working properly, as copied cell is still highlighted, when i get error.
Sheets("Enter - Exit").Select
Case .Range("B20").Value = ""
.Range("B20").PasteSpecial xlPasteValues
Application.CutCopyMode = False ' <==== Not sure if these lines are
working properly, as copied cell is still highlighted, when i get error.
Sheets("Enter - Exit").Select
Case .Range("F20").Value = ""
.Range("F20").PasteSpecial xlPasteValues
Application.CutCopyMode = False ' <==== Not sure if these lines are
working properly, as copied cell is still highlighted, when i get error.
Sheets("Enter - Exit").Select
Case .Range("I20").Value = ""
.Range("I20").PasteSpecial xlPasteValues
Application.CutCopyMode = False ' <==== Not sure if these lines are
working properly, as copied cell is still highlighted, when i get error.
Sheets("Enter - Exit").Select
Case Else
MsgBox "Please Contact CTM, to Add More Places to Link the New
TimeSheet to.", , "Title...."
Application.DisplayAlerts = True
Exit Sub
End Select
End With
res = InputBox("The Name Entered is ALREADY in Use, Please Try again, or
Cancel to Exit.", "Title....") ' <==== Do not know how to do this in code.
IE. Sheet with InputBox Value already Used.
If res = "" Then
Application.DisplayAlerts = False
With activeworksheet
..Select
..Delete
GoTo bb
End With
Sheet4.Select
Application.CutCopyMode = False
GoTo bb
Else
End If
Sheets(res.Value).Select
Application.CutCopyMode = False
Sheet4.Select
Application.DisplayAlerts = True
'exitpoint:
GoTo bb
AA:
bb:
Sheet4.Select
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~


ctm