Cut and Paste Issues

[Mschwarz31]

Hi guys,

I'm having trouble with a cut and paste macro. I used the macro recorder to cut a line from a worksheet and paste it onto another worksheet in the same workbook. I then have the macro attached to a checkbox. the problem is that when I run the macro it pastes the data onto the second worksheet at the top where I have a header, rather than in the range I designated when I recorded the macro. I've made sure to size and format the second worksheet so that it's identical to the first one. Here's the code ;



Sub Move_Data2()
'
' Move_Data2 Macro
' Cut and paste data from Sheet "Open to corresponding cells in sheet "Closed""&chr(10)&"'"&chr(10)&"' Keyboard Shortcut: Ctrl+Shift+M"&chr(10)&"'
Range("A6:K6").Select
Range("K6").Activate
Selection.Copy
Sheets("Closed").Select
Range("A6:K6").Select
Range("K6").Activate
Range("A6:K6").Select
Range("K6").Activate
ActiveSheet.CheckBoxes.Add(1078.5, 107.25, 29.25, 16.5).Select
ActiveSheet.Paste
Sheets("Open").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Selection.ClearContents
End Sub


What am I doing wrong? Thanks for any advice

 

[My Aswer Is This]

It's always best to know what sheet the script starts on.
At the beginning of your script.
Put something like this Sheets("Me").Activate
This way when testing I know what sheet we are starting on.

And you want to paste you check where exactly. Do not say active sheet.

Say something like this Sheets("Copy") Range("A2")

It's always better to not use active cell

And if your going to run this script more then once where do you want it pasted next time.

Your script is showing screen locations not cell locations.

 

[Mschwarz31]

Ok, that's a revelation. How do you know the difference between screen locations and cell locations?

 

[My Aswer Is This]

Since you use terms like active sheet it's hard for me to really see what your trying to do.

But look at my script and see if you can see how it is different.
It gets the top screen location of a cell and the left screen location of a cell.

In your script you do not specify what sheet your on when you start the script.

And your doing two different things

Try this and see if you can see how it works.

If this does not do what you want then explain in more detail what help you need.

Sub Move_Data2()
'
' Move_Data2 Macro
' Cut and paste data from Sheet "Open to corresponding cells in sheet "Closed""&chr(10)&"'"&chr(10)&"' Keyboard Shortcut: Ctrl+Shift+M"&chr(10)&"'
Dim Left As Long
Dim Top As Long
Left = Range("B6").Left
Top = Range("B6").Top
Range("A6:K6").Select
Range("K6").Activate
Selection.Copy
Sheets("Closed").Select
Range("A6:K6").Select
Range("K6").Activate
Range("A6:K6").Select
Range("K6").Activate
ActiveSheet.CheckBoxes.Add(Left, Top, 29.25, 16.5).Select
ActiveSheet.Paste
Sheets("Open").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Selection.ClearContents
End Sub