Copy paste values by inserting a column vba

[superfb]

Hi all,

I have about 10 tabs in a workbook.

Each tab has tables of information.
Where in one column has formulas looking into other workbooks.

Is there a way in VBA that could do the following:

1) Copy the formula, and insert a column before it and paste values.
2) The columns with the formulas, is in diff columns in each tab. Can the Macro find the formula and then paste this in the previous column via inserting the column.

I hope that was clear?

Thank you in advance

 

[sijpie]

If you can do it by hand, you can do it using VBA.
Your code needs to run through each column on say row 2. It checks if the cell has a formula. If so then do the insert and copy /paste the values. This can be done even more efficient by not using copy/pastespecial, but by setting the values of the column to the value of the column with the formula. Lightning fast.

Option Explicit

Sub InsertValueColumn(shtSheet As Worksheet)
    Dim rC As Range
    Dim lCol As Long, lL As Long, lR As Long
    Dim sForm As String
    
    'assuming the data columns start in A, and the data (not the headings) start in row 2 then
    
    lCol = Range("A2").CurrentRegion.Columns.Count  'count the maximum number of columns to process
    lCol = Range("A2").CurrentRegion.Rows.Count  'count the  number of rows (to copy)
    
    For lL = 1 To lCol
        'go through each of the columns to look for the one with formulas
        sForm = Left(Cells(2, lL).Formula, 1)
        If sForm Like "=" Or sForm Like "+" Or sForm Like "-" Then  'is a formula
            Range(Cells(2, lL)).EntireColumn.Insert 'insert a column before
            'now set the value ov the cells in the new column to the value of the cells in the formula column
            Range(Cells(1, lL), Cells(lR, lL)).Value = Range(Cells(1, lL + 1), Cells(lR, lL + 1)).Value
            Exit For    'end loop
        End If
    Next lL
End Sub

Sub LoopThruSheets()
    'run this macro to go through all sheets and copy the formula column
    Dim wksWS As Worksheet
    
    For Each wksWS In ThisWorkbook.Worksheets
        InsertValueColumn wksWS
    Next wksWS
End Sub

Read thru the comments to see how it works

 

[superfb]

If you can do it by hand, you can do it using VBA.
Your code needs to run through each column on say row 2. It checks if the cell has a formula. If so then do the insert and copy /paste the values. This can be done even more efficient by not using copy/pastespecial, but by setting the values of the column to the value of the column with the formula. Lightning fast.

Option Explicit

Sub InsertValueColumn(shtSheet As Worksheet)
    Dim rC As Range
    Dim lCol As Long, lL As Long, lR As Long
    Dim sForm As String
   
    'assuming the data columns start in A, and the data (not the headings) start in row 2 then
   
    lCol = Range("A2").CurrentRegion.Columns.Count  'count the maximum number of columns to process
    lCol = Range("A2").CurrentRegion.Rows.Count  'count the  number of rows (to copy)
   
    For lL = 1 To lCol
        'go through each of the columns to look for the one with formulas
        sForm = Left(Cells(2, lL).Formula, 1)
        If sForm Like "=" Or sForm Like "+" Or sForm Like "-" Then  'is a formula
            Range(Cells(2, lL)).EntireColumn.Insert 'insert a column before
            'now set the value ov the cells in the new column to the value of the cells in the formula column
            Range(Cells(1, lL), Cells(lR, lL)).Value = Range(Cells(1, lL + 1), Cells(lR, lL + 1)).Value
            Exit For    'end loop
        End If
    Next lL
End Sub

Sub LoopThruSheets()
    'run this macro to go through all sheets and copy the formula column
    Dim wksWS As Worksheet
   
    For Each wksWS In ThisWorkbook.Worksheets
        InsertValueColumn wksWS
    Next wksWS
End Sub

Read thru the comments to see how it works

Thank you very much for taking the time out to write this for me - genuinely appreciate this!

i put the above code into VBA and when i create a button to assign the macro the only macro i can see is LoopThruSheets()

Another point, is i have a main menu where i tend do have formulas (basically use it as a user form) so ideally i want this tab excluded, and few others that i manually update - so is it worth the code having a list of tabs to this in? (tabs are called Table ,1 Table 2, Table 3, Table 4, Table 5, Table 6, Table 7, and so on up to Table 15.

 

[sijpie]

Yes, you are supposed to only see the loopthrusheets macro. Itt will call the other one.

To include only the sheets with names starting with table, modify the loopthrusheets macro as follows

For Each wksWS In ThisWorkbook.Worksheets
        If wksWS.Name like "Table*" then InsertValueColumn wksWS
    Next wksWS