0

I've been creating a VBA code to help me with a worksheet I use but I'm stuck at a certain point.

The code looks at the table on the current worksheet, adds a new column to the end of the table and then I get it to copy the first column in the worksheet (as this has the formats and some calculated cells). This is where my coding finishes. Ideally I would then like it to take the copied cells and paste them into the new end column of the table.

This is what I have so far:

    Sub AddNewColumn()
  Application.ScreenUpdating = False
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    With oSh.ListObjects("Labour")
.ListColumns.Add
Range("Labour[[#All],[Column16]]").Select
    Selection.Copy

End With
  Application.ScreenUpdating = True
End Sub

(Labour being the name of the current table). If I can get this to work fantastic but then I think I will encounter another issue. The table is on a template worksheet and contained on this I have a command button to create a copy of the template (for different tasks). This would then change the name of the table (Labour1 then Labour2 etc as new worksheets are created). How would I get the code to work on new worksheets as the code I have at the minute would simply want to link back to the original table (Labour).

braX
  • 11,506
  • 5
  • 20
  • 33
Sploits
  • 1

2 Answers2

1

You don't need actually copy values from the first column to the newly created, just use formula. I have modified your code:

Sub AddNewColumn()
  Application.ScreenUpdating = False
    Dim oSh As Worksheet
    Dim oList As ListObject
    Dim str As String

    Set oSh = ActiveSheet
    Set oList = oSh.ListObjects("Labour")

    With oList
            .ListColumns.Add
            str = .ListColumns(1).Name
            .ListColumns(.ListColumns.Count).DataBodyRange.FormulaR1C1 = "=[@[" & str & "]]"
    End With
End Sub

If you need actual values, not formulas, you may copy and paste special the last column. Before end with add:

With .ListColumns(.ListColumns.Count).DataBodyRange
    .Copy
    .PasteSpecial xlPasteValues
End With

This is answer to your first question. Unfortunately, I am not able to understand the second. Besides, I think you should ask it separately.

MarcinSzaleniec
  • 2,246
  • 1
  • 7
  • 22
  • The code you've supplied does insert the column to the end but it populates the column with a reference to column 1 (it doesn't actually give me any calculated fields). It has helped me out so I will try and add to it to hopefully get it to do what I want. Many thanks for your help – Sploits Jan 22 '18 at 07:36
0

OK I have tweaked your code @MarcinSzaleniec and it appears to be working.

    Sub AddNewColumn()
    Application.ScreenUpdating = False
    Dim oSh As Worksheet
    Dim oList As ListObject
    Dim str As String

    Set oSh = ActiveSheet
    Set oList = oSh.ListObjects("Labour")

    With oList
            .ListColumns.Add
            str = .ListColumns(1).Name
            Range("Labour[[#All],[Column16]]").Select
            Selection.Copy
            .ListColumns(.ListColumns.Count).DataBodyRange.PasteSpecial xlPasteAll
    Application.ScreenUpdating = True
    End With
    End Sub

The reason I need:

   Range("Labour[[#All],[Column16]]").Select
   Selection.Copy

Is due to it being a column hidden out the way and has the blank bits blank and the formula bits as formulas.

Many thanks for everybody's help. Now to ask the second part of my question on here.

Sploits
  • 1