VBA Copy Paste Special certain columns of a table?

[Melimob]

Hi all

Wonder if someone can help.

I have a table where some columns are formulas I want to keep and some are formulas I want to overwrite with their values (paste special to remove formulas).

I have code to either leave the table alone (leave all formulas)
or
remove all formulas and leave values but not if I want a combination of both?

Since the rows will be dynamic I need to copy paste special to last row for the columns to overwrite?

Appreciate any help.

Thank you

 

[Yongle]

VBA below assumes that the table is an Excel Table created with Insert \ Table
- set dbR to your data range and the code does the same for a normal range

What VBA does
- dbR is the data area of specified table
- formulas in 4th and 6th columns of the table are overwritten by the values in the cells
- values in 1st column of the table are cleared

Sub ClearColumns()
    Dim dbR As Range
    Set dbR = Sheets("SheetXXX").ListObjects("Table1").DataBodyRange
    
[COLOR=#006400]'FIRST overwrite values[/COLOR]
    dbR.Columns(4) = dbR.Columns(4).Value
    dbR.Columns(6) = dbR.Columns(6).Value
[COLOR=#006400]'NEXT clear contents[/COLOR]
    dbR.Columns(1).ClearContents

End Sub

 

[Melimob]

VBA below assumes that the table is an Excel Table created with Insert \ Table
- set dbR to your data range and the code does the same for a normal range

What VBA does
- dbR is the data area of specified table
- formulas in 4th and 6th columns of the table are overwritten by the values in the cells
- values in 1st column of the table are cleared

Sub ClearColumns()
    Dim dbR As Range
    Set dbR = Sheets("SheetXXX").ListObjects("Table1").DataBodyRange
    
[COLOR=#006400]'FIRST overwrite values[/COLOR]
    dbR.Columns(4) = dbR.Columns(4).Value
    dbR.Columns(6) = dbR.Columns(6).Value
[COLOR=#006400]'NEXT clear contents[/COLOR]
    dbR.Columns(1).ClearContents

End Sub

thank you so much Yongle.. I've test this however it's not overwriting my values and it's clearing my formulas

here's my code:

 With HRGC
    .Range("B1:C1").Copy
    .Range("B1:C1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    
    Dim dbR As Range
    Set dbR = Sheets("HRG Clients ").ListObjects("Table6").DataBodyRange
    
'FIRST overwrite values
    'I've based column no. on: column 1 = a, column 2 = b? .  my table data range is from b-t

        dbR.Columns(2) = dbR.Columns(2).Value


    dbR.Columns(3) = dbR.Columns(3).Value
    'dbR.Columns(4) = dbR.Columns(4).Value
    dbR.Columns(7) = dbR.Columns(7).Value
    dbR.Columns(8) = dbR.Columns(8).Value
    dbR.Columns(10) = dbR.Columns(10).Value
    dbR.Columns(11) = dbR.Columns(11).Value
    dbR.Columns(12) = dbR.Columns(12).Value
    dbR.Columns(13) = dbR.Columns(13).Value
    dbR.Columns(14) = dbR.Columns(14).Value
    dbR.Columns(15) = dbR.Columns(15).Value
    
'NEXT clear contents
'I want to keep formulas for these columns.  I tried SpecialCells(xlCellTypeConstants, 23) but got an error?


    'dbR.Columns(2).SpecialCells(xlCellTypeConstants, 23).ClearContents
    'dbR.Columns(4).ClearContents

    dbR.Columns(16).ClearContents
    dbR.Columns(17).ClearContents
    dbR.Columns(18).ClearContents
    dbR.Columns(19).ClearContents
        
    End With

many thanks

 

[Yongle]

I wrote - formulas in 4th and 6th columns of the table are overwritten by the values in the cells

and the bit of the code that does that is

    dbR.Columns([COLOR=#ff0000]4[/COLOR]) = dbR.Columns(4).Value
    dbR.Columns([COLOR=#ff0000]6[/COLOR]) = dbR.Columns(6).Value

(Apologies if not clear in previous post) Column 4 is NOT column D - it is the 4th column of the table
If your table starts in column B then column C is dbR.Columns(2) etc

 

[Melimob]

I wrote - formulas in 4th and 6th columns of the table are overwritten by the values in the cells

and the bit of the code that does that is

    dbR.Columns([COLOR=#ff0000]4[/COLOR]) = dbR.Columns(4).Value
    dbR.Columns([COLOR=#ff0000]6[/COLOR]) = dbR.Columns(6).Value

(Apologies if not clear in previous post) Column 4 is NOT column D - it is the 4th column of the table
If your table starts in column B then column C is dbR.Columns(2) etc

it's a thing of beauty!!
THANK YOU SO MUCH!!

Worked like a charm.

Don't know what I would do without you guys.

Love it

Thanks again!