convert last 2 columns to values & add formulas to next column

[toshimarise]

Hello!

Although I can figure out some of the individual steps, I am having a hard time getting the various parts of this macro to work together. I appreciate any help you can give!

There are roughly 500 rows with data and around 50 columns, but the rows could change up or down and 2 columns get added every day.

This is my basic outline of what needs to happen:

• Unfilter the sheet
• Convert the last 2 columns with data to values (manually this would be copy, then paste as > values in the same spot)
• Fill the first empty column with Formula 1 down to the last row containing data in column A
• Fill the next empty column with Formula 2 down to the last row containing data in column A
• Fill in headers for the new columns
  • Mark the 2^nd to last column header with “[yesterday’s date] EOD”
  • Mark the last column header with “[today’s date]”

Let me know if you need more information. Thank you in advance.

 

[Gavin T]

Try this:

Sub Macro1()

    Dim LastCol As Long
    Dim LastRow As Long
    
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
      
[COLOR="#008000"]      ' Remove filter[/COLOR]
        .UsedRange.AutoFilter
        
[COLOR="#008000"]      ' Convert to values[/COLOR]
        With .Range(Cells(1, LastCol - 1), Cells(LastRow, LastCol))
            .Value = .Value
        End With
         
[COLOR="#008000"]      ' 1st column with formula 1[/COLOR]
        .Range(Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 = "=""Formula1"""
        
[COLOR="#008000"]      ' 2nd column with formula 2[/COLOR]
        .Range(Cells(2, LastCol + 2), Cells(LastRow, LastCol + 2)).FormulaR1C1 = "=""Formula2"""
        
[COLOR="#008000"]      ' Add headers[/COLOR]
        .Cells(1, LastCol + 1) = Date - 1 & " EOD"
        .Cells(1, LastCol + 2) = Date
    End With
    
End Sub

 

[Chris Macro]

This is how I would approach coding your automation macro:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Automate()<br><br><SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> DataRange <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> ColCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> sht = ActiveSheet<br><br><SPAN style="color:#007F00">'Unfilter Sheet</SPAN><br>  sht.AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#007F00">'Assumes all data in spreadsheet makes up data set</SPAN><br>  <SPAN style="color:#00007F">Set</SPAN> DataRange = sht.UsedRange<br>  ColCount = DataRange.Columns.Count<br><br><SPAN style="color:#007F00">'Hardcode last 2 columns</SPAN><br>  DataRange.Columns(ColCount) = DataRange.Columns(ColCount).Value<br>  DataRange.Columns(ColCount - 1) = DataRange.Columns(ColCount - 1).Value<br><br><SPAN style="color:#007F00">'Add 2 formula-filled columns to end (not sure what formula you want)</SPAN><br>  DataRange.Columns(ColCount).Offset(0, 1).FormulaR1C1 = "=RC[-3]"<br>  DataRange.Columns(ColCount).Offset(0, 2).FormulaR1C1 = "=RC[-4]"<br>  <br><SPAN style="color:#007F00">'Add Headers for new Columns</SPAN><br>  DataRange.Cells(1, ColCount + 1).Value = Format(Now - 1, "mm/dd/yyyy") & " EOD"<br>  DataRange.Cells(1, ColCount + 2).Value = Format(Now, "mm/dd/yyyy")<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[toshimarise]

Thank you both very much! I was able to use a minor variation on Gavin's code to do exactly what I needed.

 

[Gavin T]

You're welcome.