add highest number from different sheet column to sheet

[nzt101]

Hey guys,

so i have a workbook, with information pulled from 2 other files that i refresh these files daily, then Refresh all to update the main workbook with new information.

In the main book,
how would i add to Sheet1, column F, with the highest number from Sheet2, Column R
i'd like to add to the end of column F rather than replacing what was already there.
Ideally i'd put dates that the highest number was loaded into Column F into Column G beside it

Then i'd like to highlight the rows in Sheet1 that are higher than the previous refresh number in Column F
-- ive already done a bit of mucking around with the conditional formatting so could probably work it out, im more chasing the answer to the first section, but this would be a bonus

thanks in advance!

 

[Trebor76]

Hi nzt101,

Not sure what you're after in the second part but this should do the trick for the first section:

Option Explicit
Sub Macro1()

    Dim lngResult As Long, lngPasteRow As Long
    
    Application.ScreenUpdating = False
    
    lngResult = Application.WorksheetFunction.Max(Sheets("Sheet2").Range("R:R"))
    
    With Sheets("Sheet1")
        lngPasteRow = .Cells(Rows.Count, "F").End(xlUp).Row + 1
        .Range("F" & lngPasteRow).Value = lngResult
        .Range("G" & lngPasteRow).Value = Format(Now(), "dd-mmm-yyyy")
    End With
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[nzt101]

Awesome thank you Robert that worked well.
But just to check, do i need to run the VBA after each data refresh or is there a way of automating that?

And for the 2nd part, i assume that i'd use conditional formatting for this?
I'd be looking to highlight the rows in Sheet1 that have numbers above the previous high number in Sheet2 Column F
Say if the new high number was 10 and the previous high number was 7, i'm looking at highlighting the rows with anything above 7

 

[nzt101]

sorry i can't see how to Edit my last post, but for the conditional formatting
it would look for numbers above the previous high number that are in Column R then highlight those rows

 

[Trebor76]

do i need to run the VBA after each data refresh or is there a way of automating that?

Yes you would need to run the VBA after the refresh for the maximum number to be copied from Sheet2 to Sheet1. You would need to have code to do the refresh followed by my code if you want to automate the entire process. You could record the how you currently do the refresh to get the code that way (refresh can mean different things i.e. refresh a pivot table or data connection etc).

 

[nzt101]

Hi nzt101,

Not sure what you're after in the second part but this should do the trick for the first section:

Option Explicit
Sub Macro1()

    Dim lngResult As Long, lngPasteRow As Long
   
    Application.ScreenUpdating = False
   
    lngResult = Application.WorksheetFunction.Max(Sheets("Sheet2").Range("R:R"))
   
    With Sheets("Sheet1")
        lngPasteRow = .Cells(Rows.Count, "F").End(xlUp).Row + 1
        .Range("F" & lngPasteRow).Value = lngResult
        .Range("G" & lngPasteRow).Value = Format(Now(), "dd-mmm-yyyy")
    End With
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

Hi Robert,

thanks again for your help on this one, just wondering, would there also be a way of adding another function to this macro?
i want to write the SUM of P:P of Sheet2 to Sheet1 row H so it updates and fills the bottom cell of F G & H at the same time

thank you

 

[Trebor76]

thanks again for your help on this one, just wondering, would there also be a way of adding another function to this macro?
i want to write the SUM of P:P of Sheet2 to Sheet1 row H so it updates and fills the bottom cell of F G & H at the same time

Try this:

Option Explicit
Sub Macro1()

    Dim lngResult As Long, lngPasteRow As Long
    Dim dblSUM As Double
    
    Application.ScreenUpdating = False
    
    lngResult = Application.WorksheetFunction.Max(Sheets("Sheet2").Range("R:R"))
    dblSUM = Application.WorksheetFunction.Sum(Sheets("Sheet2").Range("P:P"))
    
    With Sheets("Sheet1")
        lngPasteRow = .Cells(Rows.Count, "F").End(xlUp).Row + 1
        .Range("F" & lngPasteRow).Value = lngResult
        .Range("G" & lngPasteRow).Value = Format(Now(), "dd-mmm-yyyy")
        .Range("H" & lngPasteRow).Value = dblSUM
    End With
    
    Application.ScreenUpdating = True

End Sub

 

[nzt101]

spot on! perfect, thanks again!

 

[Trebor76]

You're welcome