CODEWITHSUNDEEP
vbaexcel
Sploits
Sploits

Reputation: 1

Pasting into last column of table

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).

Upvotes: 0

Views: 497

Answers (2)

Sploits
Sploits

Reputation: 1

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.

Upvotes: 0

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

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.

Upvotes: 1

Related Questions