VBA issue on creating a table from a copy selection

[shophoney]

Hi, I have a pivot table that I'm copying to a new tab to work with as values.

The code below is to copy the entire table and paste special to a new tab after the sheet "Inventory Movement". Then paste special a few time to keep the format. Then create a table from the cells and add 2 columns to the table.

But I get a VBA error "RUN TIME ERROR 1004"

Sub InventoryMovementCopyToNewSheetForReview2()
Dim ws As Worksheet
Dim rng As Range
'
' Copy Inventory Movement tab and create a new worksheet as values only for design and review
'
Range("A4:Z81").Select
Range("A5").Activate
Selection.Copy

Set ws = Worksheets.Add(After:=Sheets("Inventory Movement"))
ws.Name = "Markdown Review " & Format(Date, "mmm_dd")

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$Z$78"), , xlYes).Name = _
"Table11"
Range("Table11[#All]").Select
ActiveSheet.ListObjects("Table11").TableStyle = "TableStyleMedium2"
ActiveWindow.DisplayGridlines = False
Range("AA1").Select
ActiveCell.FormulaR1C1 = "PROPOSSED DISCOUNT"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "COMMENTS"
Columns("AB:AB").Select
Selection.ColumnWidth = 20
End Sub

Also when I review the code above will I have an issue with the table name as I plan on running the VBA all the time to create an additional table for review and keeping the old tables/sheets.

What if my table ranges get bigger will that also be an issue.

The table name in the code above is "Table11", but how can i rerun it as the name would already be used once.

And what if my " ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$Z$78"), , xlYes).Name = _" is longer than line 78?

Thanks

 

[Yard]

Hi, it's been a while since I was here, but let me try to help.

One key thing here is the use of variables - try to use them whenever you are playing around with objects and ranges.

1. You don't say what line causes the Run error?
2. For the table name, you could use the same date stamping you are using for the additional worksheet (but without any spaces)
3. The range you want to convert to a table is the same size as the one you copied, right? So you know how big it is.

Also, I would consider the following , IMHO:

• There is rarely any need to .Select or .Activate things from VBA. It just slows everything down.
• I would avoid using Selection.Paste - much better practice to specify a Worksheet.Range (or .Cells) where you want it pasting
• Hardcoding "A4:Z81" into the code is, IMHO, risky. Consider creating a named range in the spreadsheet (preferably dynamic) and referring to that name in the code.
• Same principle for hardcoding "AA1" - risky if the width of the range changes in future. You know the size of the range, you know the place where you pasted it, so you know which cell is to the right of it now

Hope that helps
Yard