PDA

View Full Version : Find the largest value in a column, then copy and paste to a different workbook



mpalk
06-14-2013, 10:56 AM
I am writing a macro that I am having some trouble with. I would like to have the macro find the largest number in one workbook, and copy and paste that number into the next empty cell in row 3 of another workbook. I have found a code and tweaked it so it copies and pastes data into the correct workbook and worksheet, but I have not figured out how to have it find the largest number or paste it to the next open cell(I only found how to paste it in a single cell). Below is the code i currently have( the if statement is only to check if the copy/paste worked, it will not be part of the final code). Thanks for the help.



Private Sub CommandButton1_Click()
Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long
Dim vMax As Variant

Set wsMaster = ThisWorkbook.Sheets("Contract Metrics")
NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1

Set wbDATA = Workbooks.Open("C:\Documents and Settings\Michael Palkovitz\My Documents\Test\Contracts Metrics.xlsx")
With wbDATA.Sheets("Contract Task Summary(1)")
LastRow = .Range("C" & .Rows.Count).End(xlUp).Row

If LastRow > 19 Then
.Range("C" & LastRow).Copy
wsMaster.Range("C" & 3).PasteSpecial xlPasteValues
wsMaster.Range("C" & 3).PasteSpecial xlPasteFormats
End If
End With

wbDATA.Close False
End Function

snb
06-14-2013, 12:04 PM
Private Sub CommandButton1_Click()
with Getobject("C:\Documents and Settings\Michael Palkovitz\My Documents\Test\Contracts Metrics.xlsx")
ThisWorkbook.Sheets("Contract Metrics").cells(rows.count,1).end(xlup).offset(1)=application.max(.Sheets("Contract Task Summary(1)").usedrange)
.Close False
end with
End Sub

mpalk
06-14-2013, 12:28 PM
Thanks for the reply snb, this solves my problem of getting the max number from the other workbook, but i am getting a 'pastespecial method of range class failed', when i implement your code into mine in the wsMaster.Range("C" & 3).PasteSpecial xlPasteValues part of the code. Any ideas why or how to fix? my code now looks like this:

Private Sub CommandButton1_Click()
Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long

Set wsMaster = ThisWorkbook.Sheets("Contract Metrics")
NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1

Set wbDATA = Workbooks.Open("C:\Documents and Settings\Michael Palkovitz\My Documents\Test\Contracts Metrics.xlsx")
With GetObject("C:\Documents and Settings\Michael Palkovitz\My Documents\Test\Contracts Metrics.xlsx")
ThisWorkbook.Sheets("Contract Metrics").Cells(Rows.Count, 1).End(xlUp).Offset(1) = Application.Max(.Sheets("Contract Task Summary(1)").UsedRange)
.Close False

wsMaster.Range("C" & 3).PasteSpecial xlPasteValues
wsMaster.Range("C" & 3).PasteSpecial xlPasteFormats
End With




wbDATA.Close False
End Sub
End Sub

snb
06-14-2013, 01:13 PM
As you might have noticed: my code is a replacement for all your code.
What kind of formatting do you need in the destination cell ?

mpalk
06-17-2013, 04:18 AM
I need the format to be currency. I tried your code and, although there were no errors, No output appeared.

mpalk
06-17-2013, 04:54 AM
Also, after closer examination, the code I have in the original post finds the value I need (the last row has the largest value), so all i need is the code to paste into the next empty cell in row 3, instead of just "C3".