Find lowest value in a common column between two worksheets and then name the worksheets

**floydian** · 07-13-2013, 10:42 AM

I have two worksheets on one workbook currently listed as Sheet1 & Sheet2

I have the same column (which is T) on each sheet which is a date (6/6/2013) value

I would like a VBA script which will look at both sheets to figure out which sheet has the newest create date.
I'm hoping that this will tell me which of the two sheets contains the newest data!

Next I would like the script to name the newer of the worksheets (New Data) and of coruse the other worksheet (Old Data)

**martindwilson** · 07-13-2013, 10:45 AM

id have thought you could simply use a formula to identify the sheet and the date!

**floydian** · 07-13-2013, 10:52 AM

I have a VBA code that is automating everything in the back end.

I know this is very simple but I am a VBA idiot.

I know it's something like:
finding the lowest value in sheet1 row T and assigning it to a value
finding the lowest value in sheet2 row T and assigning it to a SECOND value
Compare value 1 and value 2 and based on the lowest, changing the worksheet name

I know what I think NEEDS to happen... I have tried a few times but I can't get past the first line SUB change_ws_name ()

**floydian** · 07-13-2013, 11:45 AM

I hate that I am sooo stupid with this!

So far I have:

Dim sht1 As Range
Dim sht2 As Range
Dim sValue1 As Variant
Dim sValue2 As Variant
     
With Sheets("Sheet1")
    sht1 = .Range("T2:T")

and I am ALREADY getting an error!

**martindwilson** · 07-13-2013, 11:42 AM

tests all of col t sheet1 and sheet2 for max and renames sheet1 and sheet2 only if they both exist

Sub testd()
'tests all of col t sheet1 and sheet2 for max and renames sheet1 and sheet2 only if they both exist
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    On Error Resume Next
    Set ws2 = Sheets("sheet2")
    Set ws1 = Sheets("sheet1")
    If Err.Number <> 0 Then
        MsgBox "sheet1 or sheet2 do not exist exiting macro"
        
Exit Sub
End If
    
   
    

Set myrange1 = Worksheets("Sheet1").Range("t:t") 'change here if you just want to check 1 cell code could be a lot shorter if it is one cell only tho
Set myrange2 = Worksheets("Sheet2").Range("t:t")  'and here eg Range("t1") or range("t2:t1000") 
firstws = WorksheetFunction.Max(myrange1)
secondws = WorksheetFunction.Max(myrange2)


If firstws = secondws Then
MsgBox ("max date is same on both ws.  Exiting macro")
Exit Sub
End If
If firstws > secondws Then
Sheets("Sheet1").Name = "data"
Sheets("Sheet2").Name = "old data"
End If
If firstws < secondws Then
Sheets("Sheet1").Name = "old data"
Sheets("Sheet2").Name = "data"
End If
    
  
End Sub

**martindwilson** · 07-13-2013, 12:09 PM

well the code i gave you does what i think you want not what you asked for !
the lowest value is the oldest date the highest will be the newest date

**floydian** · 07-13-2013, 02:27 PM

I must have been posting as you posted that code.

Is there a good book to learn VBA for the total new developer.
I am a complete idiot right now when it comes to this!

I can't wait to try it out and will let you know what I find!

THANK YOU!!!!

**martindwilson** · 07-13-2013, 03:01 PM

no idea! i just had a look and patched it together with a few google searches i'm sure there is much better code!

Find lowest value in a common column between two worksheets and then name the worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Find lowest value in a common column between two worksheets and then name the worksheets

Re: Find lowest value in a common column between two worksheets and then name the workshee

Re: Find lowest value in a common column between two worksheets and then name the workshee

Re: Find lowest value in a common column between two worksheets and then name the workshee

Re: Find lowest value in a common column between two worksheets and then name the workshee

Re: Find lowest value in a common column between two worksheets and then name the workshee

Re: Find lowest value in a common column between two worksheets and then name the workshee

Re: Find lowest value in a common column between two worksheets and then name the workshee

Thread Information

Users Browsing this Thread

Similar Threads

Find common id in worksheets and eliminate unnecessary lines of data

[SOLVED] Combining several worksheets with one common column (primary key)

[SOLVED] Find lowest value in corresponding cells on multiple worksheets. Return value & location.

Find lowest value in corresponding cells on multiple worksheets. Return value & location.

[SOLVED] Excel 2007 : Find lowest number in column seperated by common names in another column

Bookmarks

Bookmarks

Posting Permissions