Paste Special Macro Assistance needed

A

akemeny

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.
 
R

RyanH

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.
 
A

akemeny

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.
 
D

Dave Peterson

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

RyanH

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Modification help needed 1
Macro Modification help 3
Macro Modification 1
Getvalues Macro Help Needed 2
Queries 0
vba 1
Macro Modification Help 1
Modify sub to include sheetname in new col 10

Top