VBA Code help

[superfb]

Hi,

I have this code that helps to insert a column in various tables (copies and then paste special the data)

Is there a way to adjust this code to delete the previous column? in stead off inserting the column - bare in mind the last column has formulas, i would like the column before this to be deleted

    Sub Insert_Col()
Dim ws As Worksheet
Dim lr As Long
Dim lc As Long
Dim myrng As Range
Dim myvar As Variant

Application.ScreenUpdating = False
On Error GoTo Oops:
'assuming there are are no sheets that need to be ignored ???
For Each ws In ThisWorkbook.Sheets

Select Case ws.Name

Case "Main Menu", "Notes", "Contents", "Table 8", "Table 10_SOC10", "Table 10_SOC00", "Table 7_SIC03", "Table 11", "Table 13", "Table 14" ' Sheets to be ignored
GoTo Ignore:

Case Else  'Otherwise do insert
    With ws
    
        'last column  by interrogating row 3!!!!!!!!!!
        lc = .Cells(3, Columns.Count).End(xlToLeft).Column
        'last row
        lr = .Cells(Rows.Count, lc).End(xlUp).Row
      
        'range of interest
        Set myrng = .Range(.Cells(1, lc), .Cells(lr, lc))
        'Assign range values to array myvar
        myvar = myrng.Value
        'insert a new column
        myrng.Columns(1).EntireColumn.Insert
        ' range values to new column
        myrng.Offset(0, -1).Value = myvar
        'Copy format to new column
     myrng.Copy
     myrng.Offset(0, -1).PasteSpecial xlPasteFormats
     Application.CutCopyMode = False
     myrng.Offset(0, -1).ColumnWidth = myrng.ColumnWidth
     
    End With

Thanks

 

[igold]

I have this code that helps to insert a column in various tables

Are you really working with Tables or are they ranges of data on different sheets...

 

[superfb]

Are you really working with Tables or are they ranges of data on different sheets...

Sorry not sure what you mean, but if this helps i have almost 15 tabs in a workbook, all different tables that have an array formula linked to various OTHER workbooks - when i open them and press F9 it will do the calculation. As i need a snapshot of that data according to date, i use the above code to copy it in to the column before.

However, at a later date...that data gets revised, due to this i want to delete the column before the formula column (if that make sense) then repeat the process described just now!

I hope that helps

 

[igold]

Ok, look at the attached link and in the section- How Do I Find Existing Tables? Go to Step 4, and see what that code does for you.

Also, this is very good reference in general for working with Tables...

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

 

[superfb]

Ok, look at the attached link and in the section- How Do I Find Existing Tables? Go to Step 4, and see what that code does for you.

Also, this is very good reference in general for working with Tables...

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Thank you - this would be helpful but im not using the table as per the excel, but i have created a table summary manually