Analyse a table that changes every week

[KLolli]

Hello,

I am trying to find the most efficient way to analyse a table in an excel file that I receive, updated, every week. I have 6 formulas (in 6 columns) which I am using to analyse the data and that I use to create pivot tables.
I would like to find a solution to avoid cut and paste as much as I can, and add some automated steps so that even if the row number increases in the table I receive every week, "my columns" are updated accordingly.
Does anyone have any suggestions?

Many thanks in advance for your your help.

 

[XLfriend777]

Hi KLolli - Welcome to the forum. When doing this type of activity I often find it useful to find the last row and column used. The following code will help do that. Hope this helps get you started.

Sub Range_End_Method()
'Finds the last non-blank cell in a single row or column

Dim lRow As Long
Dim lCol As Long
    
    'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    MsgBox "Last Row: " & lRow & vbNewLine & _
            "Last Column: " & lCol
 
End Sub

 

[KLolli]

Thanks for your reply goesr.
I think it's better if I do not adventure into VBA coding!!
Also I receive the data in a table, so it is already organised and ready for doing sorting or pivots. What I need to do is basically add the extra columns with my formulas and some conditional formatting.
The problem is that I am still doing cut&past when it comes to adding those. I need a less manual proces and there are so many videos and tutorial on merging tables, queries, etcetera that I don't know which would work for me.

Thanks,


K.