Choose which cell to paste data
in [Programming]
|
From: Mike Fogleman on 9 May 2010 09:19 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" <mholmes(a)safetysystemsuk.com> wrote in message news:ed49e056-eb8a-4486-b4f9-ca1be96538c5(a)g21g2000yqk.googlegroups.com... > On 8 May, 16:49, "Don Guillett" <dguille...(a)gmail.com> wrote: >> Try this. Select the cell to copy>fire the macro>select the paste >> cell>macro >> 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 >> dguill...(a)gmail.com"Mik" <mhol...(a)safetysystemsuk.com> wrote in message >> >> news:3d9a3e96-cf5e-4294-bbc5-cc4bead9decd(a)s29g2000yqd.googlegroups.com... >> >> >> >> > 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 |