Currently I have the following Macro:

Sub getvalues()
lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
'MsgBox lr
Rows("2:" & lr).ClearContents
With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "am") = upheld Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "am") = upheld And IsDate(.Cells(i, "aj")) Then
..Rows(i).Copy Rows(dlr)
Next i
End With
End Sub

The way it currently pulls information is it looks at column AM for any
cells with "upheld". It then checks for a date in AJ. If both are met then
it copies the row.

This works perfectly for certain aspects of my spreadsheets, but I need two
things that I can't figure out. The first, how can I change that above macro
so that it won't clear or delete any information after its been copied over?
Second, how can I change the macro to paste only the values of the cells that
its copying from?

Thanks in advance for any assistance.

This procedure will do both. You do not need to copy and paste if you only
want to copy the values.

Sub getvalues()

Dim lr As Long
Dim slr As Long
Dim dlr As Long
Dim i As Long

lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
Rows("2:" & lr).ClearContents

With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
If .Cells(i, "AM") = "upheld" And IsDate(.Cells(i, "AJ")) Then
.Rows(i).Value = Rows(dlr).Value
Next i
End With

End Sub


Hope this helps! If so, let me know by clicking "YES" below.

--
Cheers,
Ryan


"akemeny" wrote:

Currently I have the following Macro:

Sub getvalues()
lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
'MsgBox lr
Rows("2:" & lr).ClearContents
With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "am") = upheld Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "am") = upheld And IsDate(.Cells(i, "aj")) Then
.Rows(i).Copy Rows(dlr)
Next i
End With
End Sub

The way it currently pulls information is it looks at column AM for any
cells with "upheld". It then checks for a date in AJ. If both are met then
it copies the row.

This works perfectly for certain aspects of my spreadsheets, but I need two
things that I can't figure out. The first, how can I change that above macro
so that it won't clear or delete any information after its been copied over?
Second, how can I change the macro to paste only the values of the cells that
its copying from?

Thanks in advance for any assistance.

Ryan,

The code that you gave me below is giving me the Compile Error: Next
without For. I can clearly see where you have the For present so I'm not
sure why it isn't working.

"RyanH" wrote:

This procedure will do both. You do not need to copy and paste if you only
want to copy the values.

Sub getvalues()

Dim lr As Long
Dim slr As Long
Dim dlr As Long
Dim i As Long

lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
Rows("2:" & lr).ClearContents

With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
If .Cells(i, "AM") = "upheld" And IsDate(.Cells(i, "AJ")) Then
.Rows(i).Value = Rows(dlr).Value
Next i
End With

End Sub


Hope this helps! If so, let me know by clicking "YES" below.

--
Cheers,
Ryan


"akemeny" wrote:

Currently I have the following Macro:

Sub getvalues()
lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
'MsgBox lr
Rows("2:" & lr).ClearContents
With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "am") = upheld Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "am") = upheld And IsDate(.Cells(i, "aj")) Then
.Rows(i).Copy Rows(dlr)
Next i
End With
End Sub

The way it currently pulls information is it looks at column AM for any
cells with "upheld". It then checks for a date in AJ. If both are met then
it copies the row.

This works perfectly for certain aspects of my spreadsheets, but I need two
things that I can't figure out. The first, how can I change that above macro
so that it won't clear or delete any information after its been copied over?
Second, how can I change the macro to paste only the values of the cells that
its copying from?

Thanks in advance for any assistance.

If .Cells(i, "AM") = "upheld" And IsDate(.Cells(i, "AJ")) Then
.Rows(i).Value = Rows(dlr).Value
End if '<- added
Next i

akemeny wrote:

Ryan,

The code that you gave me below is giving me the Compile Error: Next
without For. I can clearly see where you have the For present so I'm not
sure why it isn't working.

"RyanH" wrote:

This procedure will do both. You do not need to copy and paste if you only
want to copy the values.

Sub getvalues()

