-
Find the largest value in a column, then copy and paste to a different workbook
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.
[vba]
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
[/vba]
-
[vba]
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
[/vba]
-
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 [vba]wsMaster.Range("C" & 3).PasteSpecial xlPasteValues[/vba] part of the code. Any ideas why or how to fix? my code now looks like this:
[vba]
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[/vba]
-
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 ?
-
I need the format to be currency. I tried your code and, although there were no errors, No output appeared.
-
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".
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules