Excel VBA - Copy/Paste Values per Criteria
up vote
-1
down vote
favorite
How do I copy just the values of a range to destination. Here is my code below. Any suggestions would be appreciated.
If ActiveSheet.Range("H2") = Sheets("RAW DATA").Range("E2") Then
On Error GoTo 0
refreshData
Selection.AutoFilter
Else
Dim r As Range
Dim LastRow As Long, i As Long, ws2 As Worksheet
With Worksheets("RAW DATA")
With Worksheets("RAW DATA")
.AutoFilterMode = False
.Range("G10").AutoFilter Field:=7, Criteria1:="N"
With .AutoFilter.Range
On Error Resume Next
Set r = .Resize(.Rows.Count - 1, 5).Offset(1, 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not r Is Nothing Then
r.copy Worksheets("ORDER QUEUE").Range("F11" & .Rows.Count).End(xlUp).Offset(1, 0)
End If
End With
.AutoFilterMode = False
End With
End With
End If
I'm specifically looking to copy values w/o using PasteSpecial.
excel vba excel-vba
edited Nov 12 at 7:19
Pᴇʜ
19.2k42650
asked Nov 9 at 20:41
user10630747
11
|
show 2 more comments
up vote
-1
down vote
favorite
How do I copy just the values of a range to destination. Here is my code below. Any suggestions would be appreciated.
If ActiveSheet.Range("H2") = Sheets("RAW DATA").Range("E2") Then
On Error GoTo 0
refreshData
Selection.AutoFilter
Else
Dim r As Range
Dim LastRow As Long, i As Long, ws2 As Worksheet
With Worksheets("RAW DATA")
With Worksheets("RAW DATA")
.AutoFilterMode = False
.Range("G10").AutoFilter Field:=7, Criteria1:="N"
With .AutoFilter.Range
On Error Resume Next
Set r = .Resize(.Rows.Count - 1, 5).Offset(1, 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not r Is Nothing Then
r.copy Worksheets("ORDER QUEUE").Range("F11" & .Rows.Count).End(xlUp).Offset(1, 0)
End If
End With
.AutoFilterMode = False
End With
End With
End If
I'm specifically looking to copy values w/o using PasteSpecial.
excel vba excel-vba
edited Nov 12 at 7:19
Pᴇʜ
19.2k42650
asked Nov 9 at 20:41
user10630747
11
3
I dont see a question in your question
– urdearboy
Nov 9 at 20:44
Possible duplicate of Excel VBA Copy Paste Values only( xlPasteValues )
– urdearboy
Nov 9 at 20:52
2
You have a few issues with yourWithblocks. First, you haveWith Worksheets("RAW DATA")back to back. Also, when detereming the last row onORDER QUEUEyou are actually looking at the wrong sheet since you apply the with block to.Rows.Count. Also, you have a few unneccsary error traps here. No need to error trapSet r = ...- You are already checking for the error withIf Not r is Nothing Then
– urdearboy
Nov 9 at 20:54
@urdearboy thanks for pointing out the things above; however, i'm specifically looking for a way to copy value to a destination without using 'PasteSpecial' - if possible.
– user10630747
Nov 9 at 21:26
1
See Range.Value equal to each other
– urdearboy
Nov 9 at 21:46
|
show 2 more comments
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
How do I copy just the values of a range to destination. Here is my code below. Any suggestions would be appreciated.
If ActiveSheet.Range("H2") = Sheets("RAW DATA").Range("E2") Then
On Error GoTo 0
refreshData
Selection.AutoFilter
Else
Dim r As Range
Dim LastRow As Long, i As Long, ws2 As Worksheet
With Worksheets("RAW DATA")
With Worksheets("RAW DATA")
.AutoFilterMode = False
.Range("G10").AutoFilter Field:=7, Criteria1:="N"
With .AutoFilter.Range
On Error Resume Next
Set r = .Resize(.Rows.Count - 1, 5).Offset(1, 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not r Is Nothing Then
r.copy Worksheets("ORDER QUEUE").Range("F11" & .Rows.Count).End(xlUp).Offset(1, 0)
End If
End With
.AutoFilterMode = False
End With
End With
End If
I'm specifically looking to copy values w/o using PasteSpecial.
excel vba excel-vba
edited Nov 12 at 7:19
Pᴇʜ
19.2k42650
asked Nov 9 at 20:41
user10630747
11
How do I copy just the values of a range to destination. Here is my code below. Any suggestions would be appreciated.
If ActiveSheet.Range("H2") = Sheets("RAW DATA").Range("E2") Then
On Error GoTo 0
refreshData
Selection.AutoFilter
Else
Dim r As Range
Dim LastRow As Long, i As Long, ws2 As Worksheet
With Worksheets("RAW DATA")
With Worksheets("RAW DATA")
.AutoFilterMode = False
.Range("G10").AutoFilter Field:=7, Criteria1:="N"
With .AutoFilter.Range
On Error Resume Next
Set r = .Resize(.Rows.Count - 1, 5).Offset(1, 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not r Is Nothing Then
r.copy Worksheets("ORDER QUEUE").Range("F11" & .Rows.Count).End(xlUp).Offset(1, 0)
End If
End With
.AutoFilterMode = False
End With
End With
End If
I'm specifically looking to copy values w/o using PasteSpecial.
excel vba excel-vba
excel vba excel-vba
edited Nov 12 at 7:19
Pᴇʜ
19.2k42650
asked Nov 9 at 20:41
user10630747
11
edited Nov 12 at 7:19
Pᴇʜ
19.2k42650
asked Nov 9 at 20:41
user10630747
11
edited Nov 12 at 7:19
Pᴇʜ
19.2k42650
edited Nov 12 at 7:19
Pᴇʜ
19.2k42650
edited Nov 12 at 7:19
Pᴇʜ
19.2k42650
19.2k42650
asked Nov 9 at 20:41
user10630747
11
asked Nov 9 at 20:41
user10630747
11
asked Nov 9 at 20:41
user10630747
11
11
3
I dont see a question in your question
– urdearboy
Nov 9 at 20:44
Possible duplicate of Excel VBA Copy Paste Values only( xlPasteValues )
– urdearboy
Nov 9 at 20:52
2
You have a few issues with yourWithblocks. First, you haveWith Worksheets("RAW DATA")back to back. Also, when detereming the last row onORDER QUEUEyou are actually looking at the wrong sheet since you apply the with block to.Rows.Count. Also, you have a few unneccsary error traps here. No need to error trapSet r = ...- You are already checking for the error withIf Not r is Nothing Then
– urdearboy
Nov 9 at 20:54
@urdearboy thanks for pointing out the things above; however, i'm specifically looking for a way to copy value to a destination without using 'PasteSpecial' - if possible.
– user10630747
Nov 9 at 21:26
1
See Range.Value equal to each other
– urdearboy
Nov 9 at 21:46
|
show 2 more comments
3
I dont see a question in your question
– urdearboy
Nov 9 at 20:44
Possible duplicate of Excel VBA Copy Paste Values only( xlPasteValues )
– urdearboy
Nov 9 at 20:52
2
You have a few issues with yourWithblocks. First, you haveWith Worksheets("RAW DATA")back to back. Also, when detereming the last row onORDER QUEUEyou are actually looking at the wrong sheet since you apply the with block to.Rows.Count. Also, you have a few unneccsary error traps here. No need to error trapSet r = ...- You are already checking for the error withIf Not r is Nothing Then
– urdearboy
Nov 9 at 20:54
@urdearboy thanks for pointing out the things above; however, i'm specifically looking for a way to copy value to a destination without using 'PasteSpecial' - if possible.
– user10630747
Nov 9 at 21:26
1
See Range.Value equal to each other
– urdearboy
Nov 9 at 21:46
3
3
I dont see a question in your question
– urdearboy
Nov 9 at 20:44
I dont see a question in your question
– urdearboy
Nov 9 at 20:44
Possible duplicate of Excel VBA Copy Paste Values only( xlPasteValues )
– urdearboy
Nov 9 at 20:52
Possible duplicate of Excel VBA Copy Paste Values only( xlPasteValues )
– urdearboy
Nov 9 at 20:52
2
2
You have a few issues with your
With blocks. First, you have With Worksheets("RAW DATA") back to back. Also, when detereming the last row on ORDER QUEUE you are actually looking at the wrong sheet since you apply the with block to .Rows.Count. Also, you have a few unneccsary error traps here. No need to error trap Set r = ... - You are already checking for the error with If Not r is Nothing Then– urdearboy
Nov 9 at 20:54
You have a few issues with your
With blocks. First, you have With Worksheets("RAW DATA") back to back. Also, when detereming the last row on ORDER QUEUE you are actually looking at the wrong sheet since you apply the with block to .Rows.Count. Also, you have a few unneccsary error traps here. No need to error trap Set r = ... - You are already checking for the error with If Not r is Nothing Then– urdearboy
Nov 9 at 20:54
@urdearboy thanks for pointing out the things above; however, i'm specifically looking for a way to copy value to a destination without using 'PasteSpecial' - if possible.
– user10630747
Nov 9 at 21:26
@urdearboy thanks for pointing out the things above; however, i'm specifically looking for a way to copy value to a destination without using 'PasteSpecial' - if possible.
– user10630747
Nov 9 at 21:26
1
1
See Range.Value equal to each other
– urdearboy
Nov 9 at 21:46
See Range.Value equal to each other
– urdearboy
Nov 9 at 21:46
|
show 2 more comments
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53232992%2fexcel-vba-copy-paste-values-per-criteria%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
I dont see a question in your question
– urdearboy
Nov 9 at 20:44
Possible duplicate of Excel VBA Copy Paste Values only( xlPasteValues )
– urdearboy
Nov 9 at 20:52
2
You have a few issues with your
Withblocks. First, you haveWith Worksheets("RAW DATA")back to back. Also, when detereming the last row onORDER QUEUEyou are actually looking at the wrong sheet since you apply the with block to.Rows.Count. Also, you have a few unneccsary error traps here. No need to error trapSet r = ...- You are already checking for the error withIf Not r is Nothing Then– urdearboy
Nov 9 at 20:54
@urdearboy thanks for pointing out the things above; however, i'm specifically looking for a way to copy value to a destination without using 'PasteSpecial' - if possible.
– user10630747
Nov 9 at 21:26
1
See Range.Value equal to each other
– urdearboy
Nov 9 at 21:46