Dim lr As Long
Dim slr As Long
Dim dlr As Long
Dim i As Long

lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
Rows("2:" & lr).ClearContents

With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
If .Cells(i, "AM") = "upheld" And IsDate(.Cells(i, "AJ")) Then
.Rows(i).Value = Rows(dlr).Value
Next i
End With

End Sub


Hope this helps! If so, let me know by clicking "YES" below.

--
Cheers,
Ryan


"akemeny" wrote:

Currently I have the following Macro:

Sub getvalues()
lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
'MsgBox lr
Rows("2:" & lr).ClearContents
With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "am") = upheld Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "am") = upheld And IsDate(.Cells(i, "aj")) Then
.Rows(i).Copy Rows(dlr)
Next i
End With
End Sub

The way it currently pulls information is it looks at column AM for any
cells with "upheld". It then checks for a date in AJ. If both are met then
it copies the row.

This works perfectly for certain aspects of my spreadsheets, but I need two
things that I can't figure out. The first, how can I change that above macro
so that it won't clear or delete any information after its been copied over?
Second, how can I change the macro to paste only the values of the cells that
its copying from?

Thanks in advance for any assistance.

--

Dave Peterson

Sorry about that. I forgot to put the End If.

Sub getvalues()

Dim lr As Long
Dim slr As Long
Dim dlr As Long
Dim i As Long

lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
Rows("2:" & lr).ClearContents

With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
If .Cells(i, "AM") = "upheld" And IsDate(.Cells(i, "AJ")) Then
.Rows(i).Value = Rows(dlr).Value
End If
Next i
End With

End Sub

Hope this helps! If so, let me know by clicking "YES" below.
--
Cheers,
Ryan


"akemeny" wrote:

Ryan,

The code that you gave me below is giving me the Compile Error: Next
without For. I can clearly see where you have the For present so I'm not
sure why it isn't working.

"RyanH" wrote:

This procedure will do both. You do not need to copy and paste if you only
want to copy the values.

Sub getvalues()

Dim lr As Long
Dim slr As Long
Dim dlr As Long
Dim i As Long

lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
Rows("2:" & lr).ClearContents

With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
If .Cells(i, "AM") = "upheld" And IsDate(.Cells(i, "AJ")) Then
.Rows(i).Value = Rows(dlr).Value
Next i
End With

End Sub


Hope this helps! If so, let me know by clicking "YES" below.

--
Cheers,
Ryan


"akemeny" wrote:

Currently I have the following Macro:

Sub getvalues()
lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
'MsgBox lr
Rows("2:" & lr).ClearContents
With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "am") = upheld Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "am") = upheld And IsDate(.Cells(i, "aj")) Then
.Rows(i).Copy Rows(dlr)
Next i
End With
End Sub

The way it currently pulls information is it looks at column AM for any
cells with "upheld". It then checks for a date in AJ. If both are met then
it copies the row.

This works perfectly for certain aspects of my spreadsheets, but I need two
things that I can't figure out. The first, how can I change that above macro
so that it won't clear or delete any information after its been copied over?
Second, how can I change the macro to paste only the values of the cells that
its copying from?

Thanks in advance for any assistance.

Nice to acknowledge where you got your
"I have the following macro"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"akemeny" wrote in message
...
Currently I have the following Macro:

Sub getvalues()
lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
'MsgBox lr
Rows("2:" & lr).ClearContents
With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 6 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "am") = upheld Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "am") = upheld And IsDate(.Cells(i, "aj")) Then
.Rows(i).Copy Rows(dlr)
Next i
End With
End Sub

The way it currently pulls information is it looks at column AM for any
cells with "upheld". It then checks for a date in AJ. If both are met
then
it copies the row.

This works perfectly for certain aspects of my spreadsheets, but I need
two
things that I can't figure out. The first, how can I change that above
macro
so that it won't clear or delete any information after its been copied
over?
Second, how can I change the macro to paste only the values of the cells
that
its copying from?

Thanks in advance for any assistance.