Copying a cell value to a different sheet with a macro
 

To populate a combo box using validation a value of a cell on another sheet
(a job name) is copied to another sheet within a macro. Once the jobname has
been pasted I am currently inserting another row so that the next time a
paste is done the jobname in the cell is not overridden. Please view code
below

Sheets("Job").Select
ActiveSheet.Unprotect
ActiveSheet.Range("C30").Select
'Selection.Locked = False
'Selection.FormulaHidden = False
ActiveSheet.Paste
'Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("B10").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

It works fine if I do not create a new row remming out the line
Selection.Insert Shift:=xlDown
but I lose the name of the last entry so the user cannot retrieve that job.
The error I get is

Run-time error 1004. Paste method of worksheet failed.

I then have to end. The paste happens before the insert row so I am
confused. Is there a fix or a different way of populating a combo box using
data validation?

Many thanks

Nathan

Copying a cell value to a different sheet with a macro
 

Sub FFFF()
Selection.Copy
Sheets("Job").Unprotect
Selection.Copy Destination:= _
Sheets("Job").Range("C30")
Sheets("Job").Range("C30").Insert Shift:=xlDown
Sheets("Job").Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
End Sub

Worked fine for me.

--
Regards,
Tom Ogilvy



"NathanG" wrote in message
...
To populate a combo box using validation a value of a cell on another
sheet
(a job name) is copied to another sheet within a macro. Once the jobname
has
been pasted I am currently inserting another row so that the next time a
paste is done the jobname in the cell is not overridden. Please view code
below

Sheets("Job").Select
ActiveSheet.Unprotect
ActiveSheet.Range("C30").Select
'Selection.Locked = False
'Selection.FormulaHidden = False
ActiveSheet.Paste
'Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("B10").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

It works fine if I do not create a new row remming out the line
Selection.Insert Shift:=xlDown
but I lose the name of the last entry so the user cannot retrieve that
job.
The error I get is

Run-time error 1004. Paste method of worksheet failed.

I then have to end. The paste happens before the insert row so I am
confused. Is there a fix or a different way of populating a combo box
using
data validation?

Many thanks

Nathan