Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copying data from a cell on sheet to a diff cell/sheet | Excel Worksheet Functions | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions | |||
MACRO for copying active sheet without using a certain name | Excel Programming | |||
Macro for copying named range to any sheet | Excel Programming | |||
copying sheet with macro | Excel Programming |