Forum Discussion

Tim Beechey-Newman's avatar
Tim Beechey-Newman
Copper Contributor
Oct 25, 2017
Solved

Macro does not run. Pop up box appears headed 'Create Table'

I have built a model in Excel 2007 and included 3 cut ‘n paste macros (recorded – not written). I have sent it to my friend (I don’t know what version of Excel he has). When he runs (at least one of)...
Macros & VBA
Need Help
  • JKPieterse's avatar
    JKPieterse
    Oct 27, 2017
    For some reason the keyboard shortcut you assigned to your macro does not work for your friend. Thus, Excel reponds to the built-in shortcut control+L, which is to convert a range to a table (Home tab, format as table). I assume you copied your macro as text for your friend. This does not copy the keyboard short-cut with it, you have to set that up yourself. At your friends machine, open Excel and press alt+F8. Find your macro in that list and press Options. Now you can set-up the short-cut key.
JKPieterse's avatar
JKPieterse
Silver Contributor
Oct 26, 2017
Can you post your macro please?
Tim Beechey-Newman's avatar
Tim Beechey-Newman
Copper Contributor
Oct 26, 2017

Jan

Thank you very much for your reply. Here is the code: (it repeats itself).

Tim

 

Keyboard Shortcut: Ctrl+Shift+L
'
    Sheets("Workings").Select
    Range("Q133").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("Q11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("R155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("R11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S11").Select
    Sheets("Workings").Select
    Range("S155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("T155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("T11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("U155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("U11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("V155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("V11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("W155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("W11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("X155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("X11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=3
    Sheets("Workings").Select
    ActiveWindow.SmallScroll ToRight:=6
    Range("Y155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("Y11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("Z155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("Z11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AA155").Select
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AA11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AB155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AB11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AC155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AC11").Select
    ActiveWindow.SmallScroll ToRight:=6
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AD155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AD11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AE155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AE11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AF155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AF11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AG155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AG11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AH155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AH11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AI155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AI11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AJ155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AJ11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AK155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AK11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AL155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AL11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AM155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AM11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Workings").Select
    Range("AN155").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Scenarios").Select
    Range("AN11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Fin Stats Ann").Select
    Range("F148").Select
    Sheets("Workings").Select
    Range("A98").Select
End Sub

 

 

 

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 27, 2017
    For some reason the keyboard shortcut you assigned to your macro does not work for your friend. Thus, Excel reponds to the built-in shortcut control+L, which is to convert a range to a table (Home tab, format as table). I assume you copied your macro as text for your friend. This does not copy the keyboard short-cut with it, you have to set that up yourself. At your friends machine, open Excel and press alt+F8. Find your macro in that list and press Options. Now you can set-up the short-cut key.
    • Tim Beechey-Newman's avatar
      Tim Beechey-Newman
      Copper Contributor
      Oct 27, 2017

      Jan

      Thank you so much for your reply. I / we have not actioned it yet, but it seems to make complete sense, so I imagine it will work.

      Best wishes

      Tim

Resources