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

 

[Joe4]

You should get most of what you need with the Macro Recorder, but bear in mind that the Macro Recorder is very literal, and sometimes you need to edit some of the code it creates to make it more dynamic.

Why don't you post the code you came up with, and let us know which line is causing the error, and we will see if we can help you clean it up?

 

[momentumons]

OK! Thank you @Joe4

Here's the code from the recording:

ub EXTERNAL_COPY()
'
' EXTERNAL_COPY Macro
'

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

When I run the Macro it creates the copy of the tab but the data doesn't appear in it (some strange formatting does). The debugger says the first error is happening on this line:
ActiveSheet.Shapes.Range(Array("Rectangle 4")).Select

Can you help!? Thanks

 

[alansidman]

An alternative is to import the file to Power Query. Delete the columns you wish and rename the columns in question. Close and Load to Excel.

 

[momentumons]

Thanks @alansidman ! I have only just begun to use (and love) Power Query! However, I don't think it will work for this one because:
- I need to copy the entire sheet (which include image files and lots of intro text fields at the top)
- Even the main data 'table' isn't "formatted as a table" because although the column headings are always the same, there may be many subheadings that are manually included/formatted.
- Some columns may be removed or added for each copy
- At the bottom there's a little summary which has a bunch of formulas - this is also not a part of the main data table and might be in a different spot each time

But If you think PowerQuery would still work for this please let me know how I can use it? I have only used it for big data tables before... Thanks

 

[Joe4]

When I run the Macro it creates the copy of the tab but the data doesn't appear in it (some strange formatting does). The debugger says the first error is happening on this line:
ActiveSheet.Shapes.Range(Array("Rectangle 4")).Select

If you get an error on that line, that seems to suggest that you do not have a shape named "Rectangle 4" on that sheet.
Can you confirm that? If you do have a shape on that sheet, check its name.

 

[momentumons]

Thank you @Joe4 . I can confirm that I do have a shape called "rectangle 4" on the original sheet.

However, I am guessing that when I create a copy as part of the macro recording - perhaps the name of the shape automatically changes so it's not a duplicated name from the original sheet? As the macro includes making this copy every time - how can I work around this?

 

[Joe4]

Here is a link to some code that shows you how to loop through all the shapes on the sheet:

VBA Loop through Shapes - EXCELCISE

VBA code how to loop through shapes on a worksheet and / or all sheets of a workbook using For Each loop.

www.excelcise.org

 

[momentumons]

Thanks! That looked complicated... but I seemed to solve the shape issue by doing "find and select objects" and deleting them that way. Not sure why but that is now no longer a problem. Thanks for your guidance on this.

However, now it is creating the new copy...but the new version is only including one column from my entire sheet? The last column in the print area - the last column with data (AI) - becomes column A in the copy and this is the only column in the copy...the rest of the sheet is blank. It has copied over two shapes that are now squished into this one row. But NONE of the actual excel data has been copied/moved?

Here's the new code:

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("A28:P38").Select
    ActiveSheet.Shapes.Range(Array("Rectangle 3", "Rectangle 4")).Select
    Selection.Delete
    Range("B2:Q3").Select
    Selection.ClearContents
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A28:P38").Select
    Columns("B:H").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Rows("32:32").Select
    Range("M32").Activate
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.Save
    Sheets("EXTERNAL COPY").Select
    Sheets("EXTERNAL COPY").Move
End Sub

 

[Joe4]

This sort of thing is very difficult to troubleshoot without having access to your data file.

However, you may be able to debug this yourself.
If you have two monitors, put the worksheet on one and the VB Editor on the other (or if you just have on, resize your VBA code so it is about 1/4 the size of the screen.
Now, place the cursor over the top line of your code (the "Sub EXTERNAL_COPY()" line), and start pressing F8 to go though your code one line at a time.
The goal is to watch what happens on the sheet as you go through code, line-by-line.
Many times, when you see what is happening, the problem area of the code becomes very apparent, and you can focus on fixing it.