Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose which cell to paste data
Using VBA, I have copied the contents / comments / color etc.. of a
particular cell (single or merged), and wish to manually select (using mouse pointer) which cell to paste the data. So, I guess the VBA code should pause / stop until a new cell is chosen and confirmed. Is this posible? My code so far is.... Private Sub CommandButton4_Click() ' MOVE data Dim Answer As String Dim MyNote As String MyNote = "Move selected data?" Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "For Confirmation") If Answer = vbNo Then Exit Sub End If Selection.Copy With ActiveCell .Interior.ColorIndex = xlNone .ClearContents .ClearComments .UnMerge End With MsgBox ("Please select new cell, where data will be copied to. ") ????????????? ActiveSheet.Paste End Sub Thanks in advance. Mik |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose which cell to paste data
Type:=8 will allow you to use the mouse:
Sub movem() Dim r1 As Range, r2 As Range Set r1 = Selection Set r2 = Application.InputBox(prompt:="pick destination", Type:=8) r1.Copy r2 End Sub -- Gary''s Student - gsnu201002 "Mik" wrote: Using VBA, I have copied the contents / comments / color etc.. of a particular cell (single or merged), and wish to manually select (using mouse pointer) which cell to paste the data. So, I guess the VBA code should pause / stop until a new cell is chosen and confirmed. Is this posible? My code so far is.... Private Sub CommandButton4_Click() ' MOVE data Dim Answer As String Dim MyNote As String MyNote = "Move selected data?" Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "For Confirmation") If Answer = vbNo Then Exit Sub End If Selection.Copy With ActiveCell .Interior.ColorIndex = xlNone .ClearContents .ClearComments .UnMerge End With MsgBox ("Please select new cell, where data will be copied to. ") ????????????? ActiveSheet.Paste End Sub Thanks in advance. Mik . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose which cell to paste data
On 8 May, 16:49, "Don Guillett" wrote:
Try this. Select the cell to copyfire the macroselect the paste cellmacro finishes job Sub copytoselectionSAS() Selection.Copy Application.InputBox(Prompt:= _ *"Select your copy cell", Title:="SalesAid Software", Type:=8) *With ActiveCell * * .Interior.ColorIndex = xlNone * * .ClearContents * * .ClearComments * * .UnMerge End With -- Don Guillett Microsoft MVP Excel SalesAid Software "Mik" wrote in message ... Using VBA, I have copied the contents / comments / color etc.. of a particular cell (single or merged), and wish to manually select (using mouse pointer) which cell to paste the data. So, I guess the VBA code should pause / stop until a new cell is chosen and confirmed. Is this posible? My code so far is.... Private Sub CommandButton4_Click() ' MOVE data Dim Answer As String Dim MyNote As String MyNote = "Move selected data?" Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "For Confirmation") If Answer = vbNo Then * *Exit Sub End If Selection.Copy With ActiveCell * *.Interior.ColorIndex = xlNone * *.ClearContents * *.ClearComments * *.UnMerge End With MsgBox ("Please select new cell, where data will be copied to. ") ????????????? ActiveSheet.Paste End Sub Thanks in advance. Mik- Hide quoted text - - Show quoted text - Thank you both for your reply..... The cells that are being copied / moved can sometimes have a border along one of it's edges (left or right, depending on it's location). When i run your code, the borders also copy across to the new location. I don't want this to happen. I have tried utilising the 'PasteSpecial Paste:=xlPasteAllExceptBorders' routine, but i get errors. Possibly entering it wrong. Can you please assist? Thanks Mik |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose which cell to paste data
I am not exactly sure what you have and what you want. Is this one or more
cells to copy? Do the source cells have color,merging,comments. Do you want to copy one or more cells with OUT these. Perhaps a sample file. If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Mik" wrote in message ... On 8 May, 16:49, "Don Guillett" wrote: Try this. Select the cell to copyfire the macroselect the paste cellmacro finishes job Sub copytoselectionSAS() Selection.Copy Application.InputBox(Prompt:= _ �"Select your copy cell", Title:="SalesAid Software", Type:=8) �With ActiveCell � � .Interior.ColorIndex = xlNone � � .ClearContents � � .ClearComments � � .UnMerge End With -- Don Guillett Microsoft MVP Excel SalesAid Software "Mik" wrote in message ... Using VBA, I have copied the contents / comments / color etc.. of a particular cell (single or merged), and wish to manually select (using mouse pointer) which cell to paste the data. So, I guess the VBA code should pause / stop until a new cell is chosen and confirmed. Is this posible? My code so far is.... Private Sub CommandButton4_Click() ' MOVE data Dim Answer As String Dim MyNote As String MyNote = "Move selected data?" Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "For Confirmation") If Answer = vbNo Then � �Exit Sub End If Selection.Copy With ActiveCell � �.Interior.ColorIndex = xlNone � �.ClearContents � �.ClearComments � �.UnMerge End With MsgBox ("Please select new cell, where data will be copied to. ") ????????????? ActiveSheet.Paste End Sub Thanks in advance. Mik- Hide quoted text - - Show quoted text - Thank you both for your reply..... The cells that are being copied / moved can sometimes have a border along one of it's edges (left or right, depending on it's location). When i run your code, the borders also copy across to the new location. I don't want this to happen. I have tried utilising the 'PasteSpecial Paste:=xlPasteAllExceptBorders' routine, but i get errors. Possibly entering it wrong. Can you please assist? Thanks Mik |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose which cell to paste data
Your best method would be to remove the borders on the destination cell
after the paste. As Gary's Student had set the destination range to a variable r2, you can use that variable to remove the borders.. Set r2 = Application.InputBox(prompt:="pick destination", Type:=8) r1.Copy r2 r2.Borders.LineStyle = xlNone Even if there are no borders to remove, this will not throw an error. Mike F "Mik" wrote in message ... On 8 May, 16:49, "Don Guillett" wrote: Try this. Select the cell to copyfire the macroselect the paste cellmacro finishes job Sub copytoselectionSAS() Selection.Copy Application.InputBox(Prompt:= _ "Select your copy cell", Title:="SalesAid Software", Type:=8) With ActiveCell .Interior.ColorIndex = xlNone .ClearContents .ClearComments .UnMerge End With -- Don Guillett Microsoft MVP Excel SalesAid Software "Mik" wrote in message ... Using VBA, I have copied the contents / comments / color etc.. of a particular cell (single or merged), and wish to manually select (using mouse pointer) which cell to paste the data. So, I guess the VBA code should pause / stop until a new cell is chosen and confirmed. Is this posible? My code so far is.... Private Sub CommandButton4_Click() ' MOVE data Dim Answer As String Dim MyNote As String MyNote = "Move selected data?" Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "For Confirmation") If Answer = vbNo Then Exit Sub End If Selection.Copy With ActiveCell .Interior.ColorIndex = xlNone .ClearContents .ClearComments .UnMerge End With MsgBox ("Please select new cell, where data will be copied to. ") ????????????? ActiveSheet.Paste End Sub Thanks in advance. Mik- Hide quoted text - - Show quoted text - Thank you both for your reply..... The cells that are being copied / moved can sometimes have a border along one of it's edges (left or right, depending on it's location). When i run your code, the borders also copy across to the new location. I don't want this to happen. I have tried utilising the 'PasteSpecial Paste:=xlPasteAllExceptBorders' routine, but i get errors. Possibly entering it wrong. Can you please assist? Thanks Mik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop down data want to choose more than one item for cell | Excel Worksheet Functions | |||
choose a range to copy paste based on data in the cells | Excel Programming | |||
Choose merged cell and sum data | Excel Worksheet Functions | |||
How do I choose a cell colour, without it being tied to the data? | Excel Discussion (Misc queries) | |||
choose a cell anywhere and have filtered data appear there? Is there a way? | Excel Programming |