0

I am trying to copy a range of cells and pasting them in the same worksheet but a few columns away. I am getting an application defined error. What gives? I followed this example here: 

    MsgBox "Would you like to save the chart data for the completed sprint?", vbOKCancel
    If answer = 0 Then
        MsgBox "Copying ..."

        Worksheets("KPIs").Range("AN:AY").Copy
        Worksheets("KPIs").Range("AN:AY").Offset(3).Insert Shift:=xlRight
        Application.CutCopyMode = False

    Else
        End
    End If

UPDATE

After modifying offset(3) to offset(0,3), I have encountered a new error that's not completely display (what the heck Excel?)

enter image description here

Jrules80
  • 178
  • 12
  • You could have looked it up in Help or googled. `Offset` is rows, columns so you need `offset(0,3)` or if you're lazy like me `offset(,3)`. – SJR Jan 16 '20 at 21:29
  • `xlShiftToRight` as well... but I think there's still a problem. – BigBen Jan 16 '20 at 21:30
  • Yeap, there is still a problem, yessir. – Jrules80 Jan 16 '20 at 21:33
  • Where do you want the columns to be inserted - after `AP`? – BigBen Jan 16 '20 at 21:34
  • @SJR - thank you for your reply. Excel just spat an ugly message at my face. See my question for screen-grabs. – Jrules80 Jan 16 '20 at 21:34
  • Try doing this manually. Do you get an error? I'm not sure where exactly you're trying to insert the columns. – BigBen Jan 16 '20 at 21:35
  • I am able to cut/paste them manually just fine. I need to make space for new set of data. – Jrules80 Jan 16 '20 at 21:38
  • 1
    Cut/paste is not the same as *insert copied cells*. The macro recorder is helpful here. – BigBen Jan 16 '20 at 21:38
  • Never used a macro recorder. Sorry! VBA programming is completely new to me.I have always used Excel for trivial uses before now :( – Jrules80 Jan 16 '20 at 21:40
  • 2
    AN to AY are more than three columns so you would be inserting this into the middle of the range you are copying. – Scott Craner Jan 16 '20 at 21:40
  • 1
    *Developer* tab > *Record Macro* :-) – BigBen Jan 16 '20 at 21:41
  • If you want it 3 columns from where the range ends then you need to use `Worksheets("KPIs").Range("AY:AY").Offset(,3).Insert Shift:=xlRight` – Scott Craner Jan 16 '20 at 21:41
  • @ScottCraner - Did you mean to say Range ("AN:AY")? – Jrules80 Jan 16 '20 at 21:43
  • @BigBen - So, I clicked on Record Macro option and it said it will save it in "This Workbook". I cut and pasted the range and stopped recording. I went into "This Workbook" to look for code but in vain. – Jrules80 Jan 16 '20 at 21:44
  • @Jrules80 no I meant exactly what I typed. – Scott Craner Jan 16 '20 at 21:45
  • Haha, yessir. You did. Let me check it out. – Jrules80 Jan 16 '20 at 21:46
  • 1
    Now if you want the Range to be variable then we can do something like: `Worksheets("KPIs").Range("AN:AY").Offset(,3 + Worksheets("KPIs").Range("AN:AY").columns.Count).Insert Shift:=xlRight` then you can just use the size of the range and add 3 columns. – Scott Craner Jan 16 '20 at 21:49
  • Thank you, thank you! That worked. Cannot thank this forum enough and to all you guys that pitch in to help.You guys have no idea how grateful I am :) Keep it up, y'all. – Jrules80 Jan 16 '20 at 21:59

0 Answers0