Macro to create a copy of my worksheet - in a new document as values (no formulas) and with a few changes (row deleted and a few other things deleted)

[momentumons]

Hello! I have a document that has some internal-only info but most of it is what we need to send to external parties. I'd love some help creating a macro for my internal doc that will create an "external copy" that:

- is 'paste as values' (ie same formatting but no formulas)
- has a few columns deleted
- headline changed
- macro buttons deleted


The macro needs to work no mater what content is in the sheet - the template will be used every week and will have different number of row and different formatting in those rows. Columns won't change.

It needs to be robust as it ill be used by several different team members.

Thanks!

FYI - I tired to use the "record macro function" to 1. create a copy of the tab; 2. copy & paste as values; 3. delete the required rows/buttons and make the headline change; 4. Move to a new workbook..... but it did not work. It creates the copy but get "runtime error 1004: the item with the specified name wasn't found". In the debugger it looks like a problem with deleting the maco buttons...but this is where I get lost and seek your wise help! I am guessing that is the first of a few problems I will run into on this one

 

[momentumons]

Thanks @Joe4 that's a super helpful tip! I have gone through and I can see what's happening - where I am 'selecting B:H columns and deleting them (which is what i recorded and looks ok in the code???) - what is really happening is columns A:AH are being selected and deleted instead. I can't work out why or how to fix this. Other than that it is working well! Can you help?

Sub EXTERNAL_COPY()
'
' EXTERNAL_COPY Macro
'

'
    Sheets("MP").Select
    Sheets("MP").Copy Before:=Sheets(1)
    Sheets("MP (2)").Select
    Sheets("MP (2)").Name = "EXTERNAL_COPY"
    Range("A41").Select
    ActiveSheet.Shapes.Range(Array("Rectangle 3", "Rectangle 4")).Select
    Selection.Delete
    Columns("B:H").Select
    Selection.Columns.Ungroup
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("B2:Q3").Select
    Selection.ClearContents
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Columns("B:H").Select
    Selection.Columns.Delete
    Range("A28:I37").Select
    Sheets("EXTERNAL_COPY").Select
    Sheets("EXTERNAL_COPY").Move
End Sub

 

[momentumons]

Thanks @Joe4 - that is a great tip. I worked out where the problem was and then was able to google some replacement code. It's working perfectly now! Thanks for your help.

FYI for anyone who comes across this:

The Record Macro button write this code (which somehow deleted the entire thing):

Columns("B:H").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

I replaced it with this:

 Columns("B:H").EntireColumn.Delete